Problems with IDataRecord.IsDBNull

28 Jun 2006 1:41 PM

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

28 Jun 2006 1:59 PM

Marina Levit [MVP]

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?

Show quote

"Chukkalove" <some***@microsoft.com> wrote in message
news:OWQ4WirmGHA.3796@TK2MSFTNGP05.phx.gbl...
> 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
>
>
>

28 Jun 2006 2:06 PM

Chukkalove

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.

29 Jun 2006 7:11 AM

Frans Bouma [C# MVP]

Chukkalove wrote:

> 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
> ?

    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#)
------------------------------------------------------------------------

29 Jun 2006 10:35 AM

Chukkalove

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

29 Jun 2006 3:06 PM

Marina Levit [MVP]

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.

Show quote

"Chukkalove" <some***@microsoft.com> wrote in message
news:uoubPf2mGHA.4212@TK2MSFTNGP04.phx.gbl...
> 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
>