Calling stored procedure creates null reference exception

Question

I have a simple code which calls a stored procedure:

bool connOpened = OpenConnectiontoDB();
SqlCommand cmd = new SqlCommand("spRegisterUser", cn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter userName = new SqlParameter("@UserName", username);
SqlParameter pass = new SqlParameter("@Password", password);
SqlParameter user_email = new SqlParameter("@Email", email);

cmd.Parameters.Add(userName);
cmd.Parameters.Add(pass);
cmd.Parameters.Add(user_email);

int returnCode=0;

try
{
    returnCode = (int)cmd.ExecuteScalar();
}
catch (Exception e)
{
    int i = 0;
}

return returnCode;

An exception is thrown at :

returnCode = (int)cmd.ExecuteScalar();

Message: "Object reference not set to an instance of an object."

I have no idea why, I checked:

cn.status it shows as open, I can run the stored procedure directly in the DB. And cmd appears to be populated so what is this error?

why you say the query returns null? It does not. It runs a stored procedure which returns an integer. And I tested the sp I only get -1 or 1.

The proposed duplicate answer does not answer this question. It only shows how to handle the error when object is null. My question was what is the cause of null.

What is your `cmd` returns as a first column or the first row exactly? Where do you define your `cn`? — Soner Gönül, Apr 06 '15 at 11:52
What is `connOpened ` and `cn` ? and is connection open before executing query ? — yogi970, Apr 06 '15 at 11:54
You [can't cast null to int](http://stackoverflow.com/questions/361258/why-does-casting-a-null-to-a-primitiveie-int-in-net-2-0-throw-a-null-ref-exc). Your query returns a null result. See [duplicate](http://stackoverflow.com/questions/559632/executescalar-throws-nullreferenceexception) on how to check for that. If your query shouldn't return a null result, check your query. — CodeCaster, Apr 06 '15 at 11:55
why you say my query returns null? It does not. I runs a stored procedure which returns an integer. And I tested the sp. — S Nash, Apr 06 '15 at 11:58
This is marked as duplicate? so where is the answer for this? — S Nash, Apr 06 '15 at 11:59
The proposed duplicate answer does not apply in this case. Again the SP that I run does return an integer and i tested it more than one times. — S Nash, Apr 06 '15 at 12:00
Reviewed the so called duplicate answer , it is not an answer in this case at all. It just shows how to catch the Exception. It does not explain cause of the null exception which is my question. Again each time I run the SP I get 1 or -1. — S Nash, Apr 06 '15 at 12:03
can you inspect the `cmd` variable right before you call it? The error implies it isn't instantiated properly. We can't see any code setting up `cn` - are you certain you're doing that properly? Also does your SP `RETURN` a value or `SELECT` a value? perhaps you should post the SP. Looking at this page: ExecuteScalar will return null reference if resultset is empty, and can returns a maximum of 2033 characters.; if you are using `RETURN` instead of `SELECT then `ExecuteScalar` will indeed return a NULL object. — Nick.Mc, Apr 06 '15 at 12:23
No connection open , no connection close, no objects dispose....!First post full code or write complete code then test it. — yogi970, Apr 06 '15 at 12:35
try change **returnCode = (int)cmd.ExecuteScalar();** to **Console.WriteLine(cmd.ExecuteScalar().GetType().ToString());** and see what write to console. Or setup brackpoint and see **cmd.ExecuteScalar()** type. — Дмитрий Чистик, Apr 06 '15 at 14:15

Calling stored procedure creates null reference exception

0 Answers0