ODP.NET delete statement can not pass DBNull as parameter value

Question

Using: .NET 4, ODP.NET 11.2.0.3.0, Oracle Database 10g Release 10.2.0.3.0

I am going crazy in trying to determine why my delete statement doesn't work. Perhaps someone here can help me.

Here is the code that DOES work:

cmd = New OracleCommand("delete from u_parameters where pkey = :pkey and user_id = :user_id and computer is null", Con)
cmd.Parameters.Add("pkey", OracleDbType.NVarchar2).Value = "Test"
cmd.Parameters.Add("user_id", OracleDbType.Decimal).Value = 1
cmd.ExecuteNonQuery()  ' -- this returns 1 as it should

Here is the code that DOESN'T work:

cmd = New OracleCommand("delete from u_parameters where pkey = :pkey and user_id = :user_id and computer = :computer", Con)
cmd.Parameters.Add("pkey", OracleDbType.NVarchar2).Value = "Test"
cmd.Parameters.Add("user_id", OracleDbType.Decimal).Value = 1
cmd.Parameters.Add("computer", OracleDbType.NVarchar2).Value = DBNull.Value
cmd.ExecuteNonQuery()  ' -- this returns 0!!

By doesn't work I mean that the statement executes but nothing happens in the database (and the result of ExecuteNonQuery is 0 which means no rows where affected). I really don't understand what could be the problem here. I've tried setting the 'computer' parameter IsNullable to True but it doesn't not change anything.

Please help.

try `delete from u_parameters where pkey = :pkey and user_id = :user_id and (:computer is null or computer = :computer)` — Damith, Sep 02 '13 at 08:59
Actually it should be `delete from u_parameters where pkey = :pkey and user_id = :user_id and ((:computer is null and computer is null) or computer = :computer)` — Dalibor Čarapić, Sep 02 '13 at 10:41

score 2 · Accepted Answer · edited May 23 '17 at 11:57

2

AND x = NULL doesn't equate to true when x is null.

Try using IS NULL or the isnull() function.

See Not equal <> != operator on NULL for more info

edited May 23 '17 at 11:57

Community

1
1

answered Sep 02 '13 at 08:47

Ryan

3,924
6
46
69

I already knew that but I expected that parameterized queries avoid this somehow. Thank you for the answer. – Dalibor Čarapić Sep 02 '13 at 10:42

score 0 · Answer 2 · answered Sep 02 '13 at 08:49

You can do the following for any nullable parameters.

  oleDBCmd.Parameters.Add(new OracleParameter("computer", OracleType.NVarChar));`

if(string.IsNullOrEmpty(toStr)) {
    oleDBCmd.Parameters["computer"].Value = DBNull.Value;
} else {
    oleDBCmd.Parameters["computer"].Value = toStr;
}

`

ODP.NET delete statement can not pass DBNull as parameter value

2 Answers2