Handling ExecuteScalar() when no results are returned

According to MSDN documentation for DbCommand.ExecuteScalar:

If the first column of the first row in the result set is not found, a null reference (Nothing in Visual Basic) is returned. If the value in the database is null, the query returns DBNull.Value.

Consider the following snippet:

using (var conn = new OracleConnection(...)) {    conn.Open();    var command = conn.CreateCommand();    command.CommandText = "select username from usermst where userid=2";    string getusername = (string)command.ExecuteScalar();}

At run-time (tested under ODP.NET but should be the same under any ADO.NET provider), it behaves like this:

• If the row does not exist, the result of command.ExecuteScalar() is null, which is then casted to a null string and assigned to getusername.
• If the row exists, but has NULL in username (is this even possible in your DB?), the result of command.ExecuteScalar() is DBNull.Value, resulting in an InvalidCastException.

In any case, the NullReferenceException should not be possible, so your problem probably lies elsewhere.

First you should ensure that your command object is not null. Then you should set the CommandText property of the command to your sql query. Finally you should store the return value in an object variable and check if it is null before using it:

command = new OracleCommand(connection)command.CommandText = sqlobject userNameObj = command.ExecuteScalar()if (userNameObj != null)  string getUserName = userNameObj.ToString() ...

I'm not sure about the VB syntax but you get the idea.

I just used this:

    int? ReadTerminalID()    {        int? terminalID = null;        using (FbConnection conn = connManager.CreateFbConnection())        {            conn.Open();            FbCommand fbCommand = conn.CreateCommand();            fbCommand.CommandText = "SPSYNCGETIDTERMINAL";            fbCommand.CommandType = CommandType.StoredProcedure;            object result = fbCommand.ExecuteScalar(); // ExecuteScalar fails on null            if (result.GetType() != typeof(DBNull))            {                terminalID = (int?)result;            }        }        return terminalID;    }