在
循环中调用 sproc 的正确方法是什么?
如果我进来这样的东西:
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
然后在循环的第二次和后续迭代中,我得到一个错误
过程或函数 spMySproc 指定了太多参数。
您需要将命令准备和循环分开。然后,可以多次使用参数集合来执行命令.
'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