Results 1 to 4 of 4

Thread: Looping a stored procedure * resolved*

  1. #1

    Thread Starter
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Looping a stored procedure * resolved*

    This is probably simple but I don't see the answer. I'm using a stored procedure in a loop. The first call works fine. The second appends the parameters to the first call and it errors out with a too many parameters passed message. How do I clear it out for the next iteration of the loop? Here is basically what I'm doing. The PARAM*s are declared prior to the execute, but just once.

    VB Code:
    1. Do While Not .EOF
    2.                Call InitializeCounters
    3.             With cmd
    4.               .CommandText = "ReferralAssignment"
    5.               .CommandType = adCmdStoredProc
    6.               .ActiveConnection = cnCMS
    7.               Set param1 = .CreateParameter("Administrator", adInteger, adParamInput, 4, iAdministrator)
    8.               .Parameters.Append param1
    9.               Set param2 = .CreateParameter("ApptSched", adInteger, adParamOutput, 4)
    10.               .Parameters.Append param2
    11.               Set param3 = .CreateParameter("ApptLetter", adInteger, adParamOutput, 4)
    12.               .Parameters.Append param3
    13.               Set param4 = .CreateParameter("DoctorLetter", adInteger, adParamOutput, 4)
    14.               .Parameters.Append param4
    15.               Set param5 = .CreateParameter("ReportReceived", adInteger, adParamOutput, 4)
    16.               .Parameters.Append param5
    17.               Set param6 = .CreateParameter("TotalAddendums", adInteger, adParamOutput, 4)
    18.               .Parameters.Append param6
    19.               Set param7 = .CreateParameter("Assigned", adInteger, adParamOutput, 4)
    20.               .Parameters.Append param7
    21.               Set param8 = .CreateParameter("Total", adInteger, adParamOutput, 4)
    22.               .Parameters.Append param8
    23.               .Execute
    24.                  
    25.              iScheduled = param2.Value
    26.              iApptLtr = param3.Value
    27.              iDrLtr = param4.Value
    28.              iReportReceived = param5.Value
    29.              iAddendum = param6.Value
    30.              iAssigned = param7.Value
    31.              iTotal = param8.Value
    32.              End With
    33. .movenext
    34. loop

    There's more to the code but the problem seems to be doing the appends.
    Last edited by TysonLPrice; May 13th, 2003 at 11:28 AM.

  2. #2
    Hyperactive Member
    Join Date
    Nov 1999
    Location
    Galway, Ireland
    Posts
    316
    For a = 0 To cmd.Parameters.Count
    cmd.Parameters.Delete (a)
    Next a


    you will need to clear out the old parameters,,,,,,,
    Slan

  3. #3
    Hyperactive Member
    Join Date
    Nov 1999
    Location
    Galway, Ireland
    Posts
    316
    After looking closer your code doesn;t seem to make sense as you will only have the results for the last record as you don't add them up or anything.......

    Perhaps you should sum them in your stored procedure or something...
    Slan

  4. #4

    Thread Starter
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834
    Thanks everyone! I just used set cmd = nothing after the call and the cleared it out for the next call.

    As far as the comment about it looks like nothing is added up I did a select distinct on the administrator and sum all the records up in the stored procedure based on certain criteria.

Posting Permissions

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



Click Here to Expand Forum to Full Width