PRB: ADO Not Returning @@IDENTITY Value After AddNew

• ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2

SYMPTOMS

ActiveX Data Objects (ADO) does not return the @@IDENTITY value after executing a Resultset.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."

• For the Resultset.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 Resultset.AddNew method, the IDENTITY value returns as expected.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Paste the following code into the General Declarations sections of a new Visual Basic form:




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);UID=sa;PWD=;DATABASE=Pubs;"
      End With

      Label1.Caption = "Connection Established..."

      End Sub

      Private Sub Form_Unload(Cancel As Integer)

      Set ADOCon = Nothing

      End Sub 

2. From the Project menu, choose References and select the Microsoft ActiveX Data Objects Library.



3. Add two Command buttons. Set the Caption of the first one to Create Table and the Caption of the second Insert Record.



4. Add one label and remove the default caption.



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



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

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Mark S. Miller, Microsoft Corporation

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

Q156489 INF: Overview of SQL Server, ODBC, and DB-Library Cursors

Additional query words:

Keywords : kbADO150 kbADO200 kbDAO350 kbDatabase kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb