Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

excel - Using "SELECT SCOPE_IDENTITY()" in ADODB Recordset

Using a VBA script in Excel, I'm trying to insert a new row into a table and then get back the identity value of that row. If I run:

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

in Management Studio, the row is inserted and it gives me the returned identity value as expected. However, when I run the exact same query through a ADODB recordset in VBA, I'm having trouble. The row is indeed inserted, but I can't access the identity value. The recordset lists 0 fields and has actually been closed as well. I've tried with and without the semicolon, and I also tried running the query as a single transaction as well. Same deal, no dice. Any idea what is going on?

Here's 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 fails to display because the rs.Fields(0).Value returns NULL. I added a watch to rs, and, like I said, shows 0 fields after the query and also appears to be closed (state = 0).

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

When you run a batch of commands using ADODB, I believe it runs each one seperately. To force the next command to run, you have to use the following:

Set rs = rs.NextRecordset()

Changing the end of your routine to the following should do the trick:

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...