SQL query for column (with values contained) registering as null

Question

Query returns what I expected it to return now with is null instead of != null, but I am still stuck as to why I am getting the exception in my c# application which is my main problem, Thanks

I have a column called UserId in a table WT_Users when I run the query

'select UserId from WT_Users;'

It returns a list of usersIds 1,2,3,4 which is fine but when I run the query

'select UserId from WT_Users where UserId != null'

No values are returned even thought I know that there are values held within the column. I ran these queries because I was originally working on a c# application and when I ran a reader with the first query above i got an exception 'Object reference not set to an instance of an object' when i tried to pull the value from the reader, even thought I have a 'HasRows' check and 'while read' check that were satisfied

    SqlCommand findContactID = new SqlCommand("select UserId from WT_Users",       con);
        con.Open();
        SqlDataReader dr7 = findContactID.ExecuteReader();
        if (dr7.HasRows)
        {
            while (dr7.Read())
            {
                userIDs.Add(Convert.ToString(dr7.GetInt32(0)));
            }
        }

In visual studio I can even see that the reader does contain the values I what under Results view.

I have spent too much time already trying to figure this out and its probably something obvious. Thanks

Possible duplicate of [SQL is null and = null](http://stackoverflow.com/questions/9581745/sql-is-null-and-null) — sstan, Oct 01 '15 at 13:39
Sorry ya the query works fine now thanks but I am still getting the exception in the c# application — joe127, Oct 01 '15 at 13:41
It would help if you posted the error + stack trace. For all we know, it could be `userIDs` that's `null`, which would have nothing to do with your reader. — sstan, Oct 01 '15 at 13:43

score 1 · Accepted Answer · answered Oct 01 '15 at 13:37

1

You should never use comparison operations with null. Use is null or is not null.

Whenever you use a comparison operator with null, it resolves to UNKNOWN, which is neither True nor False.

select UserId from WT_Users where UserId is not null

answered Oct 01 '15 at 13:37

Vamsi Prabhala

48,685
4
36
58

score 0 · Answer 2 · 2015-10-01T13:47:59.033

Try this:

private void Test2(SqlConnection con)
{
    var userIDs = new List<String>();
    using (var cmd = new SqlCommand("select UserId from WT_Users", con))
    {
        con.Open();
        using (var r = cmd.ExecuteReader())
        {
            while (r.Read())
            {
                var o = r[0];
                if ((o != null) && (o != DBNull.Value))
                {
                    userIDs.Add(o.ToString());
                }
            }
        }
    }
}

SQL query for column (with values contained) registering as null

2 Answers2