access sql-parameter with dbnull

Question

I want to update some rows in my accessdatabase. When typing the whole query in the commandtext-object, everything works just fine. But when i try to use the parameters the DBNull-value doesn't get recognized:

Here is how i do this:

using (var connection = new OleDbConnection(CONNECTION_STRING_ACCESS))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
          //this works
          //command.CommandText = "UPDATE MY_TABLE SET COL_1 = '10' WHERE COL_2 = '78' AND COL_3 IS NULL";

          //this doesn't work...
          command.CommandText = "UPDATE MY_TABLE SET COL_1 = @COL_1 WHERE COL_2 = @COL_2 AND COL_3 = @COL_3
          command.Parameters.AddWithValue("@COL_1", 20);
          command.Parameters.AddWithValue("@COL_2", 78);
          command.Parameters.AddWithValue("@COL_3", DBNull.Value);

          var rows_updated = command.ExecuteNonQuery();

          Console.WriteLine(rows_updated);
     }
}

How do i have to handle the DBNull-parameter to get the rows updated, which have COL_2 = 78 and COL_3 = [Null] ?

Answer 1

NULL is undefined, so nothing can be equal to NULL, you have to use the IS NULL syntax.

You need an IF/ELSE block to build the SQL based on having a value for COL_3. Build the SQL with IS NULL if no value, or build the SQL and bind the variable (your existing code) if present.

See SQL is null and = null