MS Access?

[PLH]

MS Access? [RESOLVED] BY jmcilhinney

Hello all, I hope any one can help me with a little problem I have in my app. My app uses MS Access and I need to sort it by Date and Time. The app uses DataView. It seems that MS Access only stores the date even though I set the format property of data type as a "General Date" which should be in this "02/25/2005 5:45 PM" I don't know why but after saving a new order the date and time looks "02/25/2005" in the actual database. If MS Access date/Time data type stores only date or time what the format property does?

 

[jmcilhinney]

The Date/Time column type stores both the date and the time if it is provided with both. You need to use the OleDbType.DBTimeStamp type for your parameters when inserting into the database in order to save both date and time. I suggest taking a look at the OleDbType enumeration in the help to see exactly what each type means.

 

[PLH]

I thank you for your reply but can you give me an example. I add a row with customer info like this:

Dim drO As DataRow

drO = Ds1.Orders.NewRow

drO.Item("OrderID") = Format(Now, "MMddyyHHmmss")

drO.Item("CustomerID") = cmbNewImpClient.SelectedValue

drO.Item("Due_Date_Time") = dtpOrdNew.Value <- for example: #02/25/2005 5:25:00 PM#

daOrders.Update(Ds1, "Orders")

WHAT IS WRONG IN THIS CODE. WHY IT ONLY SAVES THE DATE?

 

[jmcilhinney]

Your InsertCommand has a Parameters collection. You need to find the parameter that relates to the "Due_Date_Time" column and change its Type property to DBTimeStamp. How have you created your DataAdapter? If it was in the designer then you need to go to the Properties window for the adapter and alter the parameter there. If it was in code, you would have used something like this:

daOrders.InsertCommand.Parameters.Add("@Due_Date_Time", OleDbType.DBTimeStamp, 0, "Due_Date_Time")

Your code may well be a bit different, but make sure that you are specifying DBTimeStamp as the Type. You should do the same thing for your UpdateCommand too if it affects that column.

 

[PLH]

Yes I created the DataAdapter in the designer. I changed the OleDbType to DBTimeStamp in the InsertCommand property. Now I get an exeption that says
"Data type mismatch in criterea expression." Should I do the same to the Update, Delete and Select comands. If yes then I noticed that there are two copyes of the same column. For example in the Delete comand's property under parameters there are
Due_Date_Time
Due_Date_Time1
columns. Should I change all of them?

 

[jmcilhinney]

Hmmm... this is tricky without seeing things for myself. Is this your own database or has it come from another source? I had a similar situation where dates had been already stored using OleDbType.Date, which also includes time. Perhaps you could try that type instead. If you already have data in the database that has been saved using another type then that may be the issue. Whatever type you end up using you need to use the same one for all parameters that relate to that column.

I seem to recall someone else having this same issue sometime ago, but I can't remember who or where or how they ended up resolving the issue. I think that they were using the Data Adapter Configuration Wizard and it seemed determined that they use DBDate. Is that how you created your adapter too?

 

[PLH]

Thanks I got it. Yes I did use Configuration Wizard and it uses DBDate. Now I changed it to OleDbType.Date and it saves the time too. It seems every thing is OK now. Thank you very much you save me a lot of time.

 

[jmcilhinney]

Excellent! Don't forget to mark your thread as resolved as per my signature.