Add DateTime.Now to ACCESS 2007 thru stored procedure [modified]
-
For your information: I've been struggling the whole morning trying to update a table in an Access 2007 database with a C# application. I'm using a simple Stored procedure:
UPDATE version SET version.installdate = @sinstalldate, version.lastrundif = @ilastrundif
WHERE [version].[version] = @version;The column version.installdate is a standard Date/Time column. The parameters are filled the usual way, but Access kept throwing errors my way (wrong datatype). After struggling for a long time and I finaly found the problem: I used the next line of code to fill the DateTime parameter:
parameter.Value = DateTime.Now;This fills the parameter with <12-17-2008 13:36:52**:332**> (including miliseconds). Access doesn't seem to support this! Instead now I used the following code:DateTime dt = Convert.ToDateTime(DateTime.Now.ToString()); parameter.Value = dt ;Now the parameter gets filled with <12-17-2008 13:36:52> and this works fine! That's all, Happy coding!modified on Wednesday, December 17, 2008 7:48 AM
-
For your information: I've been struggling the whole morning trying to update a table in an Access 2007 database with a C# application. I'm using a simple Stored procedure:
UPDATE version SET version.installdate = @sinstalldate, version.lastrundif = @ilastrundif
WHERE [version].[version] = @version;The column version.installdate is a standard Date/Time column. The parameters are filled the usual way, but Access kept throwing errors my way (wrong datatype). After struggling for a long time and I finaly found the problem: I used the next line of code to fill the DateTime parameter:
parameter.Value = DateTime.Now;This fills the parameter with <12-17-2008 13:36:52**:332**> (including miliseconds). Access doesn't seem to support this! Instead now I used the following code:DateTime dt = Convert.ToDateTime(DateTime.Now.ToString()); parameter.Value = dt ;Now the parameter gets filled with <12-17-2008 13:36:52> and this works fine! That's all, Happy coding!modified on Wednesday, December 17, 2008 7:48 AM
Since Access uses Ole automation dates, the correct thing to do would likely have been to use DateTime.Now.ToOADate.
-
Since Access uses Ole automation dates, the correct thing to do would likely have been to use DateTime.Now.ToOADate.
Unfortunately DateTime.Now.ToOADate() is also a conversion, witch in this case returns a double. The parameter for the Stored procedure requires a OleDbType.DBTimeStamp:
parameter = objCommand.Parameters.Add("@sinstalldate", OleDbType.DBTimeStamp); parameter.Value = dtFirstInstall;So I stil have to do a Convert.ToDateTime(double) to re-create a DateTime object... -
Unfortunately DateTime.Now.ToOADate() is also a conversion, witch in this case returns a double. The parameter for the Stored procedure requires a OleDbType.DBTimeStamp:
parameter = objCommand.Parameters.Add("@sinstalldate", OleDbType.DBTimeStamp); parameter.Value = dtFirstInstall;So I stil have to do a Convert.ToDateTime(double) to re-create a DateTime object...I learn something new daily. In this case it's just one more reason why I quit using Microsoft Access for anything at all. It's just too broken.
-
For your information: I've been struggling the whole morning trying to update a table in an Access 2007 database with a C# application. I'm using a simple Stored procedure:
UPDATE version SET version.installdate = @sinstalldate, version.lastrundif = @ilastrundif
WHERE [version].[version] = @version;The column version.installdate is a standard Date/Time column. The parameters are filled the usual way, but Access kept throwing errors my way (wrong datatype). After struggling for a long time and I finaly found the problem: I used the next line of code to fill the DateTime parameter:
parameter.Value = DateTime.Now;This fills the parameter with <12-17-2008 13:36:52**:332**> (including miliseconds). Access doesn't seem to support this! Instead now I used the following code:DateTime dt = Convert.ToDateTime(DateTime.Now.ToString()); parameter.Value = dt ;Now the parameter gets filled with <12-17-2008 13:36:52> and this works fine! That's all, Happy coding!modified on Wednesday, December 17, 2008 7:48 AM
Converting to string and immediately parsing, especially back to the same type, is a coding horror. Did you try subtracting the milliseconds?
d.AddMilliseconds ( d.Millisecond * -1 ) -
Converting to string and immediately parsing, especially back to the same type, is a coding horror. Did you try subtracting the milliseconds?
d.AddMilliseconds ( d.Millisecond * -1 )Yes, I tried that also. But in that case I'm still left with :000 at the end and Access just won't accept it... And I know it's an absolute coding horror, but I have not found a way around it yet. So if anyone has any idea...
-
For your information: I've been struggling the whole morning trying to update a table in an Access 2007 database with a C# application. I'm using a simple Stored procedure:
UPDATE version SET version.installdate = @sinstalldate, version.lastrundif = @ilastrundif
WHERE [version].[version] = @version;The column version.installdate is a standard Date/Time column. The parameters are filled the usual way, but Access kept throwing errors my way (wrong datatype). After struggling for a long time and I finaly found the problem: I used the next line of code to fill the DateTime parameter:
parameter.Value = DateTime.Now;This fills the parameter with <12-17-2008 13:36:52**:332**> (including miliseconds). Access doesn't seem to support this! Instead now I used the following code:DateTime dt = Convert.ToDateTime(DateTime.Now.ToString()); parameter.Value = dt ;Now the parameter gets filled with <12-17-2008 13:36:52> and this works fine! That's all, Happy coding!modified on Wednesday, December 17, 2008 7:48 AM
How is this subtle? If you transfer data between two different languages or environments then you must check that data types are stored in the same way. If they are not you need to provide code that transforms one representation to the other. This is basic to programming. Consider, for example, sending binary integers from a big-endian to a little-endian system. If you assume that all systems have the same endianess you're in for as surprise.