Inserting "NULL" or "NOT NULL" into ASP.NET parameterized queries

Question

I have an asp DropDownList that is used to filter database results. One of my filters is used to check if a column is NULL or NOT NULL:

<asp:DropDownList runat="server" id="activeFilter" CssClass="form-control"  AutoPostBack="false" >
  <asp:ListItem selected="True" value="">-- Filter By Active/Inactive </asp:ListItem>
  <asp:ListItem value="NOT NULL">Active</asp:ListItem>
  <asp:ListItem value="NULL">Inactive</asp:ListItem>
</asp:DropDownList>

I then try to insert it into a parameterized query like so:

//called from another method

string query = ".... AND Status=@statusFilter ...."
SqlConnection conn = new SqlConnection(connString)
SqlCommand command = new SqlCommand(query, conn)
command.Parameters.AddWithValue("@statusFilter", statusFilter.SelectedValue);

//other stuff, make sql call

This process works for other filter values, something like

"... AND Name=@nameFilter ..."

Where nameFilter might be "John", "Tim", "Amy", etc. so I'm assuming this has something to do with the fact that NULL and NOT NULL shouldn't actually be strings. Any easy solution for this? I see I can add DBNull.Value but what about NOT NULL?

Any help would be much appreciated.

EDIT: Sorry everyone, I made a mistake in my first post - I copied the wrong query string. My query string is actually

"... AND Date_Completed IS @activeFilter ..."

I know I cant say Column=Null, so again I apologize. I'm going to run through the suggestions right now and update everyone.

EDIT 2: Okay so the issue is that you cannot treat NULL / NOT NULL as parameters. I was hoping I could use it as a parameter as I had a number of utility functions to generate the final query based on a many different filters. Ultimately, this did not work, and I needed to alter my methods and insert the entire " IS NULL " or "IS NOT NULL" directly using an If/Else block.

You may be passing a NULL value but the query doesn't work. NULL in SQL means an Unknown value. The result of comparing Unknown to Unknown is itself Unknown. That's why Name=@nameFilter will always fail if @nameFilter contains a NULL — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 27, 2019 at 13:22
are you talking about DBNull? learn.microsoft.com/en-us/dotnet/api/… and anyway what kind of value is NOT NULL ? perhaps a bool column would be more useful in this case — Ted
– Ted, Commented Jun 27, 2019 at 13:23
You need two queries here, and an if statement to choose the right one, you can't use a parameter. Also, you should not be showing words like null to users, that's confusing UI. — DavidG
– DavidG, Commented Jun 27, 2019 at 13:25
Also relevant: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… — DavidG
– DavidG, Commented Jun 27, 2019 at 13:25
IS NULL and IS NOT NULL are expressions, not values. If you want to check whether Name is null you'll have to write NAME IS NULL. If you want to see whether the name matches the parameter or is null itself you'll have to write WHERE NAME IS NULL OR NAME = @nameFilter — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 27, 2019 at 13:25

Bruno Tenreiro · Accepted Answer · 2019-06-27 13:31:01Z

1

This won't work like everyone says in the comments. You can do an If to see the value selected and in the sql query you can use IS NULL or IS NOT NULL. Something like below:

if (statusFilter.SelectedValue == "NULL"){
   string query = ".... AND Status IS NULL...."
}
else{
   string query = ".... AND Status IS NOT NULL...."
}

answered Jun 27, 2019 at 13:31

Bruno Tenreiro

962 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zohar Peled · Accepted Answer · 2019-06-27 13:28:57Z

0

In SQL, You can't do = null nor can you parameterize is null or is not null.

This doesn't mean, however, that it can't be solved - something like this will do the trick:

...AND (Status = @Status OR 
    (Status IS NULL OR @Active = 'NULL') OR 
    (Status IS NOT NULL AND @Active = 'NOT NULL') 
)...

answered Jun 27, 2019 at 13:28

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Collectives™ on Stack Overflow

Inserting "NULL" or "NOT NULL" into ASP.NET parameterized queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related