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

User · Answer

Try this one  if null set 0 or something  return command ExecuteScalar      DBNull Value   0    double command ExecuteScalar

User · Answer

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

User · Answer

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 lt int gt       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

User · Answer

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

User · Answer

Try this one  var getValue   cmd ExecuteScalar        conn Close    return  getValue    null    string Empty   getValue ToString

User · Answer

Value is not null  but DBNull Value   object value   cmd ExecuteScalar    if value    DBNull Value

User · Answer

There is a advance feature of c   use that        string getValue   cmd ExecuteScalar    ToString     thants all

User · Answer

To work with NpgsqlCommand or the standard sqlCommand use   int result   int Parse cmd ExecuteScalar   ToString

User · Answer

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

User · Answer

try this       string getValue   Convert ToString cmd ExecuteScalar

User · Answer

You can use like the following  string result   null  object value   cmd ExecuteScalar     if  value    null         result   value ToString             conn Close    return result

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

Examples related to c#

Examples related to sql

Examples related to asp.net

Examples related to sql-server

Examples related to database