SQLCommand.CreateParameter.Value = DBNull.Value not returning results

Question

I have code that creates a Null parameter like this

 p = cmd.CreateParameter();
 p.DbType = DbType.Int32;
 p.ParameterName = strName;
 p.Value = DBNull.Value;
 cmd.Parameters.Add(p);

when I insert a record the SQLValue is set to {Null}

And the record is properly created with a Null value for the column.

When I select a record to try and retrieve a record with a Null value setting the parameter using the same approach above..once again the SQLValue for the parameter is {Null}

So same code same set up...but now it does not return the record. I cant retrieve any records when I want to create a parameter with a null value (p.Value = DBNull.Value;) . I get nothing back.

I know its not the query because if I change the parameter to one with a value I get that record back. Is there something I am missing to set the parameter to look for a null value? Everything I have seen has said to just do it that way.

As requested below is the query

Select * from view_full_class_nests 
where parent_interface_class_pk = @parent_interface_class_pk

Also as noted this query works fine if the paramter is set with a value...only fails to retrieve if the value is DBNull.Value

DanD below provided useful link that gave me my answer

Need the query to be Select * from view_full_class_nests where parent_interface_class_pk = @parent_interface_class_pk or @parent_interface_pk Is Null

you should use `ISNULL` check in your Select query.. can you show the Select Query..? — MethodMan, May 02 '16 at 21:49
Added the requested sql query at bottom of original posting. — Bastyon, May 02 '16 at 21:53
for starters you need to stop posting the same question multiple times.. also you should read how to ask a question in regards to posting code.. where in your code do you even show what `@parent_interface_class_pk` is or defined..? — MethodMan, May 02 '16 at 21:56
perhaps you should read up on how to use `Command.Parameters.Add function or AddWithValue function` also create a stored procedure where you can pass in a parameter to the Stored proc vs trying to do what appears to be dynamic sql.. my response would also be `GL` — MethodMan, May 02 '16 at 22:04
I'm thinking maybe you should put that query into a stored procedure and define your parameter in the stored proc as such: '@parent_interface_class_pk int = NULL' — cChacon, May 02 '16 at 22:14

score 2 · Accepted Answer · edited May 23 '17 at 12:31

You can't compare NULL in your where clause:

From https://msdn.microsoft.com/en-us/library/ms172138(v=vs.100).aspx:

Because null is considered to be unknown, two null values compared to each other are not considered to be equal. In expressions using arithmetic operators, if any of the operands is null, the result is null as well.

You will have to use ISNULL operator, see below for a previous answer that may help you:

Null value parameters in where clause in ado.net

SQLCommand.CreateParameter.Value = DBNull.Value not returning results

1 Answers1

Linked