PRB: ADO Not Returning @@IDENTITY Value After AddNew

ID: Q195224

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0

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

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 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 2.0 Library.

3. Add two Command buttons. Name the first one Create Table and 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.

REFERENCES

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

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

ODBC 3.0 Programmer's Reference, volume II; topic: "SQLMoreResults", pg. 830, Microsoft Press

Inside Microsoft SQL Server 6.5, pg. 552 - 559.

SQL Server Books Online; topic: "dbcursoropen", topic: "Unique Index"

Additional query words:

Keywords          : kbADO150 kbADO200 kbDAO350 
Version           : WINDOWS:1.5,2.0
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: November 7, 1998