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?

ERROR [07002] [Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 1. That error tell that one parameter is missing. — Emanuele Pavanello, Aug 06 '13 at 14:23
ODBC does not support named params; http://msdn.microsoft.com/en-us/library/system.data.odbc.odbccommand.parameters.aspx it uses ordinal `?` placeholders - if your using Access is there a reason for using ODBC rather than OLEDB (which will allow them)? — Alex K., Aug 06 '13 at 14:23

Steve · Accepted Answer · 2022-06-06T21:06:42.373

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.

this is shameful !! how am I supposed to write portable database code ? — micahhoover, Nov 06 '18 at 20:56

C Sharper · Answer 2 · 2013-08-06T14:29:39.237

15

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();

edited Aug 06 '13 at 14:29

answered Aug 06 '13 at 14:24

C Sharper

8,284
26
88
151

And what if you will have a few params ? '@id', '@firstName' and '@lastName'? How to specify correct order of '?' – Schroet Jan 19 '23 at 18:17

score 1 · Answer 3 · answered Feb 04 '20 at 15:36

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 + "%";

How to bind parameters via ODBC C#?

3 Answers3

Linked