Return Value Using String Result=Command. Executescalar() Error Occurs When Result Returns Null

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).

Try this one

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

Value is not null, but DBNull.Value.

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

You can use like the following

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

try this :

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

This should work:

var result = cmd.ExecuteScalar();
conn.Close();

return result != null ? result.ToString() : string.Empty;

Also, I'd suggest using Parameters in your query, something like (just a suggestion):

var cmd = new SqlCommand
{
    Connection = conn,
    CommandType = CommandType.Text,
    CommandText = "select COUNT(idemp_atd) absentDayNo from td_atd where absentdate_atd between @sdate and @edate and idemp_atd=@idemp group by idemp_atd"
};

cmd.Parameters.AddWithValue("@sdate", sdate);
cmd.Parameters.AddWithValue("@edate", edate);
// etc ...

Use SQL server isnull function

public string absentDayNo(DateTime sdate, DateTime edate, string idemp)
{ 
    string result="0";
    string myQuery="select isnull(COUNT(idemp_atd),0) as 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;
}

Try this one, if null set 0 or something

return command.ExecuteScalar() == DBNull.Value ? 0 : (double)command.ExecuteScalar();

There is a advance feature of c#, use that '?.' . string getValue = cmd.ExecuteScalar()?.ToString(); thants all.

To work with NpgsqlCommand or the standard sqlCommand use:

int result = int.Parse(cmd.ExecuteScalar().ToString());