ASP.NET ODBC Query with parameters

Question

Please help me, I don't know what can be wrong with the following code:

        OdbcConnection conn = new OdbcConnection(connString);
        String query = "INSERT INTO customer (custId, custName, custPass, "+
                       "custEmail, custAddress, custAge) VALUES (" +
                       "@ID, @Name, @Pass, @Email, @Address, @Age)";

        OdbcCommand exe = new OdbcCommand(query, conn);
        exe.Parameters.Add("@ID", OdbcType.UniqueIdentifier).Value = id;
        exe.Parameters.Add("@Name", OdbcType.VarChar).Value = name;
        exe.Parameters.Add("@Pass", OdbcType.VarChar).Value = pass;
        exe.Parameters.Add("@Email", OdbcType.VarChar).Value = email;
        exe.Parameters.Add("@Address", OdbcType.VarChar).Value = address;
        exe.Parameters.Add("@Age", OdbcType.Int).Value = age;
        conn.Open();
        exe.ExecuteNonQuery(); // ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 6.

This code throws me Too few parameters. error when I am trying to execute query. The database is fine, it works fine when I hardcode values into a query, instead of using parameters.

Thank you.

try changing pass to passw maybe it is getting mixed up with asp identifier... — halocursed
– halocursed, Commented Oct 8, 2009 at 6:49

bluish · Accepted Answer · 2012-10-16 08:12:23Z

32

From MSDN:

When CommandType is set to Text, the .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Rewrite your query to

OdbcConnection conn = new OdbcConnection(connString);
    String query = "INSERT INTO customer (custId, custName, custPass, "+
                   "custEmail, custAddress, custAge) VALUES (" +
                   "?, ?, ?, ?, ?, ?)";

Order of Parameter counts!

EDIT: Parameter can be added this way:

OdbcCommand exe = new OdbcCommand(query, conn);
exe.Parameters.Add("ID", OdbcType.UniqueIdentifier).Value = id;
exe.Parameters.Add("Name", OdbcType.VarChar).Value = name;
exe.Parameters.Add("Pass", OdbcType.VarChar).Value = pass;
exe.Parameters.Add("Email", OdbcType.VarChar).Value = email;
exe.Parameters.Add("Address", OdbcType.VarChar).Value = address;
exe.Parameters.Add("Age", OdbcType.Int).Value = age;

edited Oct 16, 2012 at 8:12

bluish

27.6k28 gold badges126 silver badges185 bronze badges

answered Oct 8, 2009 at 7:03

Arthur

8,1593 gold badges32 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Amberlea Moore Over a year ago

Using ODBC to FilMaker, this syntax worked perfectly in .NET environment, and YES order of the parameters count!

user736893 Over a year ago

If the query parameters aren't named, what purpose does the first parameter in Parameters.Add() serve?

Arthur Over a year ago

@Scott: Other Data Provider are using named parameter. So I guess the reason is to have same API

Ben Keene Over a year ago

You may still want to use names so you can look up the parameter to set the parameter for subsequent calls. I haven't tried it in OdbcCommand yet, but they are useful for other database providers.

Myra · Accepted Answer · 2009-10-08 06:50:00Z

0

One of your columns in your query does not exist.
Please check your column names.

answered Oct 8, 2009 at 6:50

Myra

3,6663 gold badges40 silver badges47 bronze badges

Comments

Cam Soper · Accepted Answer · 2009-10-08 06:51:05Z

0

Typically you'll see this when you misspell a column name in your SQL statement. Are you sure of those column names (custId, custName, etc.)?

answered Oct 8, 2009 at 6:51

Cam Soper

1,5199 silver badges10 bronze badges

1 Comment

Maksim Vi. Over a year ago

i am pretty sure, since if I change third line of my query to actual values it works fine.

halocursed · Accepted Answer · 2009-10-08 06:51:52Z

-1

try changing pass to passw maybe it is getting mixed up with asp identifier...

answered Oct 8, 2009 at 6:51

halocursed

2,4915 gold badges28 silver badges35 bronze badges

Collectives™ on Stack Overflow

ASP.NET ODBC Query with parameters

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related