Handling ExecuteScalar() when no results are returned

Question

I am using the following SQL query and the ExecuteScalar() method to fetch data from an Oracle database:

sql = "select username from usermst where userid=2"
string getusername = command.ExecuteScalar();

It is showing me this error message:

System.NullReferenceException: Object reference not set to an instance of an object

This error occurs when there is no row in the database table for userid=2.
How should I handle this situation?

Solution 1

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.

Solution 2

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 = sql
object userNameObj = command.ExecuteScalar()
if (userNameObj != null)
  string getUserName = userNameObj.ToString()
 ...

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

Solution 3

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

Solution 4

The following line:

string getusername = command.ExecuteScalar();

... will try to implicitly convert the result to string, like below:

string getusername = (string)command.ExecuteScalar();

The regular casting operator will fail if the object is null. Try using the as-operator, like this:

string getusername = command.ExecuteScalar() as string;

Solution 5

Check out the example below:

using System;
using System.Data;
using System.Data.SqlClient;

class ExecuteScalar
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText ="SELECT COUNT(*) FROM Employee";
    mySqlConnection.Open();

    int returnValue = (int) mySqlCommand.ExecuteScalar();
    Console.WriteLine("mySqlCommand.ExecuteScalar() = " + returnValue);

    mySqlConnection.Close();
  }
}

from this here

View more solutions

Comments

[Fredrik Mörk]

That will probably not help a lot since it is the call to ExecuteScalar that throws the exception.

[Rune Grimstad]

Hmm... That might be the problem, but the poster describes the problem as "no row exists ... for id = 2" - hence I assume that the database connection is setup correctly. Updating my answer anyhow.

[Hemant Kothiyal]

Yess it work, But i am not getting why should we avoid using executescalar()

[Hemant Kothiyal]

Thanks, It works, but can you compare your answer with others(Rune Grimstad,Fredrik Mörk ) I am confused now which one is best practice

[Tommy Carlier]

If you only want to get 1 value, ExecuteScalar is a good method to use. You just have to be careful how you handle the object you get. Using the as-operator will not throw a NullReferenceException when casting null to a string.

[Rune Grimstad]

You shouldn't avoid using ExecuteScalar. It is the recommended method to use when you only want a single return value from a query. The problem is that it returns an Object, not a value of the type you want. The reason is that the database can contain a null value in the column and the query may not return a value at all. That is why you store the value in an object type variable that you cast to the apropriate type.

[Branko Dimitrijevic]

The: object o = null; string s = (string)o; does not throw an exception, so I'm wondering if the exception was really because of ExecuteScalar() returning a null? @HemantKothiyal did you actually test this in run-time and confirmed that replacing a cast with as stops the exception? And did you get the expected result in getusername?

[Branko Dimitrijevic]

@HemantKothiyal BTW, string getusername = command.ExecuteScalar(); does not compile - error CS0266: Cannot implicitly convert type 'object' to 'string'. An explicit conversion exists (are you missing a cast?) Could you post the actual code you have used?

[Andrew Barber]

Would you be willing to explain more about this code, and why it is an answer to the question posed?

[WhySoSerious]

I tried your code and I get thrown an exception in the if condition statement, so I changed it simple to: if (result != null) and it worked. Still +1 for your answer. Thanks.

[iheanyi]

Problem elsewhere? like using getusername without checking if it's null in code he did not post? After all, a NullReferenceException implies someone tried to dereference (not assign) a null.

[Branko Dimitrijevic]

@iheanyi Apparently so.

[Nishantha]

even with null value checking goes inside if condition. DBNull is Working! result.GetType() != typeof(DBNull)

[varsha]

for the simple and logical answer +1 .

[Mike Cluck]

How does this fix OPs problem?

[SMA]

If he wants to avoid exception then he needs to handle the scenario where the query might return null value.

[Hsu Wei Cheng]

@HemantKothiyal check the msdn about "as" operator ( msdn.microsoft.com/zh-tw/library/cscsdfbt.aspx ) The as operator is like a cast operation.However, if the conversion isn't possible, as returns null instead of raising an exception.Consider the following example:

[Kritner]

wouldn't this double execute the query? check if result of query is not null, if it's not, then execute the query assigning to var?

[Olivier Jacot-Descombes]

In this specific case the as operator is useful because it will return null when ExecuteScalar returns a DBNull object (when the first column contains NULL) and return the string or null (when the row is not found) otherwise. The last line is correct, but the explanation is flawed.

[Olivier Jacot-Descombes]

This will fail if the first column of the first row contains NULL, because then ExecuteScalar returns DBNull. The line should read: string getusername = command.ExecuteScalar() as string;

[Branko Dimitrijevic]

@OlivierJacot-Descombes Yes, I mentioned the InvalidCastException in my answer.

[Tobias Brösamle]

While code-only answers might solve the problem at hand (even if it is eleven years old), it will not help future readers to understand what the problem is and why it can be solved this way. So please do also add an explanation (not just comments in your code-only answer).