C# MySql Parameterized Query makes longs into null

Question

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.

Thanks guys - putting the command before adding the parameters fixed it. — Patrick, Aug 17 '16 at 15:20

score 1 · Accepted Answer · answered Aug 17 '16 at 14:56

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.

Greg · Answer 2 · 2016-08-17T15:35:50.593

You could alleviate your issue, by simply doing the following:

using(var connection = new MySqlConnection(dbConnection))
{
     connection.Open();
     using(var command = new MySqlCommand(createTableQuery, connection))
     {
          command.ExecuteNonQuery();
     }

     using(var command = new MySqlCommand(insertOrUpdate, connection))
     {
          command.Parameters.Add("..", SqlDbType = SqlDbType.Int).Value = 123456;
          command.ExecuteNonQuery();
     }
}

Keep in mind that ExecuteNonQuery will return a zero or one, if it successfully worked. Also you may want to manually specify the SqlDbType. To avoid SQL inferring incorrectly. Also, this will correctly scope your MySqlCommand, so you can correctly utilize for the queries.

And according to the documentation, it does implement the IDisposable to utilize the using block. This ensures you instantiate your MySqlCommand again.

C# MySql Parameterized Query makes longs into null

2 Answers2