Why is ExecuteScalar returning NullReferenceException error

Question

I have the following function which checks to see if a user(strU) exist in the table for a column, if so return 1, otherwise return 0:

public int AddDataScalar(string strU)
{
    string strQueryExistence = "SELECT 1 FROM [OB].[h].[OP_PEONS] WHERE Executive= '" + strU + "'";
    int inNum;
    using (SqlConnection con = new SqlConnection(strConn))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand(strQueryExistence, con);
        object value = cmd.ExecuteScalar().ToString();
        if (value != null)
        {
            inNum = 1;
        }
        else
        {
            inNum = 0;
        }
        con.Close();
    }
    return inNum;
}

It is failing in this line: object value = cmd.ExecuteScalar().ToString();

With the following error: Object reference not set to an instance of an object.

How do I resolve it?

http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it — Patrick Hofman, Oct 01 '14 at 14:34

Steve · Accepted Answer · 2014-10-01T14:45:07.837

3

ExecuteScalar returns null if your WHERE condition doesn't produce any result.
If you apply any kind of conversion when ExecuteScalar returns null you are in trouble.

Said that, I really suggest some changes to your query

public int AddDataScalar(string strU)
{
    string strQueryExistence = @"IF EXISTS(SELECT 1 FROM [OB].[h].[OP_PEONS] 
                                 WHERE Executive= @stru) SELECT 1 ELSE SELECT 0";
    int inNum = 0;
    using (SqlConnection con = new SqlConnection(strConn))
    using ( SqlCommand cmd = new SqlCommand(strQueryExistence, con))
    {
        con.Open();
        cmd.Parameters.AddWithValue("@stru", strU);
        inNum = Convert.ToInt32(cmd.ExecuteScalar());
    }
    return inNum;
}

The first thing is the IF EXISTS t-sql functions that is the fastest way to discover if a specific record exists or not in the table. The second point is the usage of a parameterized query to avoid parsing problems and Sql Injection scenarios.

The IF EXISTS statement allows to be sure that the ExecuteScalar doesn't return null because in that case the ELSE part will return zero

edited Oct 01 '14 at 14:45

answered Oct 01 '14 at 14:38

Steve

213,761
22
232
286

Thank you. I edited your code for the missing `)` – SearchForKnowledge Oct 01 '14 at 14:42
1

Well, in this case there is no need of the ternary operator, so I have removed it. – Steve Oct 01 '14 at 14:43
So the function will return 0 if user doesn't exist in the `Executive` column otherwise will return 1? – SearchForKnowledge Oct 01 '14 at 15:33
Yes, simple as that. IF EXIST simply ignore the first (SELECT 1... ) part and use the table name and the where condition as a boolean expression. If the result is true the first select outside the parenthesys is executed else the second one. – Steve Oct 01 '14 at 17:07
Ok Great Thank you. I realized why the parameter wasn't working for me. I forgot to change it to `strU` :) – SearchForKnowledge Oct 01 '14 at 17:21

Dmitry Bychenko · Answer 2 · 2018-04-13T12:18:58.010

whenever cmd.ExecuteScalar() returns null then null.ToString() throws the exception. In your case, just use teкnary operator:

inNum = cmd.ExecuteScalar() == null ? 0 : 1;

The implementation could be

public int AddDataScalar(string strU) {
  using (SqlConnection con = new SqlConnection(strConn)) {
    con.Open();

    // Make your SQL readable: use @"" strings
    strQueryExistence = 
      @"SELECT 1
          FROM [OB].[h].[OP_PEONS]
         WHERE Executive = @prm_Executive";

    // using is a better practice 
    using (SqlCommand cmd = new SqlCommand(strQueryExistence, con)) {
      // parameters are better than hardcoding
      cmd.Parameters.AddWithValue("@prm_Executive", strU); 

      return cmd.ExecuteScalar() == null ? 0 : 1; 
    }
  }
}

I want to return 0 if the user does not exist in the `Executive` column otherwise return 1 if the user exist. — SearchForKnowledge, Oct 01 '14 at 15:01

score 2 · Answer 3 · answered Oct 01 '14 at 14:32

According to MSDN (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx):

The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty.

You're calling .ToString() (why?) on a null object, since there are no results being returned. Skip the .ToString() call and it should work fine.

score 1 · Answer 4 · answered Oct 01 '14 at 14:32

1

If object returned by cmd.ExecuteScalar() is null, you will get this exception. Use:

string value = System.Convert.ToString(cmd.ExecuteScalar());

if you need the result converted to string.

answered Oct 01 '14 at 14:32

Igor

15,833
1
27
32

Why is ExecuteScalar returning NullReferenceException error

4 Answers4

Linked