INFO: Identity and Auto-Increment Fields in ADO 2.1 and Beyond

ID: Q233299

The information in this article applies to:

ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2

SUMMARY

ADO 2.1 can greatly simplify the process of retrieving identity or auto-increment fields into client-side recordsets. This article is designed to help you understand how this feature works and decide if it's right for you.

MORE INFORMATION

Retrieving new values for auto-increment fields has been a complex and confusing task in past versions of ADO. You generally needed to use a server-side dynamic cursor in order to determine the value of the auto-increment field generated for your new row. You can learn more about this in Microsoft Knowledge Base Article:

Q195910 INFO: Identity (AutoIncrement) Columns in ADO or RDS"/>

ADO 2.1 simplifies this process to the point where the new auto-increment value appears to be retrieved "auto-magically" in a client-side cursor.

It's important to understand how ADO attempts to retrieve this information in order to determine if this functionality will work properly with your particular scenario.

When you add a new row to a client-side recordset in ADO through the AddNew and Update methods, ADO generates a query to insert that new row into your database. The query looks something like:


INSERT INTO Orders (CustomerID, EmployeeID, ProductID, Quantity, ...)
            VALUES ('ALFKI', 1, 7, 5, ...)

ADO then passes this query along to the OLE DB provider and it's up to the OLE DB provider or the back-end database to actually insert the new row into the table.

Prior to ADO 2.1, this was the end of the work done by ADO in this case. ADO did not attempt to retrieve the value of the auto-increment field for the newly-created row in the table. Many database programmers with SQL Server experience might remember that SQL Server supported a query to retrieve such data, SELECT @@identity. The Jet OLE DB provider (as of version 4.0) now supports the same query. For additional information, please see the following article in the Microsoft Knowledge Base:

Q232144 INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity

This query retrieves the last auto-increment value generated on a particular connection. There are two things to keep in mind:

The first is that work done on other connections will not affect the value returned on a particular connection. If you add a row to a table with an auto-increment field but do not issue the SELECT @@identity query until after another user adds a row to the same table, you will still retrieve the auto-increment value that you generated on your connection.
The second important piece of information is that insert triggers may cause you to retrieve a different value than you would have expected. For example, assume that you insert a row into a table that uses an auto-increment field. If there's a trigger on that table that causes you to insert a row into another table that also uses an auto-increment field, the SELECT @@identity query will still retrieve the last auto-increment value generated on your connection. This means that the value you receive will correspond to the auto-increment value generated on the second table rather than on the table that you referenced directly. Keep this behavior in mind if you plan on retrieving auto-increment values in your application.

Starting with ADO 2.1, the ADO client cursor engine uses this query to try to retrieve the new auto-increment value and place that in the appropriate field in your Recordset. If you add a row to your recordset and that recordset contains an auto-increment value, ADO will issue the SELECT @@identity query after the INSERT INTO ... query.

Keep in mind that this feature relies on the underlying OLE DB provider or ODBC driver. If you're using any of the following scenarios, your client-side recordset will successfully retrieve the new auto-increment value:

OLE DB Provider / ODBC Driver	Database
Microsoft SQL Server OLE DB Provider	Microsoft SQL Server 7.0
Microsoft ODBC Driver for SQL Server	Microsoft SQL Server 7.0
Microsoft SQL Server OLE DB Provider	Microsoft SQL Server 6.5 (SP 5 and above)
Microsoft Jet OLE DB Provider 4.0	Microsoft Jet 4.0 databases

Also remember that if your client-side recordset uses a LockType of adLockBatchOptimistic, you will not see the newly-generated auto-increment values until you call the UpdateBatch method.

Additional query words: Identity AutoIncrement

Keywords : kbADO kbDatabase kbJET kbMDAC kbOLEDB210 kbGrpVBDB kbGrpMDAC
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2
Platform : WINDOWS
Issue type : kbinfo