ExecuteScalar call throwing exception "Object reference not set to an instance of an object"

Question

When debugging the the following method in a unit test I get the following error

Object reference not set to an instance of an object

when hitting the following line

result = (int)validateDatabase.ExecuteScalar();

The method is

    public static Boolean Validate(string argument1, string argument2)
    {            
        int result = -1;

        using (var connection = new SqlConnection("connection string"))
        {
            SqlCommand validateDatabase = new SqlCommand("PROCEDURE NAME", connection);
            validateDatabase.CommandType = System.Data.CommandType.StoredProcedure;
            validateDatabase.Parameters.Add("@PARAMETER1", System.Data.SqlDbType.NVarChar).Value = argument1;
            validateDatabase.Parameters.Add("@PARAMETER2", System.Data.SqlDbType.NVarChar).Value = argument2; 

            try
            {
                connection.Open();
                result = (int)validateDatabase.ExecuteScalar();
            }
            catch (SqlException exception) { Trace.WriteLine("exception.Message); }
            finally { connection.Close(); }
        }
        return (int)result == 0 ? true : false; 
    }

Answer 1

ExecuteScalar return null if the result set is null according to MSDN. This means that your cast is invalid

See here for the documentation SqlCommand.ExecuteScalar

If you want that cast to work change it to a nullable int

result = (int?)validateDatabase.ExecuteScalar();

Answer 2

I just experienced a similar problem. What I did was to select an int column from a specific record in a table, but it happened the value of that column is 0. Doing the following caused the exception “Object reference not set to an instance of an object”:

int i = (int)cmd.ExecuteScalar();

My solution was to change the above code to:

int i = 0;
object a = cmd.ExecuteScalar();
if (a != null)
   i = (int)a;

This avoided the exception.