ASP.NET - cmd.executescalar() is not returning null value..?

Asked By sunil pandey on 21-Jan-12 01:09 AM

Hi all

i want to fetch null value from table.but i am getting error object can not cast DBNULL to others type

SqlCommand cmd = new SqlCommand("select sum(payment) from cust_payment where cust_name='" + Cname + "'", conn);

int due = Convert.ToInt32(cmd.ExecuteScalar());

Sandeep Mittal replied to sunil pandey on 21-Jan-12 01:14 AM

change sql statement

SqlCommand cmd = new SqlCommand("select isnull(sum(payment),0) from cust_payment where cust_name='" + Cname + "'", conn)

sunil pandey replied to Sandeep Mittal on 21-Jan-12 01:28 AM

end of post

Suchit shah replied to sunil pandey on 21-Jan-12 01:49 AM

if you are retrunig NULL from DB you need to handle it like below condition one

object result = command.ExecuteScalar();

if( result != null && result != DBNull.Value )
{
return Convert.ToInt32( result );
}
else
{
return 1;
}

steps are below
(1) take the Execute scaler result in object
(2) check that result is NULL .... if NULL then do the operation which you required or return the value you want
(3) if it not NULL then you can do the operation which you required

Try on this way and let me know

dipa ahuja replied to sunil pandey on 21-Jan-12 03:05 AM

Put the condition before parse:

if(cmd.ExecuteScalar() != null && cmd.ExecuteScalar() != DBNull.Value);
{
int due = Convert.ToInt32(cmd.ExecuteScalar());
}