ADODB 不传入参数 [英] ADODB not passing in parameters

问题描述

我有一个带有以下参数的 SQL SP...

I have a SQL SP with the following parameters...

myProc(@param1 AS TINYINT, @sparam2 AS TINYINT, @param3 AS TINYINT, @param4 AS 
TINYINT, @param5 AS TINYINT, @param6 AS TINYINT, @param7 AS TINYINT, @paramOut 
NVARCHAR (255) OUTPUT)

我在 VBA 中有以下 ADODB 代码...

and I have the following ADODB code in VBA...

sVal1 = 1
sVal2 = 1
sVal3 = 1
sVal4 = 1
sVal5 = 1
sVal6 = 1
sVal7 = 1

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim param1, param2, param3, param4, param5, param6, param7, param8 As 
                                                     ADODB.Parameter
Dim Rs As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = "DRIVER=SQL       
              Server;SERVER=SERVER;DATABASE=PEER_DB;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString
Set cmd = New ADODB.Command
Set Rs = New ADODB.Recordset
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "myProc"

Set param1 = cmd.CreateParameter("@param1", adTinyInt, adParamInput, 1, sVal1)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("@param2", adTinyInt, adParamInput, 1, sVal2)
cmd.Parameters.Append param2
Set param3 = cmd.CreateParameter("@param3", adTinyInt, adParamInput, 1, sVal3)
cmd.Parameters.Append param3
Set param4 = cmd.CreateParameter("@param4", adTinyInt, adParamInput, 1, sVal4)
cmd.Parameters.Append param4
Set param5 = cmd.CreateParameter("@param5", adTinyInt, adParamInput, 1, sVal5)
cmd.Parameters.Append param5
Set param6 = cmd.CreateParameter("@param6", adTinyInt, adParamInput, 1, sVal6)
cmd.Parameters.Append param6
Set param7 = cmd.CreateParameter("@param7", adTinyInt, adParamInput, 1,      
adParamReturnValue)
cmd.Parameters.Append param7
Set param8 = cmd.CreateParameter("@paramOut", adVarChar, adParamOutput, 255,
adParamReturnValue)

cmd.Parameters.Append param8

Rs.CursorType = adOpenStatic
Rs.CursorLocation = adUseClient
Rs.LockType = adLockOptimistic
cmd.Execute
Rs.Open cmd

我试图做的就是传入我的 VBA sVal 变量并根据这些参数执行我的过程，然后我需要返回一直在工作的输出参数；使用 ADODB 连接时，该过程没有正确执行，因为我没有得到应有的结果.有人可以引导我朝着正确的方向前进吗?我是否遗漏了某些执行语句?

All I am attempting to do is pass in my VBA sVal variables and execute my procedure based on those parameters and then I need to return the output parameter which has been working; The procedure has not been executing properly when using the ADODB connection as I am not getting the results I should be. Can someone steer me in the right direction, am I missing some execute statement?

推荐答案

代替:

cmd.Execute
Rs.Open cmd

试试

Set rs = cmd.Execute

有关详细示例，请参阅如何使用 VBA/C++/Java 通过 ADO 查询调用存储过程.

For a detailed example, see How To Invoke a Stored Procedure with ADO Query Using VBA/C++/Java.

这篇关于ADODB 不传入参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！