FIX: AutoNumber Field Is Not Incremented When Using ADO (190370)

RESOLUTION

Here are two ways to resolve this issue:

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.)
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.

MORE INFORMATION

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

Steps to Reproduce Behavior

In Visual Basic create a New Standard EXE Project.
On Form1 (the default form) add a Microsoft DataGrid Control 6.0 (DataGrid1) and an ADO Data Control (ADODC1).
Bind the ADO Data Control to the NWIND Sample database using the ODBC Provider.
Set the ADO Data Control's RecordSource property equal to the following:
```
SELECT * FROM CATEGORIES
					
```
Ensure that ADODC1's CursorLocation property is set to "3" - adUseClient.
Set the DataGrid1's DataSource property equal to ADODC1.
Set the DataGrid1's AllowAddNew property equal to TRUE.
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

Set the ADODC1's CursorLocation property equal to 2 - adUseServer.
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

Use the ODDBC Administrator to create a data source name (DSN) to your sample Microsoft Access Northwind database.
Create a New Standard EXE Project.
From the Project menu, choose References and add the Microsoft ActiveX Data Objects Library to the project.

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