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

Answer 1

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

Answer 2

If the first cell returned is a null, the result in .NET will be DBNull.Value

If no cells are returned, the result in .NET will be null; you cannot call ToString() on a null. You can of course capture what ExecuteScalar returns and process the null / DBNull / other cases separately.

Since you are grouping etc, you presumably could potentially have more than one group. Frankly I'm not sure ExecuteScalar is your best option here...

---

Additional: the sql in the question is bad in many ways:

• sql injection
• internationalization (let's hope the client and server agree on what a date looks like)
• unnecessary concatenation in separate statements

I strongly suggest you parameterize; perhaps with something like "dapper" to make it easy:

int count = conn.Query<int>(   @"select COUNT(idemp_atd) absentDayNo from td_atd     where absentdate_atd between @sdate and @edate     and idemp_atd=@idemp group by idemp_atd",     new {sdate, edate, idemp}).FirstOrDefault(); 

all problems solved, including the "no rows" scenario. The dates are passed as dates (not strings); the injection hole is closed by use of a parameter. You get query-plan re-use as an added bonus, too. The group by here is redundant, BTW - if there is only one group (via the equality condition) you might as well just select COUNT(1).