Execute a stored procedure multiple times using a loop.

Hello:

I am trying to execute a stored procedure multiple times using a loop based on a counter I am requesting from the previous page.

Code:

cnt = request.form("cnt")

set cmd = server.CreateObject("adodb.command")

cmd.ActiveConnection = getconn()
cmd.CommandType = adcmdStoredProc
cmd.CommandText = "spDISAUpdateSponsorStatus"

for i = 1 to cnt
idpeople = request.Form("idpeople" & i)
v50m = request.Form("v50m" & i)

cmd.Parameters.Append cmd.CreateParameter("idpeople",adInteger,adParamInput,,idpeople)
cmd.Parameters.Append cmd.CreateParameter("v50m",adVarChar,adParamInput,50,v50m)
cmd.Execute

idpeople = ""
v50m = ""
next

cmd.ActiveConnection.Close
set cmd = nothing


idpeople and v50m are being named based on the counter; example:

idpeople1 = "800123"
v50m1 = "0"
idpeople2 = "800111"
v50m2 = "1"

The error I am recieving is: Procedure or function spDISAUpdateSponsorStatus has too many arguments specified.

I think this is due to it appending each parameter so on the second loop it is trying to pass in a third parmeter when it only expects two.

Any ideas?

Thanks

I figured it out!

Have to create and initialize the parameters outside the loop and assign them values inside the loop like so:


cnt = request.form("cnt")

set cmd = server.CreateObject("adodb.command")

cmd.ActiveConnection = getconn()
cmd.CommandType = adcmdStoredProc
cmd.CommandText = "spDISAUpdateSponsorStatus"

cmd.Parameters.Append cmd.CreateParameter("idpeople",adInteger,adParamInput,,0)
cmd.Parameters.Append cmd.CreateParameter("v50m",adVarChar,adParamInput,50,"")

for i = 1 to cnt
idpeople = request.Form("idpeople" & i)
v50m = request.Form("v50m" & i)

cmd.Parameters("idpeople") = idpeople
cmd.Parameters("v50m") = v50m
cmd.Execute

idpeople = ""
v50m = ""
next

cmd.ActiveConnection.Close
set cmd = nothing