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

Exception throws string getValue = cmd.ExecuteScalar().ToString(); mean error occurs when their is no result — Satinder singh
– Satinder singh, Commented May 20, 2013 at 10:16
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... — Marc Gravell
– Marc Gravell, Commented May 20, 2013 at 10:22
@MarcGravell: Sry for my bad practising habbit , and yes next time i take care of sqlInjection, by using parameterize queries — Satinder singh
– Satinder singh, Commented May 20, 2013 at 10:27
@Satindersingh as per my edited answer: you can actually solve both issues in one go... just saying... — Marc Gravell
– Marc Gravell, Commented May 20, 2013 at 10:29

Darren · Accepted Answer · 2013-05-20 10:16:03Z

69

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

answered May 20, 2013 at 10:16

Darren

71.2k24 gold badges141 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Satinder singh Over a year ago

thanks this works well, also Naresh Parmar answer worked for my by adding check for string is ""

DrNachtschatten Over a year ago

or you could use Convert.ToString(cmd.ExecuteScalar()); - this will give you String.Empty if the value is null.

Marc Gravell · Accepted Answer · 2013-05-20 10:25:45Z

22

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

edited May 20, 2013 at 10:25

answered May 20, 2013 at 10:17

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

2 Comments

Satinder singh Over a year ago

Thanks for nice explanation, 1st time heard about dapper, learn new today

Marc Gravell Over a year ago

@Satindersingh it is freely available on nuget: nuget.org/packages/Dapper

Rajeev Kumar · Accepted Answer · 2013-05-20 10:35:51Z

9

Try this one

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

edited May 20, 2013 at 10:35

answered May 20, 2013 at 10:17

Rajeev Kumar

4,9739 gold badges53 silver badges91 bronze badges

Comments

Jānis · Accepted Answer · 2013-05-20 10:34:42Z

7

Value is not null, but DBNull.Value.

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

answered May 20, 2013 at 10:34

Jānis

2,2661 gold badge17 silver badges28 bronze badges

Comments

Saravanan · Accepted Answer · 2013-05-20 10:11:30Z

5

You can use like the following

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

answered May 20, 2013 at 10:11

Saravanan

7,8535 gold badges44 silver badges77 bronze badges

4 Comments

Marc Gravell Over a year ago

@Satindersingh where did you try? what code did you use? the code in the question does not show this...

Saravanan Over a year ago

@Satindersingh: No issues... please do not apologize. Its fine if it solves the problem.

Satinder singh Over a year ago

@saravanan: one query out of this topic, how you added adds in you blog, want to add in my satindersinght.blogspot.in/2013/04/…

Saravanan Over a year ago

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

Naresh Parmar · Accepted Answer · 2013-05-20 10:18:47Z

2

try this :

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

edited May 20, 2013 at 10:18

answered May 20, 2013 at 10:13

Naresh Parmar

4623 silver badges16 bronze badges

Comments

Dimitar Dimitrov · Accepted Answer · 2013-05-20 10:23:12Z

1

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

edited May 20, 2013 at 10:23

answered May 20, 2013 at 10:16

Dimitar Dimitrov

15.2k9 gold badges55 silver badges82 bronze badges

Comments

Muzafar Hussain · Accepted Answer · 2019-01-31 04:31:07Z

1

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

answered Jan 31, 2019 at 4:31

Muzafar Hussain

216 bronze badges

Comments

Martin Pilote · Accepted Answer · 2016-03-02 11:03:02Z

0

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

edited Mar 2, 2016 at 11:03

Martin Pilote

638 bronze badges

answered Feb 23, 2016 at 21:34

hussien

1

Comments

Ramgy Borja · Accepted Answer · 2017-06-24 00:12:14Z

0

Try this one, if null set 0 or something

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

answered Jun 24, 2017 at 0:12

Ramgy Borja

2,4682 gold badges24 silver badges41 bronze badges

Comments

resolv · Accepted Answer · 2015-04-22 17:29:09Z

-1

To work with NpgsqlCommand or the standard sqlCommand use:

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

answered Apr 22, 2015 at 17:29

resolv

471 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

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

11 Answers 11

2 Comments

2 Comments

Comments

Comments

4 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

2 Comments

2 Comments

Comments

Comments

4 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related