c# string query put null for sql server parameter

Question

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 *
    FROM SMSMessage
    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

If the NULL is fixed then you don't need a parameter. Do you want to find all records from SMSMessage where RespondCode IS null and sent after that date? — Steve
– Steve, Commented Aug 27, 2016 at 13:21
Then why not WHERE (respondCode IS NULL) AND .... Pay attention to the string format for your Date constant also because the correct interpretation of that constant depends on the sql server localization — Steve
– Steve, Commented Aug 27, 2016 at 14:07
@Steve that is the thing, you got me, please look at my comment to the answer below, the code is no working, it gives the same results always. do you mean the and should be capital letter? — Marco Dinatsoli
– Marco Dinatsoli, Commented Aug 27, 2016 at 14:13

Steve · Accepted Answer · 2016-08-27 14:41:30Z

2

I would use directly IS NULL and not passing any parameter, but the most important change is how do you apply the date constant in your query statement.
Assuming you use the Italian locale in your sql server database I would use

string query = @"SELECT * SMSMessage
                 WHERE respondCode  IS NULL
                 AND (sentOn > CONVERT(DateTime, '26/08/2016', 105))

T-SQL Convert docs

On the contrary I would look carefully to the value passed for the sentOn condition. If this value changes dynamically it is better to use a parameter for this value. In this way the query optimizer of sql server will be able to build a better (faster) execution plan

string query = @"SELECT * SMSMessage
                 WHERE respondCode  IS NULL
                 AND sentOn > @dateLimit";

SqlConnection con = new SqlConnection(Utilities.getConnectionString());
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add("@dateLimit", SqlDbType.DateTime).Value = new DateTime(2016, 8, 26);

edited Aug 27, 2016 at 14:41

answered Aug 27, 2016 at 14:19

Steve

217k22 gold badges242 silver badges296 bronze badges

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

10 Comments

Marco Dinatsoli Over a year ago

what does 105 mean please ?

Marco Dinatsoli Over a year ago

i understood from the first answer that i can't pass "null" as it is in this case because i have two conditions, are you sure that it is correct the way you passed null please ?

Steve Over a year ago

I am not passing NULL, there is no parameter in the query above.

Marco Dinatsoli Over a year ago

yes there is respondCode parameter in my code, that i want to pass null for it, in your query you passed null as string, and in the first query the guy said that if i have two conditions, i can't pass null as string. you got me please ?

Steve Over a year ago

The point is: if you search always for records that have NULL in the respondCode then there is no need of a parameter there. Just write the IS NULL in the query and that's all.

|

Pரதீப் · Accepted Answer · 2016-08-27 13:24:56Z

1

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'

edited Aug 27, 2016 at 13:24

answered Aug 27, 2016 at 13:14

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

8 Comments

Marco Dinatsoli Over a year ago

It is not working, and it is not throwing an error, it allows gives the same results, even though the results are not correct

Pரதீப் Over a year ago

@MarcoDinatsoli Can you add some sample data and expected result

Marco Dinatsoli Over a year ago

when i check the count of the returning rows from that query it is always the same, though i am changing the responseCode to a value not null

Pரதீப் Over a year ago

@MarcoDinatsoli - can you post the query you are using

Marco Dinatsoli Over a year ago

i posted it it is in the question

|

Collectives™ on Stack Overflow

c# string query put null for sql server parameter

2 Answers 2

10 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related