vbscript - 重用 ADODB.Command 的正确方法

Question

在循环中调用存储过程的正确方法是什么？

如果我带着这样的东西进来:

Connection = CreateObject("ADODB.Connection")
DO UNTIL RS.EOF
    SET cmd = Server.CreateObject ("ADODB.Command")
    cmd.ActiveConnection = Connection
    cmd.CommandText = "spMySproc"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter ("@p1",adInteger,adParamInput, ,RS("Val1"))
    cmd.Parameters.Append cmd.CreateParameter ("@p2",adInteger,adParamInput, ,RS("Val2"))
    cmd.Execute
    SET cmd = nothing
LOOP

然后在循环的第二次和后续迭代中出现错误

Procedure or function spMySproc has too many arguments specified.

Answer 1

您需要将命令准备和循环分开。然后可以多次使用Parameters集合来执行命令。

'preparing command
Set cmd = CreateObject ("ADODB.Command")
    cmd.ActiveConnection = Connection
    cmd.CommandText = "spMySproc"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter("@p1", adInteger, adParamInput,,0) '0 as placeholder
    cmd.Parameters.Append cmd.CreateParameter("@p2", adInteger, adParamInput,,0) '0 as placeholder

Do Until Rs.Eof
    cmd.Parameters("@p1").Value = Rs("Val1").Value
    cmd.Parameters("@p2").Value = Rs("Val2").Value
    cmd.Execute
    Rs.MoveNext
Loop