CODEWITHSUNDEEP
c#ado.netodbc
Amit Mittal
Amit Mittal

Reputation: 1127

Error while using parameters with OdbcDataAdapter

Its a bit kind of strange, but I am facing a problem which is really not an expected behavior.

Consider the following function:

 public static System.Data.DataSet getDataSet(string cmdStr, System.Collections.Hashtable parameters)
    {
        OdbcDataAdapter adap = new OdbcDataAdapter(cmdStr, getConnection());         
        foreach (object obj in parameters.Keys)
        {                
            adap.SelectCommand.Parameters.AddWithValue(obj.ToString(), parameters[obj]);
        }
        System.Data.DataSet ds = new System.Data.DataSet();
        adap.Fill(ds);
        return ds;
    }

I use this function to execute any sql command that have some parameters. For eg, consider the code:

string qryStr = "exec  bitss_savePaymentData " +
                                "@cdate=@cdate," +
                                "@payid=@payid," +
                                "@AMOUNT=@AMOUNT";
                System.Collections.Hashtable ht = new System.Collections.Hashtable();
                ht.Add("@cdate", DateTime.Now.Date);
                ht.Add("@AMOUNT", 100);
                ht.Add("@payid", 101);
                DataSet ds=getDataSet(qryStr, ht);

The problem: Above code throws an exception "Must declare the scalar variable @cdate". While the same code works if I use SQLDataAdapter instead of OdbcDataAdapter.

Any ideas what am I missing here?

I've even tried this:

string ConnectionString = "dsn=mysql;uid=sa;DATABASE=userdb;";
string qryStr = "insert into info(code) values(@code);";
OdbcConnection con = new OdbcConnection(ConnectionString);
OdbcCommand cmd = new OdbcCommand(qryStr,con );
cmd.Parameters.Add("@code", System.Data.Odbc.OdbcType.Int).Value = "999";
cmd.Connection.Open();
OdbcDataReader odbcdtr = cmd.ExecuteReader();//exception "must declare the scalar variable @code"
con.Close;

I am unable to figure out the reason the exception is coming in above code.

Upvotes: 1

Views: 1565

Answers (1)

Amit Mittal
Amit Mittal

Reputation: 1127

I've finally found the solution as given in this link.

The Odbc interface does not recognise the use of @named variables, only ? which are taken by position. You can use ?Param1, ?Param 2 for readability, but the position is all that is used.

Upvotes: 1

Related Questions