ADODB打开记录集失败/“关闭对象时不允许操作" [英] ADODB open recordset fails / “Operation is not allowed when object is closed”

问题描述

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

由于某种原因，我尝试附加的参数未发送到sql服务器.仅在服务器上:

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

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

SELECT dbo.D100601RVDATABearingAllow

到达.

我的EXCEL UDF:

arrives.

MY 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

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

当我不使用adCmdStoredProc时，我收到一条消息，提示未提供变量Fastener.

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

有人有主意吗?关于

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)

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

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.

Does anyone have a idea? Regards

推荐答案

您确定连接正确打开了，没有检查错误的地方.因此，如果打开连接失败，请继续打开它，然后对关闭的对象执行操作.

Are you sure the connection opens correctly, you have no error checking in place. So if opening of the connection fails you continue as it was opened and you are performing operations on a closed object.

这篇关于ADODB打开记录集失败/“关闭对象时不允许操作"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！