PRB: AutoNumber Field Is Not Incremented When Using ADO

• ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2
• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 6.0
• Microsoft Visual C++, 32-bit Editions, version 6.0

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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.

STATUS

This behavior has changed with the JetOLEDB 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 ODBC drivers for Access do not return the autonumber field for an Access 2000 database if a clientside cursor is being used.

MORE INFORMATION

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

Steps to Reproduce Behavior

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

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Johnathan Johnson, Microsoft Corporation

Additional query words: kbADO150 kbADO200 kbDatabase kbOLEDB kbVBp600 kbVC600 kbmdac210sp2 kbado210sp2

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:1.5,2.0,2.1 SP2,6.0; winnt:6.0
Platform : WINDOWS winnt
Issue type : kbprb