DateTime problem with MS Access

[Thoth]

Hi, I'm 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 I'm pretty sure means the same thing as just reading the date. Is there something I'm overlooking? I'm using Microsoft Jet 4.0 OLE DB provider; I'm pretty sure that's 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/default.asp?url=/library/en-us/cpref/html/frlrfsystemglobalizationdatetimeformatinfoclasstopic.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, I'd appreciate it.

[Thoth]

Actually, ODBC is being just awful with its update command. So I'd 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 won't 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 don't think I can change my Access database to handle this, I can only set a field as a Date/Time.

Edited June 27, 2003 by Thoth

[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 clarification's 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.. I'm afraid perhaps I'm confusing the issue. I use SQL Server and assumed that SqlDbType was similar to OleDbType. I guess not. In SqlDbType there's a DateTime, why OleDbType doesn't offer this just baffles me.

 

But now your problem at least makes sense. The reason it didn't 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 I'd go back to using JET 4.0 since you're using access.

 

I'm probably offering bad advice at this point... can't 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/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbTypeClassTopic.asp

[Thoth]

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

 

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

[pirate]

Why don't you use something like this :

 

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

[Thoth]

A messagebox isn't 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]

I noticed that DBDate won't 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 don't 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]

I noticed that DBDate won't 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 don't 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 you're looking for in your implementation.

 

 

Jon

Edited June 28, 2003 by jfackler

[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

[Thoth]

I love them too... but see, the thing is, I thought the problem was this really deep, widespread thing. It took me two days just to isolate it. And then I was so stubborn about getting a DBTimeStamp to work I didn't look at my other options.