Using "SELECT SCOPE_IDENTITY ()" in an ADODB Record Set

Question

Using a VBA script in Excel, I am trying to insert a new row into a table and then return the id value of that row. If I run:

INSERT INTO DataSheet(databaseUserID, currentTimestamp) VALUES (1, CURRENT_TIMESTAMP); SELECT SCOPE_IDENTITY() 

in Management Studio, a row is inserted and returns the expected value of the identifier. However, when I run the same query through the ADODB recordset in VBA, I have problems. The row is indeed inserted, but I cannot access the identifier value. The recordset contains 0 fields and is actually closed. I tried with and without a semicolon, and I also tried to run the query as a single transaction. Same thing, no dice. Any idea what is going on?

Here is my VBA:

 Dim rs As ADODB.Recordset Dim cn As Connection Dim SQLStr As String Dim serverName As String Dim databaseName As String serverName = "MSSQLServer" databaseName = "QA" cxnStr = "Driver={SQL Server};Server=" & serverName & ";Database=" & databaseName & ";" SQLStr = "INSERT INTO DataSheet(databaseUserID, currentTimestamp) VALUES (1, CURRENT_TIMESTAMP); SELECT SCOPE_IDENTITY()" Set cn = New ADODB.Connection cn.Open cxnStr Set rs = New ADODB.Recordset rs.Open SQLStr, cn, adOpenKeyset, adLockOptimistic MsgBox (rs.Fields(0).Value) 

And the message box is not displayed because rs.Fields(0).Value returns NULL. I added the clock to rs and, as I said, shows 0 fields after the request and also seems to be closed (state = 0).

Answer 1

When you run the command package using ADODB, I believe that it runs each of them separately. To force the following command to run, you should use the following:

 Set rs = rs.NextRecordset() 

Changing the end of your routine as follows should do the trick:

 Set rs = New ADODB.Recordset rs.Open SQLStr, cn, adOpenKeyset, adLockOptimistic Set rs = rs.NextRecordset MsgBox (rs.Fields(0).Value) 

Answer 2

You perform two statements to get two results. a record set object can contain only one result at a time - to get another result, you need to use the NextRecordset method.

 Set rs = rs.NextRecordset 

Answer 3

In your rs.Open try this

rs.Open SQLStr, cn, adCmdText

Answer 4

See what happens when you delete the values ​​of adOpenKeySet and adLockOptimistic, leaving them by default.