Marco Dinatsoli Marco Dinatsoli - 1 year ago 81
SQL Question

c# string query put null for sql server parameter

I know this question was asked many times, but i am not able to find a solution for it

This is my code

string query = @"SELECT *
WHERE (respondCode IS @respondCode)
and (sentOn > '08/26/2016')
//string query = "select * from SMSMessage";
SqlConnection con = new SqlConnection(Utilities.getConnectionString());
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@respondCode", DBNull.Value);

I want the responseCode to be null,

I am getting error:

syntax error near @responseCode

when I do this
responseCode is NULL
, there is no syntax error, but the query for some reaonse doesn't bring any result

Help please

Answer Source

I guess you want this

Where (respondCode = @respondCode or @respondCode is null)
  and sentOn > '08/26/2016'

When a value is passed to @respondCode parameter the records will be filter based on @respondCode and sentOn > '08/26/2016'.

When nothing is passed to @respondCode parameter (ie) NULL, then records will be filtered only based on sentOn > '08/26/2016'

As mentioned in comments by Steve, If you need records only when respondCode is NULL then no need of that variable just hardcode the NULL condition in Where clause

Where respondCode is null
  and sentOn > '08/26/2016'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download