Problems with IDataRecord.IsDBNull

  • Thread starter Thread starter Chukkalove
  • Start date Start date
C

Chukkalove

Visual Studio 2003. MySQL database.

The following code throws a "Unable to convert MySQL date/time value to
System.DateTime" when the column contains a null value

if (Reader.IsDBNull(nCount) == false) foo();

This has me stumped. I thought the function was there to test for this
condition but it appears to be falling over.

Is there any way I can get round this please
?

thanks
 
I would be interested in knowing exactly what value is in the field at the
time. Perhaps for some reason the NULL is not coming back as such from the
database?
 
More info

Looking at the table, the value in this column is "0000-00-00 00:00:00"
which is not null but would cause a problem on conversion from MySQL
datetime to a DotNet DateTime value as it IS actually less than
DateTime.MinDate
Even so, I wouldnt expect a check for a null field to throw an exception if
a value is out of bounds. I would expect this error when I try to read from
the reader into a variable.
Im using this check at the outer level before validating each individual
field value.
 
Chukkalove said:
Visual Studio 2003. MySQL database.

The following code throws a "Unable to convert MySQL date/time value
to System.DateTime" when the column contains a null value

if (Reader.IsDBNull(nCount) == false) foo();

This has me stumped. I thought the function was there to test for
this condition but it appears to be falling over.

Is there any way I can get round this please
?
Click to expand...

WHich provider are you using?
Also, IsDBNull(ordinal) is very very slow. You should do:
if(reader.GetValue(ordinal)==System.DBNull.Value)
{
foo();
}

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Thanks for your reply Frans

The database is MySQL, using their dot net connector libraries for ADO. Is
this a problem with the provider or dotnet?
I tried using your suggestion
if (reader.GetValue(nCount) == System.DBNull.Value) foo();
And received the same exception where the value in the column was out of
bounds ie the field was not null but holds a value which is out of range for
DateTime "0000-00-00 00:00:00"

My hack for this is as follows, but it's horrible!
public void ReadFromReader(IDataReader reader)
{
string fnName = "ReadFromReader";

try
{
// check each column for null values
for (int nCount = 0; nCount < reader.FieldCount; nCount++)
{
try
{
if (reader.GetValue(nCount) == System.DBNull.Value) continue;
}
catch
{
continue;
}

string column = reader.GetName(nCount);
ReadFields(reader, column);

}//for

}
catch(Exception e)
{
LogException(fnName, e);
throw e;
}// catch
}// function
 
I don't think this is not the fault of IsDBNull. I have no idea why MySQL
would allow a datetime value that is neither a NULL nor a valid date.

Why don't you store actual nulls instead of all 0's. Then you should get the
expected behavior.
 
Back
Top