DateTime problem with MS Access

[Thoth]

Hi, Im having trouble getting an Access database to read a DateTime... when I call System.DateTime.Now, and store the result of that into a table and Update... going into my database, I notice that it stores only the date, but not the time. It reads the time as 12:00 AM, which Im pretty sure means the same thing as just reading the date. Is there something Im overlooking? Im using Microsoft Jet 4.0 OLE DB provider; Im pretty sure thats the proper OleDB Connection to use... please help.

 

[wyrd]

DateTime.Now may just be returning the date (for some reason I doubt this though). Try explicitly telling it what format you want.

DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

http://msdn.microsoft.com/library/d...globalizationdatetimeformatinfoclasstopic.asp

 

[Thoth]

At run time, DateTime.Now is returning the time. I can also confirm that when I load that into my DataSet, the time information is still retained. It is when I execute Update that the time information is lost.

 

[wyrd]

Hmm. In that case, can you show us the code you use for your Update command? (if unable because of security reasons then provide us with a sample that includes dumbied values)

 

[Thoth]

I fixed the problem by switching to an ODBC connection. Odd that Jet 4.0 would mess up, but eh. If anyone wants to figure out why this happened, Id appreciate it.

 

[Thoth]

Actually, ODBC is being just awful with its update command. So Id like to switch back to Jet 4.0. Below is the code for my Update Command.

Me.OleDbUpdateCommand1.CommandText = "UPDATE tblShortLongBikeNames SET DateShortened = ?, DateUnshortened = ?, LongName" & _
" = ?, UnshortenedYesNo = ? WHERE (ShortName = ?) AND (DateShortened = ? OR ? IS " & _
"NULL AND DateShortened IS NULL) AND (DateUnshortened = ? OR ? IS NULL AND DateUn" & _
"shortened IS NULL) AND (LongName = ? OR ? IS NULL AND LongName IS NULL) AND (Uns" & _
"hortenedYesNo = ?)"
Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("DateShortened", System.Data.OleDb.OleDbType.DBDate, 0, "DateShortened"))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("DateUnshortened", System.Data.OleDb.OleDbType.DBDate, 0, "DateUnshortened"))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("LongName", System.Data.OleDb.OleDbType.VarWChar, 100, "LongName"))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("UnshortenedYesNo", System.Data.OleDb.OleDbType.Boolean, 2, "UnshortenedYesNo"))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_ShortName", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ShortName", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_DateShortened", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "DateShortened", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_DateShortened1", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "DateShortened", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_DateUnshortened", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "DateUnshortened", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_DateUnshortened1", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "DateUnshortened", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_LongName", System.Data.OleDb.OleDbType.VarWChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "LongName", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_LongName1", System.Data.OleDb.OleDbType.VarWChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "LongName", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_UnshortenedYesNo", System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "UnshortenedYesNo", System.Data.DataRowVersion.Original, Nothing))

 

[Thoth]

I noticed that DBDate wont transmit the time, so I want to switch it to a DBTimeStamp. But when I replace every occurence of DBDate with DBTimeStamp, It throws this:

System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

I dont think I can change my Access database to handle this, I can only set a field as a Date/Time.

 

[wyrd]

TimeStamp is not what you think. You should be using a DateTime column.

 

[Thoth]

I know what you mean, and I am using a Date/Time column in Access. However, instead of updating the data with a type System.Data.OleDb.OleDbType.DBDate, I use a System.Data.OleDb.OleDbType.DBTimeStamp, because DBDate only transmits yyyymmdd, and TimeStamp does yyyymmddhhmmss and I need those hours, minutes, and seconds.

 

[Thoth]

For clarifications sake, this is part of the OleDbType Enumeration:

DBTimeStamp: Data and time data in the format yyyymmddhhmmss (DBTYPE_DBTIMESTAMP). This maps to DateTime.

Basically, my question is this: If I want to change DBDate to DBTimeStamp, what else do I need to change besides my Update, Insert, and Delete statements?

 

[wyrd]

Hmm.. Im afraid perhaps Im confusing the issue. I use SQL Server and assumed that SqlDbType was similar to OleDbType. I guess not. In SqlDbType theres a DateTime, why OleDbType doesnt offer this just baffles me.

But now your problem at least makes sense. The reason it didnt store the time is because the OleDbType.DBDate only stores the date (no times).

Ergh.. anyway. I would try something like "#?#" in your command text, then use the parameter as a string to pass in the date and time, or forget the parameter mess altogether and just build the string dynamically. At the very least Id go back to using JET 4.0 since youre using access.

Im probably offering bad advice at this point... cant really offer much of anything else on the topic. Maybe the MSDN can shed some light on to things and help you out;

http://msdn.microsoft.com/library/d...l/frlrfSystemDataOleDbOleDbTypeClassTopic.asp

 

[Thoth]

Yeah, ODBC was working for a bit, it sent the time properly. But I guess I didnt pay attention when I was in the Data Adapter Wizard and it said it couldnt make an update function, because then it really wasnt working.

Another possible solution to this problem would be to use an ODBC connection, but somehow resolve this update problem.

 

[pirate]

Why dont you use something like this :

MessageBox.Show(DateTimePicker1.Value.Today + "  " + DateTimePicker1.Value.ToShortTimeString)

 

[Thoth]

A messagebox isnt good enoughl I need something that will store in an Access database.

 

[jfackler]

Make sure your Access Db Date/Time field is formated as a "General Date" not as a "Long Date". The "General Date" formated column will allow dd/mm/yyyy hh:mm:ss am/pm type of datetime format. All other date/time fields are date or time only formated. You may then have to do a datepart or a string manipulation to appropriately format your datum for entry into the Access DB.

 

[jfackler]

Originally posted by Thoth
I noticed that DBDate wont transmit the time, so I want to switch it to a DBTimeStamp. But when I replace every occurence of DBDate with DBTimeStamp, It throws this:

System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

I dont think I can change my Access database to handle this, I can only set a field as a Date/Time.

Clarification my previous post. In Design View, check the Data Type and assure it is Date/Time, then below, in the General tab, click Format. You should get a drop down arrow that will let you choose the format: Select General Date and try the code the way you were trying here.


Jon

 

[Thoth]

Sadly, it is selected as a General Date.

 

[jfackler]

Originally posted by Thoth
I noticed that DBDate wont transmit the time, so I want to switch it to a DBTimeStamp. But when I replace every occurence of DBDate with DBTimeStamp, It throws this:

System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

I dont think I can change my Access database to handle this, I can only set a field as a Date/Time.

Your AccessDB can handle a date/time in the format I showed you. Are you still trying to use TimeStamp? TimeStamp is supported in the SQL 99 standards. However, contrary to what you are expecting, it is like a combination of DATE and TIME and thus contains 4 digits for year and two each for month, date, hour, minute and second making a total of 14 digits in the form of a number. It would not be interpreted by your Accessdb as a date/time but as a number. It works as a unique number generator but I suspect it is not what youre looking for in your implementation.


Jon

 

[Thoth]

Well, I found the problem and fixed it. Instead of DBDate, I just used Date. Works perfectly.

I hate when problems have such easy solutions.

 

[Nerseus]

Funny... I *love* easy solutions

-Nerseus