使用“ SELECT SCOPE_IDENTITY（）”；在ADODB记录集中 [英] Using "SELECT SCOPE_IDENTITY()" in ADODB Recordset

问题描述

在Excel中使用VBA脚本，我试图将新行插入表中，然后获取该行的标识值。如果我运行：

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()

在Management Studio中，该行已插入，它给了我返回预期的身份值。但是，当我通过VBA中的ADODB记录集运行完全相同的查询时，遇到了麻烦。该行确实已插入，但我无法访问该标识值。记录集列出了0个字段，并且实际上也已关闭。我尝试使用和不使用分号，也尝试将查询作为单个事务运行。同样的交易，没有骰子。知道发生了什么吗？

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?

这是我的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)

消息框无法显示，因为 rs.Fields（0）.Value 返回NULL。我在rs中添加了一个手表，就像我说的，在查询后显示0个字段，并且似乎也处于关闭状态（状态= 0）。

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

推荐答案

使用ADODB运行一批命令时，我相信它会分别运行每个命令。要强制运行下一条命令，必须使用以下命令：

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)

这篇关于使用“ SELECT SCOPE_IDENTITY（）”；在ADODB记录集中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！