c# create a sql query with null values

Question

I do this in my sql managment studio

SELECT * FROM SMSMessage WHERE respondCode IS NULL

and I got results

I want to do that query from C#

I tried this:

string query = "SELECT * FROM SMSMessage WHERE respondCode IS @respondCode";
SqlConnection con = new SqlConnection(Utilities.getConnectionString());
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@respondCode", DBNull.Value);

I got this exxception :

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near '@respondCode'.

why please?

I also tried

string query = "SELECT * FROM SMSMessage WHERE respondCode IS NULL";

and I got empty results.

Uupdate

string query = @"SELECT *
    FROM SMSMessage
    WHERE (respondCode = @respondCode)
       OR (@respondCode IS NULL AND respondCode IS NULL)";
            SqlConnection con = new SqlConnection(Utilities.getConnectionString());
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@respondCode", DBNull.Value);
            con.Open();
            using (con)
            {
                DataTable results = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(results);

"I also tried string query = "SELECT * FROM SMSMessage WHERE respondCode IS NULL";" and got empty results. Sounds like you are connecting to a different instance/database than the one you are connecting to in SSMS then. — Martin Smith
– Martin Smith, Commented Jun 27, 2015 at 11:47
Then, how did you concluded that you are getting empty result set? — Dusan
– Dusan, Commented Jun 27, 2015 at 11:49
Also, might be the debugger behavior. Did you try to execute 2 lines past sda.Fill(results); and see if you have positive row count? — Dusan
– Dusan, Commented Jun 27, 2015 at 11:55

Dusan · Accepted Answer · 2015-06-27 11:43:12Z

3

Since the NULL values cannot be compared, the NULL = NULL evaluates to unknown, your query needs to be:

SELECT * FROM SMSMessage 
WHERE (respondCode = @respondCode) OR
      (respondCode IS NULL AND @respondCode IS NULL)

edited Jun 27, 2015 at 11:43

answered Jun 27, 2015 at 11:40

Dusan

5,1546 gold badges44 silver badges61 bronze badges

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

6 Comments

Marco Dinatsoli Over a year ago

help pleaes, i am getting empty results, though there are results in the datagase

Dusan Over a year ago

Are you sure you are pointing to the same database? Check your connection string.

Marco Dinatsoli Over a year ago

yes the same database, i am using datatabel to fill the data and the number of rows = 0

Marco Dinatsoli Over a year ago

I will try to change the query to check if i get results and update you

Marco Dinatsoli Over a year ago

i accepted your answer, thanks , i updated my questions, with the code i am using, could you check please why i am geetting empty result? i am using the same database really, i just have one sql instance

|

Martin Smith · Accepted Answer · 2015-06-27 11:41:56Z

0

One way of writing this query to behave as you desire would be

SELECT *
FROM   SMSMessage
WHERE  EXISTS (SELECT respondCode
               INTERSECT
               SELECT @respondCode)

answered Jun 27, 2015 at 11:41

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Collectives™ on Stack Overflow

c# create a sql query with null values

Uupdate

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Uupdate

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related