PRB: ADO Not Returning @@IDENTITY Value After AddNew (195224)

MORE INFORMATION

Steps to Reproduce Behavior

1. Paste the following code into the General Declarations sections of a new Visual Basic form (NOTE: User <username> 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=<username>;password=<strong password>;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.