-
May 13th, 2003, 10:52 AM
#1
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:
Do While Not .EOF
Call InitializeCounters
With cmd
.CommandText = "ReferralAssignment"
.CommandType = adCmdStoredProc
.ActiveConnection = cnCMS
Set param1 = .CreateParameter("Administrator", adInteger, adParamInput, 4, iAdministrator)
.Parameters.Append param1
Set param2 = .CreateParameter("ApptSched", adInteger, adParamOutput, 4)
.Parameters.Append param2
Set param3 = .CreateParameter("ApptLetter", adInteger, adParamOutput, 4)
.Parameters.Append param3
Set param4 = .CreateParameter("DoctorLetter", adInteger, adParamOutput, 4)
.Parameters.Append param4
Set param5 = .CreateParameter("ReportReceived", adInteger, adParamOutput, 4)
.Parameters.Append param5
Set param6 = .CreateParameter("TotalAddendums", adInteger, adParamOutput, 4)
.Parameters.Append param6
Set param7 = .CreateParameter("Assigned", adInteger, adParamOutput, 4)
.Parameters.Append param7
Set param8 = .CreateParameter("Total", adInteger, adParamOutput, 4)
.Parameters.Append param8
.Execute
iScheduled = param2.Value
iApptLtr = param3.Value
iDrLtr = param4.Value
iReportReceived = param5.Value
iAddendum = param6.Value
iAssigned = param7.Value
iTotal = param8.Value
End With
.movenext
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.
-
May 13th, 2003, 10:57 AM
#2
Hyperactive Member
For a = 0 To cmd.Parameters.Count
cmd.Parameters.Delete (a)
Next a
you will need to clear out the old parameters,,,,,,,
-
May 13th, 2003, 10:59 AM
#3
Hyperactive Member
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...
-
May 13th, 2003, 11:23 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|