MSACCESS OleDbException - Data type mismatch in criteria expression.

[bentayloruk]

A friend, soon to be former, has asked me to hack on an Access DB for them. I've written a very basic query, that looks like this:

    let startDate = DateTime.UtcNow.AddDays(-90.)
    query { for header in ctx.BlahTable.Headers do
            where (header.BlahDateTime > startDate)
            select header } |> List.ofSeq

This compiles as the type of BlahDateTime is DateTime and so is startDate. However, at runtime, it blows with the following exception:

System.Data.OleDb.OleDbException was unhandled
Message: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Additional information: Data type mismatch in criteria expression.

The query works with other properties, so this looks like a type mapping issue somewhere. I'm going to dig into this a little myself. However, I thought I would get it raised here too.

If anyone has any pointers...

[colinbull]

The type mapping for the ODBC stuff seems straightforward. I wonder weather this is something to do with the DateTime not being a DateTime per se, but one of those weird OLE Automation Dates like in Excel. If this is the case then just simply boxing here is probably not the thing to do and we should detect to see if the parameter type is DateTime and then call .ToOADate. Maybe?! This is all just guessing thou, as I have never written any code against an Access DB.

[bentayloruk]

Thanks for the pointers @colinbull! I've never written any code against Access either and I don't even have Access, so I can't actually open it and have a look!

Anyway, I've grabbed the source and I'll have a play around and PR if I get this working.

[pezipink]

cheers for this Ben, I'll chime in @bennylynch incase he knows anything about this as access was his work originally

[bennylynch]

@pezipink @bentayloruk Having a look now - I can reproduce, so that's a start ....

[bentayloruk]

@pezipink @bennylynch thanks gents, you are awesome! I get in from my morning surf, and the A Team is on it!

@bennylynch I'll have some time again tomorrow, so let me know if you have this covered, or would like me to do some work on it.

[bennylynch]

@pezipink @bentayloruk Have found the problem - it is to do with how ACE.OLE driver expects DateTIme s to look in the SQL passed to it - they need to be #YYYY-MM-DD# or DD/MM/YY without quotes. Have got it to work with a hack here for now, til I get a chance to do it proper. Don't think it's up to a PR ....

[bennylynch]

@pezipink @bentayloruk OK - have pinned the problem down a bit further. The problem only occurs if the millisecond component is something other than 0. This is actually a known problem with DateTime OleDbParameters, discussed (eg) here. One of the workarounds is in fact to call ToString(), which effectively gets rid of ms component; so perhaps this hack is as good as is acheivable?

[pezipink]

Sounds alright to me, Access had its share of oddities anyway!

[bentayloruk]

@bennylynch it appears that this issue is resolved by being explicit about the parameter OleDbType, i.e. we set it to OleDbType.DBDate. I've made the change for you to have a look at. It works for me and I think it's more obvious/safe than the ToString() fix. What do you think?

Only thing I'm not sure about, is whether the DateTime type is ever mapped to other OleDbTypes. Will have a look.

[bentayloruk]

Hmmm, my fix contains a new bug. DateTime is also mapped to DBTimeStamp. Guess we need to check the column type too, when doing the explicit map.

[pezipink]

What happened with this? @bentayloruk if you only need select and where you can try the ODBC provider instead

[bentayloruk]

I was hacking on something for a friend, while away. I've had to shelve it for now, but will come back to it at some point.

[pezipink]

ok, ODBC probably won't work either for different reasons but it is all good fun right

[bentayloruk]

It's some of the funnest fun I've funed in funges.