Error in c# odbcCommand parameterization

Question

Executing the below code gives me error. Column 'Username' cannot be null
Values are being passed in the variables. But I think the OdbcCommand statement is not prepared properly.

    OdbcCommand cmd = new OdbcCommand 
    { 
    CommandText = 
    "INSERT INTO orders(username,name,email,address,contact_number,html_email)
    VALUES(@username,@name,@email,@address,@contact_number,@html_email)",

    Connection = Con 
    };

    cmd.Parameters.AddWithValue("@username", username);
    cmd.Parameters.AddWithValue("@name", name);
    cmd.Parameters.AddWithValue("@email", email);
    cmd.Parameters.AddWithValue("@address", add);
    cmd.Parameters.AddWithValue("@contact_number", contact);
    cmd.Parameters.AddWithValue("@html_email", table);

    billid = cmd.ExecuteScalar().ToString();

are you sure that username value being passed is not null? try to debug it and see. — Sudhakar Tillapudi, Feb 15 '14 at 07:47
@SudhakarTillapudi Yes, I have already checked it. All values are being passed. I doubt if this approach works with mysql. I use it with MSSqlServer but doubtfull about mysql. — , Feb 15 '14 at 07:49

Sachin · Answer 1 · 2014-02-15T09:05:47.560

Use ExecuteNonQuery instead of ExecuteScalar() to execute the insert statement.

int affectedRows = cmd.ExecuteNonQuery();

You probably want to return the last inserted record Id, that's why you are using ExecuteScalar. OdbcCommand doesn't support then named parameters, so you need to use the placeholder in the query. So overall you need to change the query to this

CommandText = "INSERT INTO orders(username,name,email,address,contact_number,html_email)
               VALUES(?,?,?,?,?,?) SELECT SCOPE_IDENTITY()";

Now you can use the ExecuteScalar()

billid = cmd.ExecuteScalar().ToString();

Sudhakar Tillapudi · Answer 2 · 2014-02-15T08:53:58.640

0

Try This:

OdbcCommand cmd = new OdbcCommand("INSERT INTO orders(username,name,email,address,contact_number,html_email)
VALUES(@username,@name,@email,@address,@contact_number,@html_email)",Con);

cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@address", add);
cmd.Parameters.AddWithValue("@contact_number", contact);
cmd.Parameters.AddWithValue("@html_email", table);

billid = cmd.ExecuteNonQuery();

edited Feb 15 '14 at 08:53

answered Feb 15 '14 at 07:53

Sudhakar Tillapudi

25,935
5
37
67

Odbc is designed to work with MySql database too so using MySqlCommand is not necessary. Xint0's answer solved my problem. Thanks for your help and I appreciate you took effort looking into it. – Feb 15 '14 at 08:52

score 0 · Accepted Answer · answered Feb 15 '14 at 08:33

OdbcCommand does not support named parameter syntax, instead you should use a question mark placeholder:

OdbcCommand cmd = new OdbcCommand 
{ 
CommandText = 
"INSERT INTO orders(username,name,email,address,contact_number,html_email)
VALUES(?, ?, ?, ?, ?, ?)",

Connection = Con 
};

cmd.Parameters.AddWithValue("@p1", username);
cmd.Parameters.AddWithValue("@p2", name);
cmd.Parameters.AddWithValue("@p3", email);
cmd.Parameters.AddWithValue("@p4", add);
cmd.Parameters.AddWithValue("@p5", contact);
cmd.Parameters.AddWithValue("@p6", table);

int affectedRecords = cmd.ExecuteNonQuery();

Error in c# odbcCommand parameterization

3 Answers3

Linked