Trouble with parameterized query

Question

I'm having trouble with a parameterized query in ODBC that usually works when I'm working with SQLClient objects.

using (OdbcConnection conn = new OdbcConnection())
{
    conn.ConnectionString = myConnectionString;
    conn.Open();

    using (OdbcCommand comm = new OdbcCommand())
    {
        comm.Connection = conn;
        comm.CommandText = "SELECT MYTABLE.MYCOLUMN FROM MYSCHEMA.MYTABLE WHERE MYCOLUMN = @MYPARAM";
        comm.Parameters.Add(new OdbcParameter("@MYPARAM",myValue));

        using (OdbcDataReader reader = comm.ExecuteReader)
        {
            //do stuff
        } 
    }
}

The message I get is

ERROR [42S22] [IBM][CLI Driver][DB2/AIX64] SQL0206N "@MYPARAM" is not valid in the context where it is used. SQLSTATE=42703

But if I'm using the SQLClient namespace I can replace Odbc with Sql everywhere above and it works fine. What am I missing?

Answer 1

From OdbcParameter.ParameterName Property

Instead of named parameters, the ODBC .NET Provider uses positional parameters that are marked with a question mark (?) in the syntax of the command text. Parameter objects in the OdbcParameterCollection and the actual parameters accepted by the stored procedure or parameterized SQL statement correspond to each other based on the order in which the OdbcParameter objects are inserted into the collection instead of by parameter name.

That's why you should use your parameters as;

comm.CommandText = @"SELECT MYTABLE.MYCOLUMN FROM MYSCHEMA.MYTABLE
                     WHERE MYCOLUMN = ?";
comm.Parameters.Add(new OdbcParameter("@MYPARAM",myValue));

Actually, it doesn't matter what you write parameter name in OdbcParameter constructor because the real important thing is order for OdbcParameter parameters.

That's why both bottom lines work for your CommandText because you have only one parameter :-)

comm.Parameters.Add(new OdbcParameter("user2320861",myValue));
comm.Parameters.Add(new OdbcParameter("meaningless string",myValue));