问题描述
我在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打开记录集失败/“关闭对象时不允许操作"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!