vba 在 ADODB 记录集中使用“SELECT SCOPE_IDENTITY()”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1201089/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using "SELECT SCOPE_IDENTITY()" in ADODB Recordset
提问by JoeCool
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:
在 Excel 中使用 VBA 脚本，我试图在表中插入一个新行，然后取回该行的标识值。如果我运行：
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?
在 Management Studio 中，插入了该行，并按预期为我提供了返回的标识值。但是，当我通过 VBA 中的 ADODB 记录集运行完全相同的查询时，我遇到了麻烦。该行确实已插入，但我无法访问标识值。记录集列出了 0 个字段，实际上也已关闭。我试过使用和不使用分号，我也尝试将查询作为单个事务运行。同样的交易，没有骰子。知道发生了什么吗？
Here's my VBA:
这是我的 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).Valuereturns 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).
并且消息框无法显示，因为rs.Fields(0).Value返回 NULL。我向 rs 添加了一个手表，就像我说的那样，在查询后显示 0 个字段，并且似乎已关闭（状态 = 0）。
回答by Tim Greaves
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:
当您使用 ADODB 运行一批命令时，我相信它会单独运行每个命令。要强制运行下一个命令，您必须使用以下命令：
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)
回答by DJ.
You are executing two statements so you will get two results back. the recordset object can only hold one result at a time - to get the other result you need to use the NextRecordset method.
您正在执行两个语句，因此您将获得两个结果。记录集对象一次只能保存一个结果 - 要获得另一个结果，您需要使用 NextRecordset 方法。
Set rs = rs.NextRecordset
回答by Mike Z
In your rs.Open Try this
在你的 rs.Open 试试这个
rs.Open SQLStr, cn, adCmdText
rs.Open SQLStr, cn, adCmdText
回答by AnthonyWJones
See what happens when you remove the adOpenKeySet and adLockOptimistic values leave them at their defaults.
看看当您删除 adOpenKeySet 和 adLockOptimistic 值将它们保留为默认值时会发生什么。
