PRB: AutoNumber Field Is Not Incremented When Using ADO

ID: Q190370

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0
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.

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

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:

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.

STATUS

This behavior is by design.

MORE INFORMATION

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

Steps to Reproduce Behavior

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.

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

Additional query words:


Keywords          : kbADO150 kbADO200 kbDatabase kbOLEDB kbVBp600 kbVC600 
Version           : WINDOWS:1.5,2.0,6.0; winnt:6.0
Platform          : WINDOWS winnt 
Issue type        : kbprb

Last Reviewed: July 13, 1999