CODEWITHSUNDEEP
c#sql-serversql-server-2008
Bastyon
Bastyon

Reputation: 1611

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

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}

enter image description here

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}

enter image description here

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

Upvotes: 0

Views: 936

Answers (1)

Dan D
Dan D

Reputation: 2523

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

Upvotes: 2

Related Questions