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; 
    }

You should have var connection = new SqlConnection(hereComesTheConnectionString) — Michael Mairegger
– Michael Mairegger, Commented Nov 21, 2013 at 10:07
It already is, I made the mistake of using a varialbe name as a place holder name, Ive altered this now — beaumondo
– beaumondo, Commented Nov 21, 2013 at 10:49
having same issue here , after couple of hours testing possibilities thats the only solution works for me : result = validateDatabase.ExecuteScalar() != null ? int.Parse(validateDatabase.ExecuteScalar().ToString()) : 0; — The Doctor
– The Doctor, Commented Jun 10, 2022 at 17:43

David Pilkington · Accepted Answer · 2013-11-21 10:08:06Z

15

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();

answered Nov 21, 2013 at 10:08

David Pilkington

13.6k3 gold badges44 silver badges75 bronze badges

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

3 Comments

beaumondo Over a year ago

Thanks David good shout. If I run my command in the dbms it returns 0 or -1 (representing true or false), but in my code the ExecuteScaler is returning null. Do you know why c# would interpret this as null

David Pilkington Over a year ago

@beaumondo according to the documentation it returns

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.

So I am assuming your result set is empty.

beaumondo Over a year ago

Nah still not working. In the db query window the result set is never null, always -1 or 0. Like an aggregate function

LaBird · Accepted Answer · 2014-05-27 09:06:48Z

6

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.

answered May 27, 2014 at 9:06

LaBird

2993 silver badges14 bronze badges

1 Comment

NealWalters Over a year ago

In my case, I was passing a bad value in the where clause parm, causing no rows to be returned. Since ExecuteScalar returns the first column of the first row, then it must return NULL when there are no rows.

friedein · Accepted Answer · 2019-03-13 14:46:22Z

4

ExecuteScalar: Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Try adding "SELECT CAST(scope_identity() AS int)" at the end of your query.

answered Mar 13, 2019 at 14:46

friedein

513 bronze badges

Comments

user2604650 · Accepted Answer · 2013-11-21 10:08:35Z

0

You are doing it rather wrong - try calling SqlDataReader for validateDatabase.ExecuteScalar() results

answered Nov 21, 2013 at 10:08

user2604650

1 Comment

beaumondo Over a year ago

The stored procedure only return a single value either -1 or 0. Thats why i wanted to use the ExecuteScalar method

Cihan TT · Accepted Answer · 2022-01-17 19:20:35Z

-1

result = validateDatabase.ExecuteScalar();
return result == null ? -1 : (int)result;

edited Jan 17, 2022 at 19:20

answered Apr 28, 2016 at 11:23

Cihan TT

12 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

3 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related