INFO: Identity (AutoIncrement) Columns in ADO or RDS (195910)

MORE INFORMATION

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.