CODEWITHSUNDEEP
vbscriptadodb
feetwet
feetwet

Reputation: 3446

Correct way to reuse ADODB.Command

What is the correct way to call a sproc within a loop?

If I come in with something like this:

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

Then on the second and subsequent iterations of the loop I get an error

Procedure or function spMySproc has too many arguments specified.

Upvotes: 2

Views: 4707

Answers (1)

Kul-Tigin
Kul-Tigin

Reputation: 16950

You need to separate command preparation and the loop. Then you can use Parameters collection multiple times to execute the command.

'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

Upvotes: 5

Related Questions