How can I avoid a “SqlNullValueException: Data is Null” Exception?

[iRead]

The variable sbSQL is an SQL SELECT SUM() statement with a while condition. The while condition may return no records. This code works fine when records are returned but throws the error listed below when the while condition returns no records.

sql = sbSQL.ToString();
SqlCommand comm = new SqlCommand(sql, conn);
comm.Connection.Open();
SqlDataReader r = comm.ExecuteReader(CommandBehavior.CloseConnection);
while(r.Read())
{
double dTotal = (r.GetDouble(0));
}

This line throws the exception> double dTotal = (r.GetDouble(0));

Exception Details: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

I’ve tried some variations of IsDBNull but fear I may be headed down the wrong path. Any input will be greatly appreciated!

Thank you!
iRead

 

[Walid]

Use the r.HasRows property
while(r.HasRows && r.Read()) // Supported by .Net 1.1
{
// By the way, are you sure this is what you
// want to do,
double dTotal = (r.GetDouble(0));
}
You actualy define the dTotal and assign value to it in each iteration !!


Walid Magd
Engwam@Hotmail.com

 

[kneejnick]

No conversions are performed, therefore the data retrieved must already be a double-precision floating point number.

Call IsDBNull to check for null values before calling this method.

that's what VS .NET says ...

 

[obislavu]

Seems to have the first column in the datareader object as a double but it is NULL in the DB.
object val = r[0];
This is the value retrieved by DataReader which is null if the first column e.g column 0 allows NULL and has NULL value in the queried tables.
You should write :

double dTotal =0;
try{
  SqlDataReader r = comm.ExecuteReader(CommandBehavior.CloseConnection); 
    while(( r!=null ) && r.Read())
    { 
         if (r[0]!=null)
             dTotal+ = (r.GetDouble(0));
    }
}

But if you want to do a "SELECT SUM()... " then the best candidate is to use ExecuteScalar():

 SqlCommand comm = new SqlCommand(sql, conn); 
    comm.Connection.Open(); 

comm.CommandText = "select sum(order_amount) from _tOrder where order_date <'2004-04-01'";
float dTotal = (float) cmd.ExecuteScalar();

-obislavu-

 

[iRead]

Walid and obislavu,

Walid you’re right. I don’t want to define the dTotal and assign value to it in each iteration. I’m experimenting with obislavu’s suggestion:

double dTotal = (double) comm.ExecuteScalar();

With that statement I’m still getting an error when the while condition returns no records. Still working on the solution.

For now this statement resolves the no record issue in my original code:

if (!r.IsDBNull(0))

Thank you all for your input!
iRead

 

[cappmgr]

I am not sure it applies here but it is nice to pass back a default vlaue from the database. IsNull is a for SQLServer,

IsNull(column_name,0)
If the column_name is null a 0 is passed back.

If you are using Oracle NVL will do the same.

Marty

 

[psemianonymous]

Something I read, that might be useful to you:

public string CustomerName(int custId)
{
   SqlCommand cmd = new SqlCommand(string.Format("SELECT Name FROM Customers WHERE idCust = {0}", custId), sqlConnectionToSchaakBondDB);
   object result = cmd.ExecuteScalar();

   if (result == null)
      return "Customer not found";
   if (result == System.DBNull.Value)
      return "Customer found but name is null";
   return (string) result;
}

source:

http://dotnetjunkies.com/WebLog/petergekko/archive/2004/04/12/11210.aspx