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, Oct 08 '09 at 06:49

score 32 · Accepted Answer · edited Oct 16 '12 at 08:12

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 '12 at 08:12

bluish

26,356
27
122
180

answered Oct 08 '09 at 07:03

Arthur

7,939
3
29
46

1

Using ODBC to FilMaker, this syntax worked perfectly in .NET environment, and YES order of the parameters count! – Amberlea Moore Jul 16 '13 at 19:40
If the query parameters aren't named, what purpose does the first parameter in `Parameters.Add()` serve? – Aug 26 '16 at 20:49
@Scott: Other Data Provider are using named parameter. So I guess the reason is to have same API – Arthur Sep 15 '16 at 11:40
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. – Ben Keene Oct 18 '18 at 17:41

score 0 · Answer 2 · answered Oct 08 '09 at 06:50

0

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

answered Oct 08 '09 at 06:50

Myra

3,646
3
38
47

score 0 · Answer 3 · answered Oct 08 '09 at 06:51

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 08 '09 at 06:51

Cam Soper

1,499
9
10

i am pretty sure, since if I change third line of my query to actual values it works fine. – Maksim Vi. Oct 08 '09 at 07:06

score 0 · Answer 4 · answered Oct 08 '09 at 06:51

0

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

answered Oct 08 '09 at 06:51

halocursed

2,451
5
27
34

ASP.NET ODBC Query with parameters

4 Answers4

Linked

Related