CODEWITHSUNDEEP
c#database
beaumondo
beaumondo

Reputation: 4930

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

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

Upvotes: 14

Views: 20047

Answers (5)

Cihan TT
Cihan TT

Reputation: 1

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

Upvotes: -1

friedein
friedein

Reputation: 51

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.

Upvotes: 4

LaBird
LaBird

Reputation: 299

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.

Upvotes: 6

user2604650
user2604650

Reputation:

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

Upvotes: 0

David Pilkington
David Pilkington

Reputation: 13620

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

Upvotes: 15

Related Questions