Patrick Patrick - 3 months ago 13
C# Question

C# MySql Parameterized Query makes longs into null

The code is based on https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html

public void TableTest(string connectionString)
{
string sqlToCreateTable = @"CREATE TABLE IF NOT EXISTS my_table
(auction_key BIGINT NOT NULL, auction_owner VARCHAR(25), first_seen BIGINT,
PRIMARY KEY(auction_key))";

string sqlInsertOrUpdateAuction = "INSERT INTO my_table (auction_key) VALUES (@my_auc_id); ";

using (MySqlConnection dbConnection = new MySqlConnection(connectionString))
{
dbConnection.Open();

// is the table in the database?
MySqlCommand cmd = new MySqlCommand(sqlToCreateTable, dbConnection);
cmd.ExecuteNonQuery();

cmd.Parameters.AddWithValue("@my_auc_id", 123456);
cmd = new MySqlCommand(sqlInsertOrUpdateAuction, dbConnection);
cmd.ExecuteNonQuery();

}
}


The error is that 123456 is seen as null.

Message=Column 'auction_key' cannot be null

I tried changing the "strict" setting in my.ini and it makes no difference.

Help please.

Answer

Well, you add the parameter to the command and then instantiate a new command:

cmd.Parameters.AddWithValue("@my_auc_id", 123456);
cmd = new MySqlCommand(sqlInsertOrUpdateAuction, dbConnection);

If you do that, the command will no longer have the value for the @my_auc_id. Try switching those two lines:

cmd = new MySqlCommand(sqlInsertOrUpdateAuction, dbConnection);
cmd.Parameters.AddWithValue("@my_auc_id", 123456);

Hope this helps.