Return value using String result=Command.ExecuteScalar() error occurs when result returns null

executescalar returns null
executescalar return value stored procedure
executescalar vs executenonquery
c# executescalar with parameters
c# executescalar to int
executescalar dapper
executescalar powershell
execute scalar in asp.net example

I want to fetch 1st row 1st cell value from database it works well with below code . But when there is no result found it throws Exception.

How to handle with DBNull . Should i change my query ? which return some value if theirs no record ?

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

Code:

    public string absentDayNo(DateTime sdate, DateTime edate, string idemp)
    { 
       string result="0";
       string myQuery="select COUNT(idemp_atd) absentDayNo from td_atd where ";
       myQuery +=" absentdate_atd between '"+sdate+"' and '"+edate+" ";
       myQuery +=" and idemp_atd='"+idemp+"' group by idemp_atd ";

       SqlCommand cmd = new SqlCommand(myQuery, conn);
       conn.Open();
//System.NullReferenceException occurs when their is no data/result
       string getValue = cmd.ExecuteScalar().ToString();
         if (getValue != null)
         {
            result = getValue.ToString();
         }
         conn.Close();
        return result;
    }

There is no need to keep calling .ToString() as getValue is already a string.

Aside that, this line could possibly be your problem:

 string getValue = cmd.ExecuteScalar().ToString();  

If there are no rows .ExecuteScalar will return null so you need to do some checking.

For instance:

var firstColumn = cmd.ExecuteScalar();

if (firstColumn != null) {
    result = firstColumn.ToString();
}

SqlCommand.ExecuteScalar Method (System.Data.SqlClient , The problem here is despite my database containing proper values, the ExecuteScalar command is always sending null value and this results in "  "ExecuteScalar returns the first column of the first row in the result set" If the first column of the first row in the result set was the value NULL in SQL ExecuteScalar will return DBNull.Value if no rows where returned ExecuteScalar will return null.

[Solved] Execute.scalar command always returns null, Return value using String result=Command.ExecuteScalar() error occurs when result returns null. Question. I want to fetch 1st row 1st cell value from database it​  The following example creates a SqlCommand and then executes it using ExecuteScalar. The example is passed a string representing a new value to be inserted into a table, and a string to use to connect to the data source. The function returns the new Identity column value if a new row was inserted, 0 on failure.

Try this one

var getValue = cmd.ExecuteScalar();    
conn.Close();
return (getValue == null) ? string.Empty : getValue.ToString();

MySqlCommand.ExecuteScalar Method, and returns the first column of the first row in the result set returned by the query. of the first row in the result set, or a null reference if the result set is empty Use the ExecuteScalar method to retrieve a single value (for example, The example is passed a string that is a SQL statement that returns an aggregate result,  Having said that, I don't see the point of using ExecuteScalar if you are grabbing the identity value from an output parameter. In that case, the value returned by ExecuteScalar becomes useless. An approach that I like because it requires less code, uses ExecuteScalar without output parameters:

You can use like the following

string result = null;
object value = cmd.ExecuteScalar();
 if (value != null)
 {
    result = value.ToString();
 }     
 conn.Close();
return result;

error in ExecuteScalar, hi,I will write dataaccess class,but An exception of type 'System. private static SqlConnection connection { get { return new SqlConnection(System. ToString​(); } public string ExecuteScalar(string commandText, "If the first column of the first row in the result set is not found, a null reference (Nothing in  It is a Blank (Null) value, distinct from a string. Similar to a Boolean that can be only True or False, in addition a third possible value of Null is possible. For the most part, the Text Input control will return an empty string if no characters are within it.

Value is not null, but DBNull.Value.

object value = cmd.ExecuteScalar();
if(value == DBNull.Value)

ExecuteScalar Vs ExecuteNonQuery, Object (returns a single value) Use : Executes the query, and returns the first column of the first row in the result set returned by the query. For example, if A1 and A3 contain numbers, and A2 contains a formula with a CUBEVALUE function that returns a zero-length string, the following formula would return a #VALUE! error: =A1+A2+A3 To prevent this, you can test for a zero-length string by using the ISTEXT function and by using the IF function to replace the zero-length with a 0 (zero

C# ADO.NET SqlCommand, The ExecuteScalar() in C# SqlCommand Object is using for retrieve a single value from If the Result Set is empty it will return a NULL reference. null; SqlConnection cnn ; SqlCommand cmd ; string sql = null; connetionString = "Data Select Count(*) from product"; cnn = new SqlConnection(connetionString); try { cnn. The representation is written as a null terminated string to buf at most buf_len bytes are written, truncating the result if necessary. The total amount of space required is returned. The total amount of space required is returned.

ExecuteScalar, Page 124 of 1917 connection, and returns the first column of the first row in the result set returned by the query. If the query does not return any row, it returns null . if the value of the XmlCommandType property is set to one of the following Client; class ExecuteScalarSample { static void Main() { string constr = "User  Async Return Types (C#) 04/14/2020; 8 minutes to read +7; In this article. Async methods can have the following return types: Task<TResult>, for an async method that returns a value. Task, for an async method that performs an operation but returns no value. void, for an event handler. Starting with C# 7.0, any type that has an accessible

Could this ExecuteScalar call be written better?, ExecuteScalar(); return noOfRecords != null; } }. There is no need to close or dispose, the using handles that part. either use parameterized SQL or a stored procedure as opposed to string An implicit conversion for the parameter value for the sql is done, it can result in bad performance. Value = paramA; command​. The strangest part is.. The database has many values. This code is always sending null for all the values present in the DB, Except for one particular value. If i send the ID of this particular value, i always get the exact corresponding value of this particular ID. But the same does'nt work for other values.

Comments
  • Where it throws exception ??
  • Exception throws string getValue = cmd.ExecuteScalar().ToString(); mean error occurs when their is no result
  • btw, that is a really bad way to build a query; clear and blatant sql injection hole; there's also an internationalisation issue, and some unnecessary intermediate strings. I hope that isn't how you do sql normally...
  • @MarcGravell: Sry for my bad practising habbit , and yes next time i take care of sqlInjection, by using parameterize queries
  • @Satindersingh as per my edited answer: you can actually solve both issues in one go... just saying...
  • thanks this works well, also Naresh Parmar answer worked for my by adding check for string is ""
  • or you could use Convert.ToString(cmd.ExecuteScalar()); - this will give you String.Empty if the value is null.
  • Thanks for nice explanation, 1st time heard about dapper, learn new today
  • @Satindersingh it is freely available on nuget: nuget.org/packages/Dapper
  • @Satindersingh where did you try? what code did you use? the code in the question does not show this...
  • @Satindersingh: No issues... please do not apologize. Its fine if it solves the problem.
  • @saravanan: one query out of this topic, how you added adds in you blog, want to add in my satindersinght.blogspot.in/2013/04/…
  • @Satindersingh: Please follow the steps in this link: support.google.com/blogger/bin/answer.py?hl=en&answer=50288. You will have to register for Adsense with your account and add the appropriate content to the blog.