Solved

C# Trouble with datasets and date/time formatting in MS Access

Posted on 2004-03-30
14
2,607 Views
Last Modified: 2012-05-04
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?
0
Comment
Question by:jamielaing
  • 8
  • 4
  • 2
14 Comments
 
LVL 27

Expert Comment

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

Dabas
0
 

Author Comment

by:jamielaing
Comment Utility
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.
0
 

Author Comment

by:jamielaing
Comment Utility
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.
0
 

Author Comment

by:jamielaing
Comment Utility
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.
0
 
LVL 27

Expert Comment

by:Dabas
Comment Utility
jamielaing:
I am a VB.NET coder.
Will a VB snippet be good enough for you?

Dabas
0
 
LVL 10

Expert Comment

by:Kavar
Comment Utility
insert into [Table name] (date field,some_otherfield) values(#03/30/2004#,'some_othervalue')
0
 

Author Comment

by:jamielaing
Comment Utility
Thanks Kavar, I'm familiar with that one, but I'm hoping to update through the dataset as follows...

oleDbDataAdapter1.Update(dsMyDataset1);

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jamielaing
Comment Utility
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.
0
 
LVL 10

Expert Comment

by:Kavar
Comment Utility
does the oleDbDataAdapter have the "WillChangeRecord" event?
0
 
LVL 10

Expert Comment

by:Kavar
Comment Utility
if so, use that event to subclass your date field with cdate()
0
 

Author Comment

by:jamielaing
Comment Utility
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.
0
 
LVL 10

Accepted Solution

by:
Kavar earned 500 total points
Comment Utility


hmmm, if you have a second, can I see your sqlDataAdapter Constructor?  (more to the point, can I see the code you used your update?)

looks like you change the behavior of update like so...

cmd = New SqlCommand("UPDATE <Table> SET YourField1= @SqlField1, YourField2= @SqlField2 " & _
                       "WHERE PrimaryKey= @SQLPrimaryKey", YourConnection)

  cmd.Parameters.Add("@SQLField1", <Whatever Type>, FieldLength, "YourField1")
  cmd.Parameters.Add("@SQLField2", <Whatever Type>, FieldLength, "Yourfield2")
'heres where you would specify the data type
  cmd.Parameters.Add("@SQLDateField", SqlDbType.NDate, FieldLength (probably 8), "YourDatefield")

.... etc

  parm = cmd.Parameters.Add("@SQLPrimaryKey", <Whatever Type>, FieldLength, "YourKeyField")
  parm.SourceVersion = DataRowVersion.Original

sqlDataAdapter.UpdateCommand = cmd

Now when you do an update, it already knows the proper subclass for your data (supposedly)

**********************************************************************

Now after some reading I found

There are several events that are raised by the DataTable object when a change is occurring in a record:

The ColumnChanging and ColumnChanged events are raised during and after each change to an individual column. The ColumnChanging event is useful when you want to validate changes in specific columns. Information about the proposed change is passed as an argument with the event. For more information see Validating Data During Column Changes.
The RowChanging and RowChanged events are raised during and after any change in a row. The RowChanging event is more general, in that it simply indicates that a change is occurring somewhere in the row; you do not know which column has changed. For more information see Validating Data During Row Changes.
By default, each change to a column therefore raises four events: first the ColumnChanging and ColumnChanged events for the specific column being changed, and then the RowChanging and RowChanged event. If multiple changes are being made to the row, the events will be raised for each change.

Note   The data row's BeginEdit method turns off the RowChanging and RowChanged events after each individual column change. In that case, the event is not raised until the EndEdit method has been called, when the RowChanging and RowChanged events are raised just once. For more information, see Suspending Update Constraints.
The event you choose depends on how granular you want the validation to be. If it is important that you catch an error immediately when a column is changed, build validation using the ColumnChanging event. Otherwise, use the RowChanging event, which might result in catching several errors at once. Additionally, if your data is structured in such a way that the value of one column is validated based on the contents of another column then you should perform your validation during the RowChanging event.


0
 

Author Comment

by:jamielaing
Comment Utility
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
0
 

Author Comment

by:jamielaing
Comment Utility
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
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now