C# DateTimeSqlNullValueException: Data is Null

Question

I start MySQL query going to a list with one value that is a DateTime object. As this list builds it hits a null value and --- DateTimeSqlNullValueException: Data is Null.

First I naively tried using ISNULL, !=NULL, ==NULL... which are not methods of the DateTime object. Then I tried

queryResult.EventTime = rdr.GetDateTime("timeIn");

if (Convert.IsDBNull(rdr.GetDateTime("timeIn")))
{
    Console.Write("yeup it's null");
    queryResult2.EventTime = DateTime.MinValue;
}

This didn't work either.

Then I though instead of rdr.GetDateTime() I thought maybe using GetString() instead and use the methods for strings to test for null then change it back to a DateTime and set it to DateTime.MinValueso I could address it in the view later on. This seemed like a total cludge and not very elegant. So I have been searching for the one liner that I can test the nullness of rdr.GetDateTime("timeIn"); and set it to DateTime.MinValue

Sam Holder · Accepted Answer · 2012-03-20 23:19:18Z

3

Don't use GetDateTime until you know the value isn't null:

if (rdr.IsDBNull(rdr.GetOrdinal("timeIn"))))
{
    Console.Write("yeup it's null");
    queryResult2.EventTime = DateTime.MinValue;
}
else
{
    queryResult.EventTime = rdr.GetDateTime("timeIn");
}

edited Mar 20, 2012 at 23:19

Sam Holder

33.1k14 gold badges108 silver badges190 bronze badges

answered Mar 20, 2012 at 22:43

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

rd42 Over a year ago

Thank you for the response. Unfortunately with (rdr.IsDBNull... the compiler complains: Error the best overloaded method match for 'System.Data.Common.DbDataReader.IsDBNull(int)' has some invalid arguments

Sam Holder Over a year ago

@rd42 This is what he meant when he said It's possible that you'll need to give the column index rather than the name see my answer, you need to get the ordinal of the column first.

rd42 Over a year ago

Ah, Thanks Sam. So something like if (Convert.IsDBNull(rdr.GetDateTime(5))) but with the correct syntax

Sam Holder Over a year ago

@rd42 I've updated this answer so it should work. If you want to make this into a one liner at the calling site then try the extension method technique.

Sam Holder Over a year ago

@rd42 yes you need to provide it with a column index, but this is what GetOrdinal does. It takes a string and returns an int so this should be ok. Are you sure this is the error?

|

Sam Holder · Accepted Answer · 2012-03-20 22:58:30Z

you can write an extension method which will do the check and so make it a one liner (untested code, but should give you the idea):

public static GetDateTimeOrDefault(this IDataReader dr, string columnName, DateTime defaultValue)
{
    int ordinal = dr.GetOrdinal(columnName);
    if (dr.IsDbNull(ordinal))
    {
        return defaultValue;
    }
    return dr.GetDateTime(ordinal);
}

you can then call this like so:

queryResult.EventTime = rdr.GetDateTimeOrDefault("timeIn",DateTime.MinValue);

you don't need to pass in the default value, but its a good idea to do so, as you then have control from the calling site to specify the value you want if the database field is null, and the calling site is where you will know what value you want. you may not always want DateTime.MinValue, you may want DateTime.Now sometimes for example

Collectives™ on Stack Overflow

C# DateTimeSqlNullValueException: Data is Null

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related