Microsoft KB Archive/190370

= FIX: AutoNumber Field Is Not Incremented When Using ADO =

Article ID: 190370

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual C++ 6.0 Enterprise Edition
 * Microsoft Visual C++ 6.0 Professional Edition
 * Microsoft Visual C++ 6.0 Standard Edition

-



This article was previously published under Q190370



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:
 * 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.



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



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.</li> Set the DataGrid1's DataSource property equal to ADODC1.</li> Set the DataGrid1's AllowAddNew property equal to TRUE.</li> 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.</li></ol>

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
<ol> Use the ODDBC Administrator to create a data source name (DSN) to your sample Microsoft Access Northwind database.</li> Create a New Standard EXE Project.</li> From the Project menu, choose References and add the Microsoft ActiveX Data Objects Library to the project.</li>  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 </li></ol>

Keywords: kbbug kbfix kbdatabase kbmdacnosweep KB190370

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.