C# MySqlCommand parameter with value of DBNull.Value not working

Question

Why is the value of DBNull.Value not working in the following code? I keep getting false as return value, while i am sure there is a record that matches.

(machineNumber has indeed the value of "b", so that is not the issue)

MySqlCommand cmd = new MySqlCommand("SELECT * FROM `sparter` WHERE `id` = @machineNumber AND `account_id` = @null", conn);

            cmd.Parameters.AddWithValue("@machineNumber", machineNumber);
            cmd.Parameters.AddWithValue("@null", DBNull.Value);

            using (var reader = cmd.ExecuteReader())
            {
                return reader.read();
            }

While this query IS working in MySqlWorkbench:

SELECT * FROM `sparter` WHERE `id` = "b" AND `account_id` IS NULL

In MySQL, dont use = or != with null. They don't work. Use IS NULL or IS NOT NULL. See https://stackoverflow.com/questions/9608639/mysql-comparison-with-null-value — Mark Willis, Sep 11 '18 at 12:21
@Mark you are correct. my mindset was wrong. i wanted to use the dbnull.value, while i didnt needed to haha — Svenmarim, Sep 11 '18 at 12:48

Antoine V · Accepted Answer · 2018-09-11T12:26:47.517

2

In fact, in MySQL use = or != null don't work. Use IS NULL or IS NOT NULL.

Can you try with this:

MySqlCommand cmd = new MySqlCommand("SELECT * 
                                     FROM `sparter`
                                     WHERE `id` = @machineNumber AND `account_id` is @null", conn);

edited Sep 11 '18 at 12:26

answered Sep 11 '18 at 11:59

Antoine V

6,998
2
11
34

@Svenmarim Glad to help you :) – Antoine V Sep 11 '18 at 12:10
You might want to add WHY/HOW this works, as mentioned in the comment by @Mark . – AsheraH Sep 11 '18 at 12:24

C# MySqlCommand parameter with value of DBNull.Value not working

1 Answers1