Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Sep 2007
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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:
    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

  • #2
    New Coder
    Join Date
    Sep 2007
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured it out!

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

    Code:
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •