ExecuteScalar () processing when results are not returned

Question

I use the following SQL query and the ExecuteScalar() method to retrieve data from an Oracle database:

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

This shows me this error message:

System.NullReferenceException: object reference not set to object instance

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

Answer 1

According to the MSDN documentation for DbCommand.ExecuteScalar :

If the first column of the first row in the result set is not found, an empty reference is returned (Nothing in Visual Basic). If the value in the database is zero, 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 (checked in ODP.NET, but should be the same in any ADO.NET provider), it behaves like this:

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

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

Answer 2

First you need to make sure your command object is not null. Then you must set the CommandText property of the command for your SQL query. Finally, you must save the return value in an object variable and check if it is empty 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 syntax of VB, but you get the idea.

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

Answer 4

Next line:

 string getusername = command.ExecuteScalar(); 

... will try to implicitly convert the result to a string, as shown below:

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

A regular cast statement will fail if the object is null. Try using as-operator, for example:

 string getusername = command.ExecuteScalar() as string; 

Answer 5

 sql = "select username from usermst where userid=2" var _getusername = command.ExecuteScalar(); if(_getusername != DBNull.Value) { getusername = _getusername.ToString(); } 

Answer 6

this may help .. example ::

 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 here

Answer 7

Always check before reading a line.

 if (SqlCommand.ExecuteScalar() == null) { } 

Answer 8

SQL NULL Value

• equivalent in C # is DBNull.Value
• if the NULLABLE column does not matter, this is what is returned
• comparison in SQL: IF ( value IS NULL )
• comparison in C #: if (obj == DBNull.Value)
• Visually represented in C # Quick-Watch as {}

Best practice when reading from a data reader:

 var reader = cmd.ExecuteReader(); ... var result = (reader[i] == DBNull.Value ? "" : reader[i].ToString()); 

In my experience, in some cases, the return value may not be present and, therefore, execution fails, returning zero. An example would be

 select MAX(ID) from <table name> where <impossible condition> 

The above script cannot find anything to find MAX. Thus, he fails. In such cases, we must compare the old way (compare with C # null )

 var obj = cmd.ExecuteScalar(); var result = (obj == null ? -1 : Convert.ToInt32(obj)); 

Answer 9

In your case, either the record does not exist with userid=2 , or it may contain a null value in the first column, because if no value is found for the query result used in the SQL command, ExecuteScalar() returns null .

Answer 10

This is the easiest way to do this ...

 sql = "select username from usermst where userid=2" object getusername = command.ExecuteScalar(); if (getusername!=null) { //do whatever with the value here //use getusername.toString() to get the value from the query } 

Answer 11

Alternatively, you can use a DataTable to check if there is a row:

 SqlCommand cmd = new SqlCommand("select username from usermst where userid=2", conn); SqlDataAdapter adp = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adp.Fill(dt); string getusername = ""; // assuming userid is unique if (dt.Rows.Count > 0) getusername = dt.Rows[0]["username"].ToString(); 

Answer 12

 private static string GetUserNameById(string sId, string connStr) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr); System.Data.SqlClient.SqlCommand command; try { // To be Assigned with Return value from DB object getusername; command = new System.Data.SqlClient.SqlCommand(); command.CommandText = "Select userName from [User] where userid = @userid"; command.Parameters.AddWithValue("@userid", sId); command.CommandType = CommandType.Text; conn.Open(); command.Connection = conn; //Execute getusername = command.ExecuteScalar(); //check for null due to non existent value in db and return default empty string string UserName = getusername == null ? string.Empty : getusername.ToString(); return UserName; } catch (Exception ex) { throw new Exception("Could not get username", ex); } finally { conn.Close(); } } 

Answer 13

Minor hypothesis: if you check the stack for an exception, it throws, and the ADO.NET provider for Oracle reads the basic set of rows to get the first value.

If there is no line, then there is no value to search.

To handle this case, execute for reading and process Next() , returning false for the case with no match.

Answer 14

I use it like this with the Microsoft Application Block DLL (its reference library for DAL operations)

 public string getCopay(string PatientID) { string sqlStr = "select ISNULL(Copay,'') Copay from Test where patient_id=" + PatientID ; string strCopay = (string)SqlHelper.ExecuteScalar(CommonCS.ConnectionString, CommandType.Text, sqlStr); if (String.IsNullOrEmpty(strCopay)) return ""; else return strCopay ; } 

Answer 15

I saw in VS2010 string getusername = command.ExecuteScalar(); gives a compilation error. It is not possible to implicitly convert a type object to a string. Therefore you need to write string getusername = command.ExecuteScalar().ToString(); when there is no record in the database, it gives an error In the instance of the object, no reference to the object is given and when I comment on '.ToString ()', it does not give any error. Therefore, I can say that ExecuteScalar does not throw an exception. I think the underwater given by @Rune Grimstad is right.

Answer 16

I had this problem when a user connecting to the database had CONNECT permissions but was not allowed to read data from the database. In my case, I could not even do something like this:

object userNameObj = command.ExecuteScalar()

Putting this in try / catch (which you probably should do anyway) was the only way I could handle the insufficient resolution problem.

Answer 17

If you want either a string or an empty string in case something is equal to zero, a break can occur without anything :

 using (var cmd = new OdbcCommand(cmdText, connection)) { var result = string.Empty; var scalar = cmd.ExecuteScalar(); if (scalar != DBNull.Value) // Case where the DB value is null { result = Convert.ToString(scalar); // Case where the query doesn't return any rows. // Note: Convert.ToString() returns an empty string if the object is null. It doesn't break, like scalar.ToString() would have. } return result; } 

Answer 18

/ * Select some int that does not exist * / int x = ((int) (SQL_Cmd.ExecuteScalar () ?? 0));

Answer 19

I used this in my VB code for the return value of a function:

If obj <> Nothing Then Return obj.ToString () else Return "" End If

Answer 20

Try this code, it seems to solve your problem.

Dim MaxID As Integer = Convert.ToInt32(IIf(IsDBNull(cmd.ExecuteScalar()), 1, cmd.ExecuteScalar()) )

Answer 21

I am using Oracle . If your sql returns a numeric value, which is an int, you need to use Convert.ToInt32 (object). The following is an example:

 public int GetUsersCount(int userId) { using (var conn = new OracleConnection(...)){ conn.Open(); using(var command = conn.CreateCommand()){ command.CommandText = "select count(*) from users where userid = :userId"; command.AddParameter(":userId", userId); var rowCount = command.ExecuteScalar(); return rowCount == null ? 0 : Convert.ToInt32(rowCount); } } } 

Answer 22

try it

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