Null dates

[GoogleCodeExporter]

I have a dashboard application that reads data from excel spreadsheets on
the file system.  Each row in the spreadsheet consists of a number of
columns with string values and one column with a date value.  Some of the
rows do not have date values.  In one of my reports on the dashboard, I
select all the rows from the spreadsheet to display based on a filter in
the UI.  The code works without issue.

Yesterday I updated the spreadsheet on the file system with a new copy. 
The spreadsheet name, workbook name and column names are exactly the same.
 The data in the spreadsheet includes the original rows (some without the
date column value) plus some additional rows (all with date values).  What
I am finding is that my code that previously worked now throws an error
message "String was not recognized as a valid DateTime." 

The code looks like this:

var rebates = from g in workbook.Worksheet<RebateSubmitted>("Query Results")
              where g.TaskSubmitDate > new DateTime(1997, 1, 1)
              select g;

if (rebates.Count() > 0)
{
foreach (RebateSubmitted r in rebates)
{
    if (r.TaskSubmitDate != null && 
    !list.Contains(r.TaskSubmitDate.Year) && 
    r.TaskSubmitDate.Year != 1)
    {
        list.Add(r.TaskSubmitDate.Year);
    }
}
}

The error is thrown on the foreach line.  

What I don't understand is why it previously works and with the addition of
new data (none of which has null data column values) the code now breaks.

I thought I might try to check for null on the date by adding a check in
the LINQ query, however, I receive the following error: "Unable to cast
object of type 'System.Linq.Expressions.MemberExpression' to type
'System.Linq.Expressions.MethodCallExpression'."

Other than removing the rows without a date (which I would rather not do),
is there a work around for this issue?

I am using version 1.2.64.

Thanks for your time.

Original issue reported on code.google.com by nosnibor...@gmail.com on 28 Apr 2010 at 12:43

[GoogleCodeExporter]

Can you attach the excel file that is causing the issue, and I will take a look 
at it

Original comment by paulyo...@gmail.com on 28 Apr 2010 at 3:31

• Changed state: Started

[GoogleCodeExporter]

Hi, I am attaching the spreadsheet I am using.  I have scrubbed some data from 
the
spreadsheet because it is client sensitive, but the column in question is 
included. 
Thanks for taking the time to investigate.

Original comment by nosnibor...@gmail.com on 28 Apr 2010 at 3:50

Attachments:

• data.xlsx

[GoogleCodeExporter]

I won't have time to look at it this week, but I will get back to you by early 
next week.

Original comment by paulyo...@gmail.com on 28 Apr 2010 at 11:20

[GoogleCodeExporter]

After researching the problem it looks like the Jet OleDb driver is having 
issues handling a 
datetime column with the first 8 rows being null. I did find a work around 
though.

You need to first return all the excel rows and convert it to a list in memory:
var rebateList = (from g in workbook.Worksheet<RebateSubmitted>("Query Results")
                 select g).ToList();

Then you can use a LINQ statement to query the in-memory list
var filteredRebates = from r in rebateList
                      where r.TaskSubmitDate > new DateTime(1997, 1, 1)
                      select r;

Let me know if this works for you.

Original comment by paulyo...@gmail.com on 3 May 2010 at 8:31

[GoogleCodeExporter]

Original comment by paulyo...@gmail.com on 12 May 2010 at 5:23

• Changed state: WontFix