Update Resync to the Rescue | Programming MicrosoftВ® ADO.NET 2.0 Core Reference

[Previous] [Next]

ADO 2.1 introduced a dynamic property to the Recordset object called Update Resync, which is used with client-side Recordsets to aid developers who encounter scenarios such as those just described. Depending on the value that you specify for this property, ADO executes queries to refetch the record that you just updated (or attempted to update) in the database.

If you're using optimistic updating mode, ADO issues these queries when you call the Update method on the Recordset, immediately after the ADO Cursor Engine issues the action query. If you're using batch optimistic updating mode, ADO issues these queries when you call UpdateBatch. The Update Resync property can contain values from the CEResyncEnum data type, shown in the following table:

CEResyncEnum Values
Constant	Value	Description
adResyncNone	0	Retrieves no data after modifications
adResyncAutoIncrement	1	Default; retrieves auto-incrementing value for new records
adResyncConflicts	2	Retrieves underlying values for records whose updates fail because of optimistic updating conflicts
adResyncUpdates	4	Retrieves the current contents of records after their updates have been submitted to the database
adResyncInserts	8	Retrieves the current contents of new records after they have been submitted to the database
adResyncAll	15	Performs actions associated with adResyncAutoIncrement, adResyncConflicts, adResyncUpdates, and adResyncInserts

To access this property, you must set the CursorLocation property to adUseClient on either the Recordset object or the Connection object prior to opening the Recordset, as shown, respectively, in the following code:

 With rsCustomers .CursorLocation = adUseClient .Open strSQL, cnDatabase, adOpenStatic, _ adLockReadOnly, adCmdText .Properties("Update Resync") = adResyncUpdates End With

 cnDatabase.CursorLocation = adUseClient With rsCustomers .Open strSQL, cnDatabase, adOpenStatic, _ adLockOptimistic, adCmdText .Properties("Update Resync") = adResyncUpdates End With

The Update Resync property is a bitmask—which means you can also use the sum of different constants for the property:

 rsCustomers.Properties("Update Resync") = _ adResyncAutoIncrement + adResyncInserts

Let's take a closer look at the behavior associated with each of the available constants.

adResyncNone

Using this constant turns off the Update Resync functionality. The ADO Cursor Engine will not issue any queries to retrieve information about the record that it just updated.

adResyncAutoIncrement

This constant is extremely helpful when you want to use a client-side Recordset to insert new records into a table that uses an auto-incrementing identity field. Many developers who are accustomed to using Microsoft Access to insert records have come to expect this functionality—as soon as they add a new record, they expect to see the new value for the identity field. As a result, adResyncAutoIncrement is the default value for the Update Resync property on client-side Recordsets.

To understand how the processing works with this constant assigned to the Update Resync property, let's take a look at some code:

 strSQL = "SELECT CustomerID, CustomerName, BalanceDue " & _ "FROM Customers" With rsCustomers .CursorLocation = adUseClient .Open strSQL, cnDatabase, adOpenStatic, _ adLockOptimistic, adCmdText .Properties("Update Resync") = adResyncAutoIncrement .AddNew .Fields("CustomerName") = "Acme Widgets" .Fields("BalanceDue") = 100 .Update End With

When you call the Update method on the Recordset object, the ADO Cursor Engine builds the following query to insert the new record into the table in the database:

 INSERT INTO Customers (CustomerName, BalanceDue) VALUES ('Acme Widgets', 100)

The database uses this query to insert the record and informs the ADO Cursor Engine that the insertion was successful. Then the Cursor Engine issues the following query:

 SELECT @@IDENTITY

The ADO Cursor Engine uses this query to retrieve the database-generated, auto-incrementing identity value for the newly inserted record and places that information into the appropriate field in the client-side Recordset object.

There are a few caveats you should keep in mind about using this feature with different database products:

Microsoft SQL Server As of ADO 2.5, this feature works with both the SQL Server OLE DB provider and ODBC driver. As of ADO 2.1, this feature worked with the SQL Server OLE DB provider but not the SQL Server ODBC driver.

Access This feature works with version 4 of the Jet OLE DB Provider but only for Jet 4 with Access 2000-formatted databases. This feature does not work with the Access ODBC driver because the driver doesn't support the SELECT @@IDENTITY query.

Oracle Oracle has no notion of an identity field whose value is generated at the time of insertion. Instead, Oracle uses a concept called sequences that adds an extra step but avoids this scenario. For more information on sequences, see your Oracle documentation.

The SELECT @@IDENTITY query returns the last identity value generated on the connection, but the value might not be what you expect. If the INSERT command updates the record and then fires a trigger that inserts another record in a different table that includes an auto-incrementing identity field, you'll retrieve the value from this second table rather than the value of the identity field in the record you actually inserted. (A trigger is a special class of stored procedure that runs when an INSERT, UPDATE, or DELETE statement is issued.)

There are three main reasons you might want or require the newly generated identity value. First, it's helpful to have this information in your Recordset as soon as you pass the new record to the database. For example, if the auto-incrementing identity field is the OrderID field, a user who's taking orders from customers might need to read back a new order ID to a customer.

Second, most identity fields are the primary key field for the table—information that can come in handy. The ADO Cursor Engine uses the primary key value in building action queries. If you want to modify the newly inserted customer information but don't have the value of the CustomerID field for that customer, ADO can't build an action query to modify the appropriate record in the table.

Third, the ADO Cursor Engine uses the primary key value when you call the Resync method. For example, you might have a rule in your trigger for generating another piece of information that you want to retrieve immediately after adding the customer—for example, typing the zip code might generate the city and state. You might also need to retrieve the value of the server-generated timestamp field in order to handle subsequent updates.

NOTE
The ability to retrieve newly generated auto-increment values was one of the most popular requests for ADO in the 2.0 time frame. This feature was available in DAO/Jet, but no comparable feature existed in either RDO or ADO. In an attempt to persuade the development team, Don Willits (author of the MSDN white paper "Implementing ADO with Various Development Languages: The ADO Rosetta Stone") and I pledged to purchase a keg of good beer for the ADO development team if they could deliver such a feature. Although Don and I later discovered that the development team already had such a feature in the specs for ADO 2.1, we were still happy to fulfill our promise. It was nice to see some of the programmers, testers, and program managers in a relaxed atmosphere. As one of the program managers became very relaxed, he asked what features I'd like to see in the next version. I'm extremely impressed with this feature and want to congratulate the development team on such a helpful and well-designed feature.

adResyncInserts

There are times—such as when you're basing your updates on a timestamp field—when you want to know what the database server did to the record you just inserted into your table. For a newly inserted record, you need to retrieve the value that the server generated for that field in order to perform a subsequent update on that record. For example, perhaps you have a trigger that sets a value for your BalanceDue field, in which case you need to retrieve and/or modify that value immediately after inserting the new record.

If you're using a primary key based on an auto-incrementing identity field, you need to combine the adResyncAutoIncrement and adResyncInserts constants to retrieve the contents of the newly inserted record. As we discussed in the previous section, adResyncAutoIncrement will retrieve the new auto-increment value. The ADO Cursor Engine then uses this value to retrieve the contents of the newly inserted record. Here's an example that utilizes this feature to insert a new record into a table containing an auto-incrementing identity field and then performs a subsequent update to that same record based on the timestamp field:

 strSQL = "SELECT CustomerID, CustomerName, BalanceDue, LastModified" & _ " FROM Customers" With rsCustomers .CursorLocation = adUseClient .Properties("Update Criteria") = adCriteriaTimeStamp .Properties("Update Resync") = adResyncInserts + _ adResyncAutoIncrement .Open strSQL, cnDatabase, adOpenStatic, _ adLockOptimistic, adCmdText .AddNew .Fields("CustomerName") = "Acme Widgets" .Update .Fields("BalanceDue") = 100 .Update End With

The ADO Cursor Engine executes the following queries the first time the Update method is called:

 INSERT INTO Customers (CustomerName) VALUES ('Acme Widgets') SELECT @@IDENTITY SELECT CustomerID, CustomerName, BalanceDue, LastModified FROM Customers WHERE CustomerID = n (n = the value retrieved in the previous SELECT query)

The first query inserts the record into the table. The second query retrieves the value generated by the server for the auto-incrementing identity field. Utilizing the data returned by the second query, the third query retrieves the contents of the newly inserted record.

In this scenario, the LastModified field contains a timestamp. When you modify the BalanceDue field and call the Update method the second time, the ADO Cursor Engine builds the following action query to handle the modification:

 UPDATE Customers SET BalanceDue = 100 WHERE CustomerID = <retrieved value> AND BalanceDue = 0 AND LastModified = <retrieved value>

Because you're using a timestamp field to handle the updates, the initial value that SQL Server generates for the timestamp field is required for this action query to succeed. However, another attempt to update this record will fail because the database generated a new timestamp value when you made this update, but you didn't retrieve this new value when calling Update the second time. To handle that scenario, you can use the adResyncUpdates constant.

adResyncUpdates

Just as adResyncInserts retrieves data from the record you just inserted, adResyncUpdates retrieves data from the record you just modified. In the previous example, the code successfully updates a record it had just inserted because, thanks to adResyncInserts, the timestamp field was retrieved immediately after the insertion. When that record is updated, the database generates a new value for the timestamp field, and that data is not returned to the Recordset, leaving the field in the Recordset outdated. Therefore, another attempt to update that same record would fail.

By using the adResyncUpdates constant, you can tell the ADO Cursor Engine that after you update an existing record by calling the Update method, you want to resynchronize the data in that record of the Recordset. The following example uses this feature:

 strSQL = "SELECT CustomerID, CustomerName, BalanceDue, LastModified" & _ " FROM Customers" With rsCustomers .CursorLocation = adUseClient .Properties("Update Criteria") = adCriteriaTimeStamp .Properties("Update Resync") = adResyncUpdates .Open strSQL, cnDatabase, adOpenStatic, _ adLockOptimistic, adCmdText .Fields("BalanceDue") = 150 .Update .Fields("CustomerName") = "Acme Widgets" .Update End With

When you call the Update method the first time, ADO builds this next action query to update that record in the table:

 UPDATE Customers SET BalanceDue = 150 WHERE CustomerID = 7 AND BalanceDue = 100 AND LastModified = 0x0...

Because you have set the Update Resync property to adResyncUpdates, the ADO Cursor Engine also issues the following query to retrieve the contents of the record that it just updated:

 SELECT CustomerID, CustomerName, BalanceDue, LastModified FROM Customers WHERE CustomerID = 7

The ADO Cursor Engine then places the returned information into the corresponding fields for the current record in the Recordset. Therefore, when you attempt to update that same record again, the second update will succeed because ADO retrieved the new timestamp value for the LastModified field that the database generated during the previous update. Similarly, subsequent updates will also succeed.

You should be aware of one issue regarding adResyncUpdates: when the ADO Cursor Engine retrieves information about the record in the database after you've performed your update, you might see that fields you hadn't modified have new values in your Recordset.

Let's look at a scenario in which the updates are based on only the fields being modified. The following code simply modifies the BalanceDue field for a particular customer:

 strSQL = "SELECT CustomerID, CustomerName, BalanceDue " & _ "FROM Customers" With rsCustomers .CursorLocation = adUseClient .Properties("Update Criteria") = adCriteriaUpdCols .Properties("Update Resync") = adResyncUpdates .Open strSQL, cnDatabase, adOpenStatic, _ adLockOptimistic, adCmdText .Fields("BalanceDue") = 150 .Update End With

If another user modifies the CustomerName field for that customer between the time you open the Recordset and the time you perform the update, there won't be a conflict. Why? Because the ADO Cursor Engine generates the following action query:

 UPDATE Customers SET BalanceDue = 150 WHERE CustomerID = 7 AND BalanceDue = 100

The CustomerName field isn't involved in this action query. The Update Criteria property is set to a value (adCriteriaUpdCols) indicating that the ADO Cursor Engine will specify only the updated fields (and the primary key field, of course) in the action query.

Let's quickly recap. You retrieved information about a particular customer. You modified the BalanceDue field and were about to update that customer information when another user modified the customer name. When you call the Update method, the update will succeed. Because you specified that you want to resynchronize the record after the update, you'll see the new value for the customer name in the Value property for that field.

adResyncConflicts

The action queries that ADO builds are considered optimistic update attempts because you're (optimistically) assuming that no one else has modified the data in the desired record. When you attempt to update a record that another user has since modified, ADO will still issue an action query such as the following:

 UPDATE Customers SET BalanceDue = 150 WHERE CustomerID = 7 AND BalanceDue = 100

But sometimes ADO will receive notification from the OLE DB provider and ODBC driver that no records were updated. This situation is called a conflict, and it generally occurs when another user has modified the value of one or more of the fields in the WHERE clause of the UPDATE query. (We'll cover trapping for and handling conflicts in more depth in Chapter 12.) If your optimistic update fails because of a conflict, ADO will pass the following error message to you:

 "The specified row could not be located for updating: Some values may have changed since it was last read."

Setting the Update Resync property to adResyncConflicts causes the ADO Cursor Engine to retrieve the current contents of the record you're attempting to modify if the update fails because of a conflict. The retrieved data is stored in the UnderlyingValue property for each Field in the Recordset object. You can use the data stored in the Value, OriginalValue, and UnderlyingValue properties to determine how to handle such a conflict. As I mentioned, we'll cover conflicts in more detail in the next chapter.

adResyncDeletes

I was just checking to see whether you're paying attention. There is no adResyncDeletes constant. Why? There's no need for one. If your attempt to delete succeeds, the record is removed from the table. There's no need to retrieve information about the record.

If the deletion fails because of a conflict, use adResyncConflicts to determine why the conflict occurred. If the deletion failed for another reason, such as referential integrity constraints (for instance, trying to delete a record from the Customers table when the customer has orders in the Orders table), you'll receive a trappable error.

adResyncAll

If you take a look at the values that are associated with the different constants in the CEResyncEnum enumeration, you might notice that the value of adResyncAll is the sum of the other constants. Setting the Update Resync property to adResyncAll combines the functionality of all the other constants in the data type but saves you the time of typing them all individually.