jamielaing
asked on
C# Trouble with datasets and date/time formatting in MS Access
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..
oleDbDataAdapter1.Update(d sMyDataset 1);
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?
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..
oleDbDataAdapter1.Update(d
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?
ASKER
Yes, this is true, however the legacy application that needs to keep running, and which connects also to the MDB automatically inserts the date "1/1/1900" along with whatever time the user enters. So if the user types "3:00 PM" the database ends up getting "1/1/1900 3:00:00 PM" as the value.
I'd be happy with the time value alone, but it appears "1/1/1900 " + <some time value> would be perfection.
I'd be happy with the time value alone, but it appears "1/1/1900 " + <some time value> would be perfection.
ASKER
As a test, I created a new SQL 2000 database, including a date/time field in one of the tables. Attempting the same thing as above, but using the SqlConnection adapter, I get the exact same results.
In frustration, I'm upping the points. I'd prefer to use datasets if possible, but perhaps they can't handle this seemingly fundamental issue.
In frustration, I'm upping the points. I'd prefer to use datasets if possible, but perhaps they can't handle this seemingly fundamental issue.
ASKER
Partial solution!!
I found a solution that works for Access and the OleDB data adapter, here is where I found it...
http://www.dotnet247.com/247reference/msgs/18/91606.aspx
Basically, change "OleDbType.DbDate" to "OleDbType.Date" in the update and insert sections of the autogenerated code. Shame on you Microsoft!
The points still stand however, if someone can find a similar solution when using the SQL adapter, which the above does not address.
I found a solution that works for Access and the OleDB data adapter, here is where I found it...
http://www.dotnet247.com/247reference/msgs/18/91606.aspx
Basically, change "OleDbType.DbDate" to "OleDbType.Date" in the update and insert sections of the autogenerated code. Shame on you Microsoft!
The points still stand however, if someone can find a similar solution when using the SQL adapter, which the above does not address.
jamielaing:
I am a VB.NET coder.
Will a VB snippet be good enough for you?
Dabas
I am a VB.NET coder.
Will a VB snippet be good enough for you?
Dabas
insert into [Table name] (date field,some_otherfield) values(#03/30/2004#,'some_ othervalue ')
ASKER
Thanks Kavar, I'm familiar with that one, but I'm hoping to update through the dataset as follows...
oleDbDataAdapter1.Update(d sMyDataset 1);
oleDbDataAdapter1.Update(d
ASKER
Dabas, yes, VB snippet is just fine with me. It's all the same set of base classes in the end.
Plus, I'm upping the points again.
Plus, I'm upping the points again.
does the oleDbDataAdapter have the "WillChangeRecord" event?
if so, use that event to subclass your date field with cdate()
ASKER
Thanks Kavar, you bring up a good point with subclassing, but the event you mention is, if I'm not mistaken, part of the recordset object, and I'm trying to work with datasets. In particular, I've solved the problem with regard to the oleDbDataAdapter (see above), now I'm looking for a similar fix using the sqlDataAdapter.
Perhaps I can latch on the the sqlDataAdatper.Update(data set) event as you say, using my own subclass. If that works I'll give you partial points, but I won't have time to test that for a few days. I was kinda hoping for something easier than rolling my own subclass. Perhaps the answer is to blow off using sqlDataAdapter all together, and use OleDb for everything.
Perhaps I can latch on the the sqlDataAdatper.Update(data
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Kavar, thanks so much for looking in to this more. My connector is pure vanilla, the result of creating a drag and drop sqlDataAdatper in VS.Net. I've been pulled into an urgent project for the last few days and haven't had time to respond or look in to this issue yet, but I will in another day or so, so I'll get back to you regarding your much appreciated comments then.
Regards,
jamielaing
Regards,
jamielaing
ASKER
Kavar, I haven't had, and still don't have, the time to get back to this issue. I seriously appreciate your help however, and although the issue isn't completely resolved, I'm giving you the points.
Thanks again,
Jamie Laing
Thanks again,
Jamie Laing
This field in the Access Table. Is it used for time purposes only. That is, the date is of no importance?
Dabas