Too many arguments specified?

[ChopinFan]

(Previously posted in SQL forum)
I am trying to execute a simple select query in an ADP file via code and I’m getting an error message saying the sproc has “too many arguments specified”. It’s a very simple query and I don’t see what I’m doing wrong. "Com" is my command object variable and "Con" is my connection object variable, both of which are working fine. I've searched the FAQs but found nothing. Thanks!

Code:

With Com
.ActiveConnection = Con
.CommandType = adCmdStoredProc
.CommandText = "dbo.MyProcedureName"
.Parameters.Append .CreateParameter("@Parameter", adVarChar, adParamInput, 9, Trim(FormField.Value))
End With
Set MyRecordset = Com.Execute


Sproc:

CREATE PROCEDURE dbo.MyProcedureName

@Parameter varchar(9)

AS

SELECT Field
FROM dbo.Table
WHERE (Field = @Parameter)

GO

 

[cmmrfrds]

with a varchar data type, you need to pass the length of the field.

.Parameters.Append .CreateParameter("@Parameter", adVarChar, adParamInput, 9, Trim(FormField.Value),9)

Also, I would check the value in the field before sending.

Dim avar as string
avar = Trim(FormField.Value)
debug.print avar
Also add some error checking on the field such as check for NULL.
nz(Trim(FormField.Value),"plug something") and then check.

 

[Crowley16]

you can just use the parameters collection itself and do not need to append the parameter at all...

i.e.

with com
...

.parameters("@parameter") = someValue

...
end with

--------------------
Procrastinate Now!

 

[ChopinFan]

Thanks both. As it turns out, the error message only indirectly related to my problem. My Command object variable had been used once already in my code and I had been trying to reset the parameters for it and reuse it before doing Set Com = Nothing at the end. Once I changed it so there's a Set Com = Nothing after each use and a set Com = New ADODB.Command before the following use, everything went fine. Oops!