C# Trouble with datasets and date/time formatting in MS Access
Posted on 2004-03-30
Hello, I'm stumped.
I'm using a legacy Access database that cannot be altered. In it, I have a table with a field of type "date/time".
Using a standard walkthrough, "Walkthrough: Simple Data Access in a Windows Form", I've created a form that allows me to fill a dataset and save it back to the database using a command like..
This works great, except for that one field in my table/dataset/datagrid is this time field, and whenever I edit it, the value goes to the current date. I tried editing the XSD to make that field a string, but when the dataset gets saved back, bingo, it's saved as the current date at 12:00:00 PM.
Further, I've tried creating my own dataset programmatically using DataTables and DataRows, and come up with the same thing.
Investigating further, I've found if I fill in the time in the Access table directly, with a time like "12:18 AM" for example, the value in that field defaults to "12/30/1899 12:18:00 AM". I am aware that this is because Access essentially treats that date as zero. If I load this record in the datagrid, I can see it as the whole date as typed above. Unfortunately if I edit the value at all, the time portion of the value reverts to 12:00:00 PM instantly.
What am I doing wrong?