Re: VB 2005 and Access Parameter Query Error

Jules wrote on Wed, 14 Dec 2005 05:53:02 -0800:

> Hi,
>
> I wonder if anyone can help me out.
>
> I've written a parameter query in Access 2003, and I'm trying to return
> the data to a VB 2005 app.
> I keep getting the following error message:
> "ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few
> parameters. Expected 1."
>
> First, the Access SQL:
>
> PARAMETERS [@user_ref] Long;
> SELECT Users.User_ref, Users.User_ID, Users.User_forename,
> Users.User_surname FROM Users
> WHERE (((Users.User_ref)=[@user_ref]));
>
> (this works as it should in Access).
>
> Next, the VB 2005 code to Access the Data Layer:
>
> Dim ds As New DataSet
> CDAL.AddParameter("[@user_ref]", 3)
> ds = CDAL.ExecuteDataSet("EXECUTE usp_user_Get",
> CommandType.StoredProcedure)

Try dropping the [] in the AddParameter method - those are only required in
the Access SQL to delimit the parameter name because you've used an @ in the
name, by including the [] in the AddParameter method you've named your
parameter [@user_ref] instead of just @user_ref to match the query. You also
might want to drop the @ in the Access SQL completely, that might be causing
problems. However, IIRC the actual names of the parameters in VB aren't
used, just their ordinal positions.

The error you're seeing can mean many things - basically, something Access
expects to see isn't there. It'll throw the same error if, for instance, you
have a typo in a field name (anything that isn't an object name is treated
as a parameter).

Dan


.