使用“SELECT SCOPE_IDENTITY()” 在 ADODB 记录集中
在 Excel 中使用 VBA 脚本，我尝试将新行插入表中，然后获取该行的标识值。 如果我
INSERT INTO DataSheet(databaseUserID, currentTimestamp)
VALUES (1, CURRENT_TIMESTAMP);
SELECT SCOPE_IDENTITY()
在 Management Studio 中运行：，则会插入该行，并按预期提供返回的标识值。 但是，当我通过 VBA 中的 ADODB 记录集运行完全相同的查询时，我遇到了麻烦。 该行确实已插入，但我无法访问标识值。 该记录集列出了 0 个字段，并且实际上也已关闭。 我尝试过使用和不使用分号，并且我还尝试将查询作为单个事务运行。 同样的交易，没有骰子。 知道发生了什么事吗？
这是我的 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）。
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).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
看看当您删除 adOpenKeySet 和 adLockOptimistic 值并将其保留为默认值时会发生什么。
See what happens when you remove the adOpenKeySet and adLockOptimistic values leave them at their defaults.
当您使用 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:
Changing the end of your routine to the following should do the trick:
您正在执行两条语句，因此您将得到两个结果。 Recordset 对象一次只能保存一个结果 - 要获取其他结果，您需要使用 NextRecordset 方法。
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.
在你的 rs.Open 中试试这个
rs.Open SQLStr, cn, adCmdTextIn your rs.Open Try this
rs.Open SQLStr, cn, adCmdText