Excel VBA 执行 SQL Server 存储过程 - 结果集抛出错误 3704
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18488135/
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
Excel VBA executing SQL Server stored procedure - result set throwing error 3704
提问by Abhishek Panda
I am trying to execute a SQL Server stored procedure from Excel VBA. The procedure returns rows into a result set object. However, while running the code, it throws an error:
我正在尝试从 Excel VBA 执行 SQL Server 存储过程。该过程将行返回到结果集对象中。但是，在运行代码时，它会引发错误：
3704 Operation is not allowed when the object is closed
3704 对象关闭时不允许操作
Note:
There is no problem with the database connection because Selectquery running on the same connection object are working fine.
注意：
数据库连接没有问题，因为Select在同一个连接对象上运行的查询工作正常。
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As New ADODB.Recordset
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
ThisWorkbook.initialize
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = ThisWorkbook.server
cn.Properties("Initial Catalog").Value = ThisWorkbook.db
cn.Properties("User ID").Value = "xxxxx"
cn.Properties("Password").Value = "xxxxx"
cn.Open
Set cmd = New ADODB.Command
cmd.CommandText = "Generate_KPI_Process_Quality_Check_RunTime"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cn
Set prm = cmd.CreateParameter("@currentMonth", adChar, adParamInput, 255, cmb_month.Value)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@center", adChar, adParamInput, 255, cmb_center.Value)
cmd.Parameters.Append prm
rst.CursorType = adOpenStatic
rst.CursorLocation = adUseClient
rst.CursorLocation = adUseServer
rst.LockType = adLockOptimistic
rst.Open cmd
If (rst.BOF And rst.EOF) Then
'Some Code
End If
回答by nzeemin
Put
放
SET NOCOUNT ON
in the stored procedure -- this will prevent output text generation like "1 record(s) updated".
在存储过程中——这将阻止输出文本生成，如“1 条记录更新”。
回答by nzeemin
You have to provide more parameters for the Openmethod of Recordset Object
你必须为提供更多参数Open的方法Recordset Object
try rst.Open cmd, cn
尝试 rst.Open cmd, cn
回答by Tarik
Use the Set keyword to assign the object:
使用 Set 关键字来分配对象：
Set cmd.ActiveConnection = cn
otherwise, the default property of the Connection object (which happen to be the connection string) will be assigned in lieu of the Connection object itself.
否则，Connection 对象的默认属性（恰好是连接字符串）将被分配来代替 Connection 对象本身。
回答by mishimam
Just put another recordset that will contain resultsets
只需放置另一个包含结果集的记录集
Dim rst1 As New ADODB.Recordset
SET rst1=rst.NextRecordset 'this will return the first resultset
If rst1.BOF or rst1.EOF Then...
'some code
End If
