When a Simple Action Query Isn t Enough

[Previous] [Next]

There are times when the simple action queries generated by the ADO Cursor Engine don't supply enough information to perform the updates you require. Action queries essentially function like a one-way street: they tell the database how to modify the record but don't return the new contents of that record. However, in reaction to the changes made by ADO, the database system might need to make a number of other changes to the record because of elements such as auto-incrementing identity fields, timestamps, and default values. You usually don't need to be aware of this data. However in some cases, without this data you might encounter problems when you try to change a single record in your Recordset more than once.

You might be asking yourself, "Why would I ever want to update the same record twice in a single session? That's inefficient." Maybe, but often it's necessary based on user actions. For example, it's possible that the user would try to update the same record more than once. (Did you consider that scenario when designing or testing your application?) Let's look at some cases you might not have considered that could cause problems if you had no information from the updated record.

Auto-Incrementing Identity Field

Suppose that your primary key is based on an auto-incrementing identity field. Prior to version 2.1 of ADO, if you used client-side Recordsets, you had no way to retrieve the new value when adding a new record to the database. The ADO Cursor Engine would insert the new record but would not retrieve the new primary key value generated for the record. After the update, the identity column in the Recordset remained set to Null, and any subsequent attempt to update that record would fail. (You'll recall from Chapter 10 that the ADO Cursor Engine needs the primary key value to generate the action query to update a record.) Any attempt to retrieve the new identity value by calling the Resync method of the Recordset would fail because the ADO Cursor Engine needs that primary key value to determine which record in the table to use to resynchronize the current record in the Recordset.

Timestamp-Based Updates

Perhaps you're using timestamp fields to control your updates. When you initially retrieve a particular record, you're retrieving the current value of that timestamp field. The ADO Cursor Engine uses that value in the action query. But when the database system receives that action query and modifies that record, the system generates a new value for the timestamp field.

Again, the action queries performed by the ADO Cursor Engine act like a one-way street. This newly generated timestamp value is not retrieved and placed into your Recordset. Instead, the modified record in your Recordset contains the outdated timestamp value that the ADO Cursor Engine retrieved when you opened the Recordset. Therefore, a subsequent attempt to update this same record will fail.

Default Values

Database developers often use default values when defining a table. If you add a new record to your Recordset and leave the value of a field empty, that field will be set in your database to Null or to the default value (if one exists for that field). However, since that default value is generated by the database and action queries don't return information, that default value does not (by default) appear in your Recordset when you submit the record to the database.



Programming ADO
Programming MicrosoftВ® ADO.NET 2.0 Core Reference
ISBN: B002ECEFQM
EAN: N/A
Year: 2000
Pages: 131
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net