How to bind parameters via ODBC C#?

Question

I need to bind parameters on ODBC query from C#. This is the sample code, but VS tells me that there's one parameter missing.

OdbcCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM user WHERE id = @id";
cmd.Parameters.Add("@id", OdbcType.Int).Value = 4;
OdbcDataReader reader = cmd.ExecuteReader();

What is the syntax for binding values on ODBC?

Dawnedawson

What is the exact error message? – Udall

ERROR [07002] [Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 1. That error tell that one parameter is missing. – Dawnedawson

ODBC does not support named params; msdn.microsoft.com/en-us/library/… it uses ordinal ? placeholders - if your using Access is there a reason for using ODBC rather than OLEDB (which will allow them)? – Restrict

Answer 1

Odbc cannot use named parameters. This means that the command string uses placeholders for every parameter and this placeholder is a single question mark, not the parameter name.

OdbcCommand.Parameters

Then you need to add the parameters in the collection in the same order in which they appear in the command string

OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM [user] WHERE id = ?";
cmd.Parameters.Add("@id", OdbcType.Int).Value = 4;
OdbcDataReader reader = cmd.ExecuteReader();

You have also another problem, the USER word is a reserved keyword per MS Access Database and if you want to use that as field name or table name then it is required to enclose every reference with square brackets. I strongly suggest, if it is possible, to change that table name because you will be hit by this problem very often.

Udall

this is shameful !! how am I supposed to write portable database code ? – Kafka

Answer 2

use "?" in place of @ if you are using ODBC.

Try to do as follows:

OdbcCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM user WHERE id = ?";
cmd.Parameters.Add("@id", OdbcType.Int).Value = 4;
OdbcDataReader reader = cmd.ExecuteReader();

Calcutta

And what if you will have a few params ? '@id', '@firstName' and '@lastName'? How to specify correct order of '?' – Riviera

Answer 3

To use ODBC parameterized LIKE carry out as follows, i.e. you do not use the typical single quotes or even put the % in the CommandText (Furthermore I think perhaps the %? has a special meaning for Oracle? :

OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM [user] WHERE name LIKE ?";
cmd.Parameters.AddWithValue("@fieldName", OdbcType.NVarChar).Value = "%" + nameFilter + "%";

Pep