Specified cast is not valid, when column returns NULL

W

Web learner

while (dr.Read())
//dr is an instance of sqlDataReader
{
double minAirTemp = (double)(Single)dr["minAirTemp"];
// minAirTemp is column in SQLExpress table (data type: real, allows nulls)
}

The above code works. But when there are NULL, I get the following error
System.InvalidCastException: Specified cast is not valid

To solve this problem, I spent a day on Google. Finally I got a hint from http://www.codeproject.com/cs/database/SmartReader.asp and replaced the code line in while{} as follows:

double minAirTemp1 = (dr.IsDBNull(dr.GetOrdinal("minAirTemp1")))
? 9999 : double.Parse(dr["minAirTemp1"].ToString()); //9999 to respresent nulls

This works for me for now. But just wondering, whether there is a better method to deal with such a situation????

Can someone be kind to point me to some suitable reference !

Thanks !
 
J

Jim Hughes

VS2005 has a double.TryParse

If the TryParse failes, then set the value to DBNull.Value
while (dr.Read())
//dr is an instance of sqlDataReader
{
double minAirTemp = (double)(Single)dr["minAirTemp"];
// minAirTemp is column in SQLExpress table (data type: real, allows
nulls)
}

The above code works. But when there are NULL, I get the following error
System.InvalidCastException: Specified cast is not valid

To solve this problem, I spent a day on Google. Finally I got a hint from
http://www.codeproject.com/cs/database/SmartReader.asp and replaced the code
line in while{} as follows:

double minAirTemp1 = (dr.IsDBNull(dr.GetOrdinal("minAirTemp1")))
? 9999 : double.Parse(dr["minAirTemp1"].ToString()); //9999 to respresent
nulls
This works for me for now. But just wondering, whether there is a better
method to deal with such a situation????
Can someone be kind to point me to some suitable reference !
Thanks !
 
F

Flinky Wisty Pomm

I have a big class called SqlHelper full of methods like:

public static decimal GetReaderDecimal(IDataRecord dr, int column,
decimal ifNull)
{
return dr.IsDBNull(column)? ifNull : dr.GetDecimal(column);
}
 
B

Bruce Wood

I did roughly the same thing in C# 1.1: write a static helper method to
which you pass the row, the column name, and the default value, and it
returns the value. I had one for each primitive type, including one for
strings.

In C# 2.0 there are nullable types, but I'm not sure how they are at
mediating between DbNull and null.
 
M

Martin Carpella

Bruce Wood said:
In C# 2.0 there are nullable types, but I'm not sure how they are at
mediating between DbNull and null.

In C# 2.0 you can use the "as" operator in conjunction with nullable
types, e.g.:

IDataReader r = ...;
int? val = r["MyColumn"] as int?;

This will gracefully handle the case that r["MyColumn"] contains a
DbNull.

Best regards,
Martin
 
W

Web learner

For a beginner like me,the whole scenario is overwhelmingly confusing. It is
hard to figure out what is legacy and what is cutting-edge elegant methods.

Can someone be kind to point out to latest tutorial material on this topic?
Please refer to my original messsage.

Thanks !
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top