INFO: Identity (AutoIncrement) Columns in ADO or RDS

How to use identity values in client cursors is one of the most prevalent issues facing developers who use ActiveX Data Objects (ADO) or the Remote Data Service (RDS). This article examines some of the issues you might encounter with identity columns in MDAC technologies through ADO or RDS 2.0.

Starting with ADO 2.1, new functionality has been added that makes the process of utilizing identity columns easier, although much of the content in this article still applies.

Note that the new functionality depends on support by the OLE DB provider and the database itself.

Specifically, using ADO 2.1 and later, ADO clientside cursors can retrieve newly-added Access Autonumber fields:

• Using the Microsoft Jet OLE DB Provider 4.0 to connect to Microsoft Access 2000 databases.

In addition, using ADO 2.1 and later, ADO clientside cursors can retrieve newly added SQL Server Identity fields:

• Using the Microsoft OLE DB Provider for SQL Server to connect to Microsoft SQL Server 6.5 Service Pack 5a and later.
• Using the Microsoft OLE DB Provider for SQL Server to connect to Microsoft SQL Server 7 and later.
• Using the Microsoft OLE DB Provider for ODBC and the ODBC driver for SQL Server to connect to Microsoft SQL Server 7 and later.

To take advantage of this functionality with Microsoft Visual Basic 6, you must use ADO code. For example, the Microsoft DataGrid Control 6.0 bound to an ADO Data Control will not retrieve newly added AutoNumber/Identity fields: it will retrieve 0 only.

For more information, please see the articles listed in the "References" section.

Retrieving the new auto-numbered fields in a client cursor is rather complex. With ADO's universal approach to data, this is difficult because database systems do not have a standard way to retrieve this information.

Jet handles this well when it communicates with an Access database because that is its native database format. Jet also handles this scenario well when working with a SQL Server database because it uses a server-side cursor behind the scenes to perform the insertion. With SQL Server, you can run a query of SELECT @@identity to retrieve the last identity value used on that particular connection. Currently, the Access ODBC driver and the Access OLE DB provider do not have that support. Oracle does not have auto- numbered fields.

With the ADO client cursor engine, the new rows are added to the database as a result of a query that looks like the following: Prior to ADO 2.1, there was no two-way communication. As a result, the auto-numbered field was not in the Recordset and ADO had no way to retrieve that row from the database in the Resync method without it. ADO was not able to tell you the auto-numbered value for the row you just entered.

Resync is great for determining why conflicts occur and retrieving information about a particular row. For example, you might have a trigger that modifies a field in the row you just updated and you might want to retrieve that information immediately after performing your update. Remember that ADO's client cursor engine uses action queries to update the database and this type of information would not be returned otherwise. However, in a case where you want to retrieve a new identity value, Resync is not the answer. It needs some way to find the current row in the table, which in this case is that identity value.

The only way to create the identity value is to add new rows to the database manually. You can walk through the Recordset and determine which rows are pending updates by looking for a Status of adRecNew. You can open up a new server-side cursor and use the information from the Recordset. Then you can use the AddNew method on the server-side cursor, checking the ID field after calling Update. If you are using Access as your server, this is your only option.

SQL Server has a special query that allows you to retrieve the last auto- increment value used on that particular connection. The query is as follows: However, it does not solve every problem. If your insert fires a trigger that inserts a row into another table that also uses an auto-incrementing field, running this query after your insert will retrieve the value for the insert that the trigger performed. Also, this query will return Null after ADO inserts the row through Update or UpdateBatch because ADO wraps the insertion in a stored procedure. Once the stored procedure is finished, this query will not return the value used in that stored procedure.

If you are using SQL Server and you need to retrieve these values, you have a couple of options available to you beyond the server-side cursor method described previously. One is to build your own INSERT INTO query and run the SELECT @@identity query afterwards. The other option is to call a stored procedure designed to insert the new row through an INSERT INTO query and return the identity value through the SELECT @@identity query in the form of an output parameter.

It is not recommended that you try to pass information back from a middle tier to a client application in an effort to keep the Recordset on the client fresh enough to perform repeated updates. While it is possible in some cases, the code that is required to do this can be cumbersome. In the case of a new row with an identity field, the identity field is marked as read-only in the Recordset. Even if your middle-tier component returns the value of the identity field, you cannot force the information into the Recordset on the client in an effort to perform updates on that row, unless you want to pass the new identity value back to the middle tier along with the changes made to that row. Instead, it is preferable to have the client request that information again to make changes to it.

Alternately, you do not have to utilize an identity/autonumber column in your database. You could implement a business object that dispenses "identity" column information directly to your application. This dispenser should be free-threaded, in case multiple applications are using it, and it probably needs to work with clients across a network. In this case, a Microsoft Transaction Server implementation would work the best.

For additional information, please click the article numbers below to view the articles in the Microsoft Knowledge Base: