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] ?

I believe you can only check if a column is null by using `COL_3 IS NULL` — ProgrammerAdept, Oct 08 '18 at 14:02
When i use **COL_3 IS @COL_3** in my command-text, i get the error "invalid use of the is operator". Does this mean i can not use the parameters when i try to check if a column is null? I can not believe this... — Olli, Oct 08 '18 at 14:08
You didn't specified is this COL_3 is always be checked for null or not. If so just use this query `UPDATE MY_TABLE SET COL_1 = @COL_1 WHERE COL_2 = @COL_2 AND COL_3 IS NULL` and do not add COL_3 parameter. — Renatas M., Oct 08 '18 at 14:16
This works if the value is NULL, but what if COL_3 can also have a value? Do i really have to change the commandtext depending on the parameters? — Olli, Oct 08 '18 at 14:27

score 1 · Accepted Answer · answered Oct 09 '18 at 13:28

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

score 0 · Answer 2 · answered Oct 08 '18 at 15:06

Just setting the parametervalue to DBNull doesn't work. I had to change the query in my commandtext object when i want to check if a column has a null-value like @Reniuz mentioned in his comment:

UPDATE MY_TABLE SET COL_1 = @COL_1 WHERE COL_2 = @COL_2 AND COL_3 IS NULL

access sql-parameter with dbnull

2 Answers2