ExecuteScalar throws NullReferenceException when calling a stored proc that returns 1

Question

I've done some research before posting this question and I'm aware of the fact that when there's no data returned, ExecuteScalar will throw a System.NullReferenceException. That is why I modified my stored proc to "return 1" so there's guaranteed a return value. However, I'm still getting the NULL reference exception.

So I tried to use the SqlCommand to query a table that has data:

        SqlCommand sqlCommand = new SqlCommand("SELECT * FROM ATableThatHasValues", conn)

When I ran execute scalar I was able to pick up a value so I know I have permission to query the database. I'm suspecting that this is some specific storeed proc permission setting that I missed?

I'd really appreciate any comment/suggestions as I've been stuck on this for a day now. :(

My code looks like this:

        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            sqlConnection.Open();
            using (SqlCommand sqlCommand = new SqlCommand("GetSomeValue", sqlConnection))
            {
                sqlCommand.CommandType = CommandType.StoredProcedure;
                //sqlCommand.Parameters.Add(new SqlParameter("@Id", this.ID));
                //sqlCommand.Parameters.Add(new SqlParameter("@State", 1 /* active */));

                byte retValue = (byte)sqlCommand.ExecuteScalar();
                return retValue;
            }
        }

THANKS!

You do understand the purpose of `ExecuteScalar`, right? It returns the first column of the first row of data, everything else is ignored. Is there a reason that you're using it along with `SELECT *`? — Chris Haas, Jan 18 '11 at 19:12

score 3 · Answer 1 · answered Jan 18 '11 at 19:17

I'm just going to elaborate on what @gbn said. When you execute SQL code you can return information in three different ways, OUTPUT parameters, tabular data and/or a single RETURN value. Like @gbn said, RETURN values are essentially specialized OUTPUT parameters. ExecuteScalar only sees information from tabular data, namely the first column of the first row. If no tabular data is received when you call ExecuteScalar a null value is returned instead. If you try to do something with this null value then obviously you'll get a NRE.

gbn · Accepted Answer · 2011-01-18T19:06:06.653

2

Random guess

You are using RETURN so there is no dataset to read column 1, row 1 for ExecuteScalar

Use SELECT or OUTPUT parameters

Edit: Actually, not so random

RETURN 1 is not a result set: it's a "special" parameter

sqlCmd.Parameters.Add(New SqlParameter("RETURN_VALUE", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

edited Jan 18 '11 at 19:06

answered Jan 18 '11 at 19:00

gbn

422,506
82
585
676

Thanks! That worked. My original stored proc actually returns a resultset but for debugging purposes I changed it thinking it would help me debug but it actually derailed me entirely. I was so frustrated that I missed a obvious small typo in my original stored proc that started the whole debugging process. :( Anyway, thanks again! – Bryan Jan 18 '11 at 19:30

ExecuteScalar throws NullReferenceException when calling a stored proc that returns 1

2 Answers2