Link to home
Start Free TrialLog in
Avatar of jamielaing
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(dsMyDataset1);

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?
Avatar of Dabas
Dabas
Flag of Australia image

Hi jamielaing,
This field in the Access Table. Is it used for time purposes only. That is, the date is of no importance?

Dabas
Avatar of jamielaing
jamielaing

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.
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.
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.
jamielaing:
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')
Thanks Kavar, I'm familiar with that one, but I'm hoping to update through the dataset as follows...

oleDbDataAdapter1.Update(dsMyDataset1);

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.
does the oleDbDataAdapter have the "WillChangeRecord" event?
if so, use that event to subclass your date field with cdate()
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(dataset) 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.
ASKER CERTIFIED SOLUTION
Avatar of Kavar
Kavar

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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