vba ADODB 打开记录集失败/“对象关闭时不允许操作”

ADODB open recordset fails / "Operation is not allowed when object is closed"

提问by Lumpi

I have the following UDF in excel which uses ADO to connect to my MSSQL server. There it should execute the scalar udf "D100601RVDATABearingAllow".

我在 excel 中有以下 UDF，它使用 ADO 连接到我的 MSSQL 服务器。在那里它应该执行标量 udf“D100601RVDATABearingAllow”。

For some reason the parameters that I try to append are not send to the sql server. At the server only:

由于某种原因，我尝试附加的参数不会发送到 sql 服务器。仅在服务器上：

SELECT dbo.D100601RVDATABearingAllow

arrives.

到达。

MY EXCEL UDF:

我的 EXCEL UDF：

   Function RVDATA(Fastener) As Long

    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim Cmd1 As ADODB.Command
    Dim stSQL As String

Const stADO As String = "Provider=SQLOLEDB.1;Data ................"
'----------------------------------------------------------
Set cnt = New ADODB.Connection
 With cnt
    .ConnectionTimeout = 3
    .CursorLocation = adUseClient
    .Open stADO
    .CommandTimeout = 3
 End With
'----------------------------------------------------------
Set Cmd1 = New ADODB.Command
    Cmd1.ActiveConnection = cnt
    Cmd1.CommandText = "dbo.D100601RVDATABearingAllow"
    Cmd1.CommandType = adCmdStoredProc
'----------------------------------------------------------
Set Param1 = Cmd1.CreateParameter("Fastener", adInteger, adParamInput, 5)
Param1.Value = Fastener
Cmd1.Parameters.Append Param1
Set Param1 = Nothing
'----------------------------------------------------------
Set rst = Cmd1.Execute()
RVDATA = rst.Fields(0).Value    
'----------------------------------------------------------
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
'----------------------------------------------------------
End Function

When I use adCmdStoredProc the whole thing fails and in the vba debugger the properties of the recordset has a lot of "Operation is not allowed when object is closed" (may sound a bit different, the message is translated)

当我使用 adCmdStoredProc 时，整个事情都失败了，并且在 vba 调试器中，记录集的属性有很多“对象关闭时不允许操作”（可能听起来有点不同，消息已翻译）

When I don't use adCmdStoredProc I get the message that the variableFastener was not provided.

当我不使用 adCmdStoredProc 时，我收到消息，指出未提供变量Fastener 。

I think that maybe something is wrong in the way I open the recordset. In other treads I read about using the "SET NOCOUNT ON" option, but that did not work either.

我认为我打开记录集的方式可能有问题。在其他方面，我读到了有关使用“SET NOCOUNT ON”选项的信息，但这也不起作用。

Does anyone have a idea? Regards Lumpi

有没有人有想法？问候隆皮

回答by DigCamara

Ran into this error as well (in my case I am using a Stored Procedure to retrieve some information). I had made some changes which caused the execution to malfunction.

也遇到了这个错误（在我的例子中，我使用存储过程来检索一些信息）。我进行了一些更改，导致执行出现故障。

The error disappeared when I put SET NOCOUNT ON as the first statement of the Stored Procedure.

当我将 SET NOCOUNT ON 作为存储过程的第一条语句时，错误消失了。

回答by Roman

You do not need to SELECTthe server side function, just provide its name ("[tra-CAE400-1].dbo.D100601RVDATABearingAllow") in the .CommandTextproperty. Also you should set the .CommandTypeproperty to "stored-procedure" (property reference on w3schools.com).

您不需要SELECT服务器端功能，只需在.CommandText属性中提供其名称（“[tra-CAE400-1].dbo.D100601RVDATABearingAllow”）即可。此外，您应该将该.CommandType属性设置为“存储过程”（w3schools.com 上的属性参考）。

Then adodb will know that you are talking about calling a function, and not trying to send a plain sql-command.

然后 adodb 会知道你在谈论调用一个函数，而不是试图发送一个普通的 sql 命令。

Chances are that it will then allow you to define the parameters on the command object. But the parameters you define on the command object should correspond exactly (in name and type) to the ones that are defined as the arguments of the function in the sql server.

很有可能它随后将允许您在命令对象上定义参数。但是您在命令对象上定义的参数应该（在名称和类型上）与在 sql server 中定义为函数参数的参数完全对应。

An example from microsoft.com on using the command-object with a stored procedure

来自 microsoft.com 的关于将命令对象与存储过程一起使用的示例

ADO Reference on microsoft.com

microsoft.com 上的 ADO 参考

回答by 9Squirrels

Another possible cause of this is debug statements. I just spent far too long trying to work out why this wouldn't work for me, the Proc on the database worked fine, the data it was supposed to insert was inserted, the VBA code worked fine, but there was nothing in the recordset. Final solution was to go through the procs that had been built and remove the PRINT statements. To test if this is the problem, run your proc on SQL Server manually, then look at the messages tab of the results, if there's anything there other than "Command(s) completed successfully." you need to eliminate those messages. "SET NOCOUNT ON" will get rid of the row count messages, but there may be others.

另一个可能的原因是调试语句。我只是花了太长时间试图弄清楚为什么这对我不起作用，数据库上的 Proc 工作正常，插入了它应该插入的数据，VBA 代码工作正常，但记录集中没有任何内容. 最终的解决方案是通过已经构建的过程并删除 PRINT 语句。要测试这是否是问题，请在 SQL Server 上手动运行您的 proc，然后查看结果的消息选项卡，如果除了“命令成功完成”之外还有其他任何内容。您需要消除这些消息。“SET NOCOUNT ON”将删除行计数消息，但可能还有其他消息。

I'm assuming that after 5 years the OP has solved this particular problem, so this is just for anyone like me that finds this while searching for the same problem.

我假设 5 年后 OP 已经解决了这个特定问题，所以这仅适用于像我这样在搜索相同问题时发现此问题的任何人。

回答by Tom Regan

In our shop we often use lines like this in our stored procedures to assist with debugging:

在我们的商店中，我们经常在存储过程中使用这样的行来帮助调试：

RAISERROR('Debug message here',0,1) WITH NOWAIT;

This also breaks opening a recordset in Excel vba. I believe the complete answer for this question is, in the stored procedure:

这也会中断在 Excel vba 中打开记录集。我相信这个问题的完整答案是，在存储过程中：

1. use SET ROWCOUNT OFF
2. remove all PRINT statements
3. remove all RAISEERROR statements used for debugging (ie severity of 0)

1. 使用 SET ROWCOUNT OFF
2. 删除所有 PRINT 语句
3. 删除所有用于调试的 RAISEERROR 语句（即严重性为 0）

回答by Drew Thompson

I also ran into this with a stored procedure. Did you SET NOCOUNT = OFF; at the bottom of your code? That is what worked for me after lots of googling. Also, if you have any other code that runs, you have to wrap it in Nocount = on/off, INCLUDING insert and update statements. You would think that an insert statement wouldn't matter but wrapping the code that way is what kept me from committing suicide today.

我也用存储过程遇到了这个问题。您是否设置了 NOCOUNT = OFF; 在你的代码底部？这就是经过大量谷歌搜索后对我有用的方法。此外，如果您有任何其他运行的代码，则必须将其包装在 Nocount = on/off, INCLUDING insert 和 update 语句中。您可能会认为插入语句无关紧要，但以这种方式包装代码使我今天不会自杀。