Error in return value of ExecuteScalar()

Question

I am trying to get the result of sql query in an int variable, but I am getting object null reference error. Can someone guide me please.

 oconn = new SqlConnection(oSession.CONNECTION_STRING);
 oconn.Open();

 objCmd.CommandText = "select Rule_Approval_Selection from UserFile where uid=" + intUserID;
 int value = (Int32)(objCmd.ExecuteScalar());
 oconn.Close();

Well, something is null, i guess `objCmd` or `oSession`. The debugger is an awesome tool — Tim Schmelter, Nov 24 '16 at 13:03
This is definitely not a duplicate. Neither `objCmd` nor `oSession` is `null`. — Sergey Kalinichenko, Nov 24 '16 at 13:05
@dasblinkenlight: that's a good one. Your crystal ball told you? — Tim Schmelter, Nov 24 '16 at 13:06
@TimSchmelter Occam's razor told me :-) OP is not new to this, so obvious things, such as `objCmd` or `oconn` being `null`, can be excluded. Missing user IDs, on the other hand, happen right and left, so that's my simplest explanation. — Sergey Kalinichenko, Nov 24 '16 at 13:11
@dasblinkenlight: how do you know that he's not new to this(230 rep)? If he had mentioned explicitly that they are not null the question would be valid. But he has not even mentioned where the exception occurs. What if he now tells that `oSession` and/or `objCmd` are null? — Tim Schmelter, Nov 24 '16 at 13:19
Why don't you use `parameters`, like `objCmd.Parameters.AddWithValue("@uid",intUserID);` — Hackerman, Nov 24 '16 at 13:23
@TimSchmelter 230 is a respectable number for members who do not play SO's game. OP has been a member for almost four years. After a closer look at the history, it appears that he's been accessing SQL DBs with C# for 2+ years. That is why I think he didn't make novice mistake of accessing `null`, and ran into something more challenging. On the other hand, I wouldn't think twice about closing as "what's `null` and how to handle it", had I seen "rep 1 member since today" in the profile. — Sergey Kalinichenko, Nov 24 '16 at 13:34

score 5 · Answer 1 · answered Nov 24 '16 at 13:07

5

ExecuteScalar() returns null when the command has no rows. In your case, when intUserId does not correspond to an existing user, null would be returned.

Switch to int? to handle this issue:

int? value = (Int32?)(objCmd.ExecuteScalar());

Now your variable value would be set to non-null when intUserId exists in the database; otherwise, it would be null.

answered Nov 24 '16 at 13:07

Sergey Kalinichenko

714,442
84
1,110
1,523

[dasblinkenlight](http://stackoverflow.com/users/335858/dasblinkenlight) Youre right because the exception is not in `ExecuteScalar()` function.. In this [MSDN article](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx#Exceptions) there is a list of the `ExecuteScalar` exceptions. – Yahfoufi Nov 24 '16 at 13:13
Thought it caused an `InvalidCastException`, but because of unboxing value types you get a `NullRefernceException`. It's mentioned [here](https://msdn.microsoft.com/en-us/library/yz2be5wk.aspx): _"Attempting to unbox `null` causes a `NullReferenceException`. Attempting to unbox a reference to an incompatible value type causes an `InvalidCastException`."_ – Tim Schmelter Nov 24 '16 at 13:17

score 0 · Answer 2 · answered Nov 24 '16 at 13:13

oconn = new SqlConnection(oSession.CONNECTION_STRING);
oconn.Open();
objCmd.CommandText = "select Rule_Approval_Selection from UserFile where uid="     + intUserID;
var x=objCmd.ExecuteScallar();
if (x!= null && DBNull.Value != x) 
{ 
int value = (Int32)(objCmd.ExecuteScalar());
}

oconn.Close();

try this...

Error in return value of ExecuteScalar()

2 Answers2