Appending Parameters Leading to Great Woe

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Appending Parameters Leading to Great Woe

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-03 : 07:33:55

Rich writes "Hi All: I just started using Stored Procedures so if the question is dumb it's because I know very little about what I'm doing here :) But I'm getting closer. I think I'm misusing the append method since the first time my loop executes all goes well. The second time though I get the error message " Procedure or Function sp_xxx has too many arguments specified. ADO okays it but SQLServer2K kicks it out. Should I be clearing out the parameters collection somehow before reentering the loop?
Thanks!!
-RR
The code snippet follows:

cmd.CommandText = "sp_xxx"
cmd.CommandType = adCmdStoredProc

'# Loop through excel fields and pass to sp_xxx

For ctr = 2 to intRowCount + 1
'# load valirables
splitDate = split(objApplication.cells(ctr,18).value, " ")
strShippingDate = splitDate(0)
strtrackingNumber = objApplication.cells(ctr,2).value
strShippingNumber = objApplication.cells(ctr,3).value
strShippingStatus = objApplication.cells(ctr,1).value

'# Append varibles
cmd.Parameters.Append cmd.CreateParameter("trackingNumber", adVarChar, adParamInput, 50, strtrackingNumber)
cmd.Parameters.Append cmd.CreateParameter("ShippingNumber", adVarChar, adParamInput, 50, strShippingNumber)
cmd.Parameters.Append cmd.CreateParameter("ShippingStatus", adVarChar, adParamInput, 50, strShippingStatus)
cmd.Parameters.Append cmd.CreateParameter("ShippingDate", adVarChar, , 50, strShippingDate)
cmd.Parameters.Append cmd.CreateParameter("Distributor", adVarChar, adParamInput, 50, strDistributor)
cmd.Parameters.Append cmd.CreateParameter("Courier", adVarChar, adParamInput, 50, strCourier)
msgbox ctr
cmd.execute

ctrLoop = ctrLoop + 1
Next
set cmd = nothing"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-03 : 19:36:35

I think you should create & append parameters before For ... Next and
without optional 'value' argument... Then, inside of For ... Next you assign
these values:

For ...
cmd.Parameters(0).Value=strtrackingNumber
cmd.Parameters(1).Value=strShippingNumber
....
cmd.Execute
Next

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources