本文介绍了ADODB打开记录集失败/“关闭对象时不允许操作"的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在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

推荐答案


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

09-05 07:37
查看更多