FIX: AutoNumber Field Is Not Incremented When Using ADO

When using the ODBC, Microsoft Access, or SQL Server OLE DB Providers the following may appear:

• A "0" displays in the AutoNumber (or Identity) field after adding records through the DataGrid control bound to an ActiveX Data Objects (ADO) Data Control
• A "0" is stored in the AutoNumber (or Identity) field after adding records to a recordset, using the AddNew method of the recordset.

This only occurs when the CursorLocation is set to "3" - adUseClient.

By using the client-side cursors, the OLE DB provider is unable to requery the server for the updated record, and a "0" appears in place of the correct value. When you requery the recordset, the correct value appears.

Here are two ways to resolve this issue:

1. Use Server-side cursors (Set CursorLocation to "2" - adUseServer). This may incur a greater performance hit, as the client requeries the server for each record after each insert. NOTE: This solution is not valid if your provider does not support server-side cursors, for example, the MS REMOTE provider (RDS.)
2. Use the Resynch method of the ADO Data Control's underlying recordset or the ADO Data Control's own Refresh method. Call either of these methods from the DataGrid's AfterUpdate event. Performance may be improved if the Requery is performed after adding a batch of records. If the recordset is requeried after every insert, performance may be affected.

The method you choose depends upon your design goals. A good rule of thumb would be for larger recordsets use the first option. For smaller, batch, or disconnected recordsets, use the second option.

This behavior has changed with the Jet OLE DB Provider version 4.0 and Access 2000. With this provider the autonumber field is returned for both the clientside and serverside cursors when using an Access 2000 database. The 3.51 ODBC driver for Access does not return the autonumber field for an Access 2000 database if a clientside cursor is being used.

The Jet 4.0 OLE DB Provider is available with Microsoft Data Access Components (MDAC) 2.1 and 2.5.

This behavior may also manifest itself in other OLE DB Providers.

Steps to Reproduce Behavior

1. In Visual Basic create a New Standard EXE Project.
2. On Form1 (the default form) add a Microsoft DataGrid Control 6.0 (DataGrid1) and an ADO Data Control (ADODC1).
3. Bind the ADO Data Control to the NWIND Sample database using the ODBC Provider.
4. Set the ADO Data Control's RecordSource property equal to the following:

   SELECT * FROM CATEGORIES
   					

5. Ensure that ADODC1's CursorLocation property is set to "3" - adUseClient.
6. Set the DataGrid1's DataSource property equal to ADODC1.
7. Set the DataGrid1's AllowAddNew property equal to TRUE.
8. Run the form and attempt to add a record to the bottom of the DataGrid. Notice the "0" placed in the CategoryId column of the grid.

Steps to Correct Behavior

1. Set the ADODC1's CursorLocation property equal to 2 - adUseServer.
2. Run the form again and attempt to add a record to the bottom of the DataGrid. Notice how the CategoryId column is replaced with the correct value. NOTE: This solution does not work when using the JET OLEDB provider.

Alternate Way to Reproduce Behavior

1. Use the ODDBC Administrator to create a data source name (DSN) to your sample Microsoft Access Northwind database.
2. Create a New Standard EXE Project.
3. From the Project menu, choose References and add the Microsoft ActiveX Data Objects Library to the project.
4. Paste the following code into your Form_Load event:

         Private Sub Form_Load()
   
         Dim rs As New ADODB.Recordset
   
             rs.CursorLocation = adUseClient
   
             'Comment the above line and uncomment this line to make the
             'AutoNumber field populate correctly.
             'rs.CursorLocation = adUseServer
   
             rs.Open "SELECT * FROM CATEGORIES","Provider=MSDASQL;DSN=NWind"_
                , adOpenKeyset, adLockOptimistic
   
             rs.AddNew
                 ' Remember to change the value below each time you run the
                 ' application, or you will get a key violation (you can
                 ' uncomment the & Timer to ensure your value will always be
                 ' unique)
                 rs!CategoryName = "RSDemo" '& Timer
                 rs!Description = "RS demonstration"
             rs.Update
   
             rs.MoveLast
   
             MsgBox "The AutoNumber field equals " & rs!CategoryId, _
                    vbInformation
   
         End Sub