Microsoft KB Archive/195224

= PRB: ADO Not Returning @@IDENTITY Value After AddNew =

Article ID: 195224

Article Last Modified on 8/10/2006

-

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 ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q195224



SYMPTOMS
ActiveX Data Objects (ADO) does not return the @@IDENTITY value after executing a Recordset.Addnew method or SQL Insert statement.



CAUSE
Here are two distinct reasons for this behavior:
 * For SQL Insert statements, ODBC version 3.5 changed the behavior with respect to SQLMoreResults such that, "output parameters aren't stored in the application's buffer until after the app calls SQLMoreResults and it returns SQL_NO_DATA_FOUND. The ODBC 2.65 driver would read-ahead and sometimes lump result sets together or skip over them. The ODBC 3.5 driver was changed to provide result sets in a consistent fashion w/o the various problems that used to occur."

NOTE: To avoid calling SQLMoreResults on such statements, which would suppress the @@IDENTITY, you must use the SET NOCOUNT ON in the SQL Insert statement.
 * For the Recordset.AddNew method, if the server cursor is built with dbcursoropen for a table not containing a unique index, the server cursor is read-only and a temporary table is created in Tempdb. Subsequent cursor fetches will be on the temporary table. Therefore, changes made by others to the rows in the base table will not be visible through the cursor.



RESOLUTION
If you use SET NOCOUNT ON in your SQL statement or a Unique Index on the table for an insert with the Recordset.AddNew method, the IDENTITY value returns as expected.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
  Paste the following code into the General Declarations sections of a new Visual Basic form (NOTE: User must have permissions to perform these operations on the database.): Dim ADOCon As ADODB.Connection

Private Sub Command1_Click

'This code creates the table. Dim ADOCmd As ADODB.Command

Set ADOCmd = New ADODB.Command With ADOCmd .ActiveConnection = ADOCon .CommandTimeout = 600 .CommandText = "if exists (select * from sysobjects " & _                       "where id = object_id('dbo.idTest') and " & _                            " sysstat & 0xf = 3) " & _ " drop table dbo.idTest" .Execute .CommandText = "CREATE TABLE dbo.idTest" & _ "(id int IDENTITY (1, 1) NOT NULL, " & _                           "col1 varchar (255) NULL , col2 datetime NULL)" .Execute 'Uncomment next two lines to return the Identity value. '.CommandText = "CREATE UNIQUE  INDEX idx_id ON dbo.idTest(id)" '.Execute End With

Label1.Caption = "idTest Table Created..."

Set ADOCmd = Nothing

End Sub

Private Sub Command2_Click

'This code performs the Inserts. Dim ADORs As Recordset

Dim strCol1 As String Dim dtCol2 As Date

strCol1 = "Hello World!" dtCol2 = Now

Set ADORs = New ADODB.Recordset With ADORs Set .ActiveConnection = ADOCon .CursorLocation = adUseServer .CursorType = adOpenKeyset .LockType = adLockOptimistic 'Uncomment this line and it works without the Unique index. '.Open "SET NOCOUNT ON;INSERT idTest(Col1, Col2) " & _ "VALUES('" & strCol1 & "', '" & dtCol2 & "');" & _ "SELECT @@IDENTITY AS ID;SET NOCOUNT OFF" 'Comment this line if you uncomment the one above. .Open "SELECT * FROM idTest WHERE 1=0" End With

'Comment these next four lines if you use the Insert SQL statement. ADORs.AddNew ADORs.Fields("Col1").Value = strCol1 ADORs.Fields("Col2").Value = dtCol2 ADORs.Update

Label1.Caption = CStr(Now) & " ADORs.id = " & ADORs("id").Value

Set ADORs = Nothing

End Sub

Private Sub Form_Load

'This code establishes the connection. Set ADOCon = New ADODB.Connection With ADOCon .CursorLocation = adUseServer .Open "Provider=MSDASQL;DRIVER={SQL           Server};SERVER=(local);User= ;password= ;DATABASE=Pubs;" End With

Label1.Caption = "Connection Established..."

End Sub

Private Sub Form_Unload(Cancel As Integer)

Set ADOCon = Nothing

End Sub  From the Project menu, choose References and select the Microsoft ActiveX Data Objects Library. Add two Command buttons. Set the Caption of the first one to Create Table and the Caption of the second Insert Record. Add one label and remove the default caption. Run the new project and click the Create Table command button. Next, click the Insert Record command button. Note in the Label.Caption that the new Identity value is not returned.</li> Uncomment the two lines of code beneath, "Uncomment next two lines to return the Identity value" in the preceding code, which creates the Unique Index on the table with the Create Table button. Repeat Step 5 and note that the Label.Caption now indicates that the Identity value returns as expected. NOTE: If you use the SQL Insert statement and uncomment or comment the appropriate code for the Insert Record button, you will notice that the Identity value returns properly and is no affected by the presence or absence of a Unique Index.</li></ol>

<div class="references_section">