stored procedure has too many arguments specified 1

[kel1981b]

Here is my another problem.
I need to check Database for multiple records. To do that
I created code like that

With cmdReport
        .ActiveConnection = cnReport
        .CommandType = adCmdStoredProc
        .CommandText = strSP_name
        
         If Mid(strSP_name, 16, 2) = "AK" Then
            For i = 0 To lstClients.ListCount - 1
            .Parameters.Append .CreateParameter("@stDate", adVarChar, adParamInput, 10, strToday)
            .Parameters.Append .CreateParameter("@client", adVarChar, adParamInput, 10, lstClients.List(i))
           
           Set rsReport = .Execute
               'some other code not related with  SQL                                                
           Next
        End if
End with

For some reason I keep getting Error like "procedure has too many arguments specified" when program is going second times through the loop. Seems to me something (I thing .Parameters) must be refreshed after every lap in loop. I tried to use .Refresh/Delete methods but it did not help. Thakx a lot for any help/advisw

 

[SonOfEmidec1100]

Dim x As Integer

Then add this before the append

For x = 0 To .Parameters.Count - 1
.Parameters.Delete (x)
Next

hope it helps

cjw

 

[zemp]

You are adding two parameters each time you are going through the loop. Just change the parameter values inside the loop. Try,

With cmdReport
.ActiveConnection = cnReport
.CommandType = adCmdStoredProc
.CommandText = strSP_name

If Mid(strSP_name, 16, 2) = "AK" Then
.Parameters.Append .CreateParameter("@stDate", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("@client", adVarChar, adParamInput, 10)
For i = 0 To lstClients.ListCount - 1
.Parameters("@stDate&quot.value = strToday
.Parameters("@client&quot.value = lstClients.List(i)

Set rsReport = .Execute
'some other code not related with SQL
Next
End if
End with
Thanks and Good Luck!

zemp

 

[kel1981b]

Thanks SonOfEmidec1100 and zemp. I'll try it tomorrow

 

[kel1981b]

Thanks zemp. You are good! In my 1000 points scale, you got 2000.

 

[zemp]

Glad to be of help. Thanks and Good Luck!

zemp