Gathering Information from ADO | Programming MicrosoftВ® ADO.NET 2.0 Core Reference

[Previous] [Next]

Let's look at the error information ADO provides and what features ADO offers programmers to help them determine why an optimistic update attempt failed. The ADO object model contains some properties and methods you can use to determine the type of modification made to the record that contains pending changes and why your update attempt failed.

ADO's Error Information

One of ADO's major drawbacks is that it often provides little information through the Error object about what happened. It sometimes returns the message, "Errors occurred," which doesn't help you determine why the error occurred. Trust me, the ADO development team is aware of programmers' frustrations with this error.

The Record and Stream objects, both new to ADO 2.5 and covered in more depth in Chapter 6, provide more detailed error information. The development team had similar code in the works for the other ADO objects, but they were concerned about the possibility of creating compatibility problems with existing code. However, the team hasn't given up, and they're currently investigating ways to improve on the current error handling in the next release of ADO, after 2.5. So for now, ADO objects that existed prior to 2.5 will still generate the same error information in ADO 2.5 that they did in ADO 2.1 to maintain backward compatibility.

NOTE
Maintaining backward compatibility can be quite a burden. If you developed a Microsoft Internet Information Services (IIS) and Active Server Pages (ASP) application using ADO 2.1 and built error handlers to trap for optimistic updating errors or other problems that generated the "Errors occurred" error, you might have to rewrite your application if installing a newer version of ADO generated different error messages when running the same code. This makes it difficult for the ADO development team to improve on the "Errors occurred" message.

What sort of error information does ADO generate when optimistic updates fail? Generally, you'll see your old friend:

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

But it depends on what else went wrong. If you have optimistic updating conflicts as well as referential integrity violations when you call Recordset.UpdateBatch, you might receive an error that refers to only the referential integrity violations. And as I mentioned, in some cases ADO simply returns, "Errors occurred." What's a poor programmer to do?

Filter Property

You can set the Filter property on the Recordset object so that the only visible records in the Recordset are those whose updates failed as a result of conflicts with the current information in the database. Set the Filter property to adFilterConflictingRecords, and then check the RecordCount property on the Recordset object. The RecordCount property will return either a positive number indicating the number of records whose updates failed as a result of conflicts, or 0 if no conflicts occurred. An update that failed as a result of a referential integrity violation or other database constraint won't be visible through this filter because such failures are not deemed as conflicts.

You can also set the Filter property to adFilterPendingRecords to see only the records that contain pending updates. Because records in a Recordset whose updates fail due to optimistic updating conflicts still contain pending updates, they're visible through this filter. However, records whose updates fail because of referential integrity violations or other database constraints will also be visible because their updates are still marked as pending.

Status Property

As its name implies, the Status property on the Recordset object indicates the status of the current record. You can check this property to determine whether the record contains pending changes (as well as the types of changes) and whether the attempt to submit the record's pending changes failed because of an optimistic updating conflict. The Status property returns a combination of the following values for a particular record:

adRecUnmodified If the update attempt succeeded and no pending changes exist

adRecNew If the record contains a pending insert

adRecModified If the record contains a pending modification

adRecDBDeleted or adRecDeleted If the record contains a pending deletion

adRecConcurrencyViolation If the record contains a pending change because the update attempt failed as a result of an optimistic updating conflict

NOTE
The documentation for adRecDBDeleted states that this value corresponds to a record that has been deleted from the database, while adRecDeleted indicates that the record has been deleted from the Recordset. My experience with the Status property has been that it returns adRecDeleted for a pending deletion prior to submitting that change to the database with UpdateBatch. If the attempt to delete that record in the database fails because of a conflict, the Status property changes to adRecDBDeleted + adRecConcurrencyViolation. Your results can vary; I recommend that you run tests using your OLE DB provider (or ODBC driver) and database.

ADO 2.5 introduced a Status property on the Field object. This property is implemented only on the Field object exposed by the Record object; if you return the Status value of a Field object that's exposed by a Recordset object, that value is always 0. Perhaps in the next release of ADO, this property will return information that you can use to determine which fields were modified locally in the Recordset and which fields changed in the database to cause the optimistic updating conflict.

Value and OriginalValue Properties

Once you've used the Filter and/or Status properties on the Recordset object to determine which records' update attempts failed, you'll want to figure out which pending changes those records contain. You might want to report this information to the user, and if you plan on resolving the optimistic updating conflict programmatically, you'll need this information to determine what the failed update attempt was supposed to do to the database.

For example, if your failed update attempt contains a modification, you can compare the Value property to the OriginalValue property on each Field object. If the pending update is a failed deletion, you can use the OriginalValue property on the Field object or objects that correspond to the field or fields in the primary key to determine which record in the database your code intended to delete.

Resync Method and Update Resync Property

Once you've determined which pending change a record contains, you'll want to examine the corresponding record in your database to understand why the update attempt failed. You can use either the Resync method or the Update Resync dynamic property on the Recordset object to retrieve the current contents of that record.

If you set the Update Resync dynamic property in the Recordset's Properties collection to adResyncConflicts, the ADO Cursor Engine will automatically retrieve the current contents of the record you're attempting to update if a conflict occurs. You can also call the Resync method on the Recordset object to retrieve this data on a record with pending changes. This method will retrieve data for only the records in your Recordset that contain pending changes and that satisfy the criteria of the value used in the AffectRecords parameter.

UnderlyingValue Property

For each of the records containing a failed update attempt, you'll generally want to compare the contents of the record you initially retrieved from the database, the changes you made to the data in your local Recordset object, and the current contents of that record in the database. All this information is available through the Field object. You've already seen that the local changes are stored in the Value property of the Field object and that the data initially retrieved from the database is stored in the OriginalValue property. When you use the Update Resync property on the Recordset object, the current contents of that record in the database are placed in the UnderlyingValue property of the Field object.

Just as you can compare the OriginalValue and Value properties on each Field object to determine which changes were made to the local Recordset, you can compare the OriginalValue and UnderlyingValue properties to determine which changes were made to the record in the database since you opened the Recordset.

If you want to use the Resync method rather than the Update Resync property, you'll need to set the ResyncValues parameter on the method to adResyncUnderlyingValues, in order to store the current contents of the record into the UnderlyingValue property on each Field object. Keep in mind that you can use the Resync method to retrieve data this way only on a Recordset object that uses batch optimistic locking. If your Recordset object uses simple optimistic locking, the Resync method will generate an error if the record contains a pending change. The Update Resync property does not enforce such a restriction.

Detecting "Holes" in Your Recordset

When we covered keyset cursors in Chapter 7, we briefly talked about "holes" that can occur in your Recordset object when a record that initially existed in the results of your query is deleted from the database. You can discover these holes if you try to examine the Value property or the UnderlyingValue property of one of the Field objects of a record you've deleted in your Recordset.

With either of these properties you'll receive the following error:

 "A given HROW referred to a hard- or soft-deleted row."

You can accurately determine whether the optimistic updating conflict occurred because the record the ADO Cursor Engine tried to modify was deleted from the database by trapping for this error. You can trap this error by retrieving the current contents of the conflicting record from the Update Resync property or the Resync method and checking the UnderlyingValue property or the Value property on any of the Field objects.

If you call the Resync method on the Recordset object and the ADO Cursor Engine cannot locate the corresponding record in the database because it was deleted, you'll receive the following trappable error:

 "The key value for this row has been changed or deleted at the data source. The local row is now deleted."

Contrary to the error message, the record hasn't actually been removed from your Recordset.

One last note: If another user modifies the value of the primary key in the database, the ADO Cursor Engine will react as though the record no longer exists in the table. An attempt to resynchronize a record whose update failed because another user modified the primary key for that record will retrieve no data because the ADO Cursor Engine has no way of locating that record.

NOTE
I don't recommend letting your users modify the primary key value for records in your database. Given how much the ADO Cursor Engine relies on the primary key value, you should avoid this scenario if at all possible. Say an optimistic update attempt fails because another user modified the primary key for the corresponding record in the database. ADO will react as though the user deleted that record from the database. If you need to know whether that record was really deleted or whether the problem was due to another user changing the value of the primary key, you're probably out of luck. There's no reliable way to locate the corresponding record in this scenario. Therefore, I won't discuss this scenario in the following sections. In this case, you're on your own.