Return value using String result=Command.ExecuteScalar() error occurs when result returns null
Solution 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();
}
Solution 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)
.
Solution 3
Try this one
var getValue = cmd.ExecuteScalar();
conn.Close();
return (getValue == null) ? string.Empty : getValue.ToString();
Solution 4
You can use like the following
string result = null;
object value = cmd.ExecuteScalar();
if (value != null)
{
result = value.ToString();
}
conn.Close();
return result;
Solution 5
Value is not null, but DBNull.Value.
object value = cmd.ExecuteScalar();
if(value == DBNull.Value)
Satinder singh
I am a Full Stack Developer have a passion to create, solve, and deploy software applications. I have a great passion to learn new things. ForEach(minute in MyLife) { myExperience ++; } Blog: Codepedia Video: Subscribe Youtube Channel
Updated on May 14, 2020Comments
-
Satinder singh almost 4 years
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; }