OleDbParaneters & the DateTime Type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm writing data to an Access database using OleDb stuff. Everything works
fine except when I'm working with dates. Here's what happens:

If I write UPDATE SQL with an "@SomeDateTime" parameter and then I create an
OleDbParameter object and I set it's Name field to @SomeDateTime and it's
Value field to a value of type DateTime, and then finally, create an
OleDbCommand with the SQL and then do a Cmd.Parameters.Add() with my
@SomeDateTime Parameter object, I get a runtime error.

So someone told me to change the Parameter.DbType so that it is
"MyParam.DbType = DbType.Date;". When I do this, the error goes away. BUT
HERE's THE PROBLEM: When I do this, the date that is written to my database
is only the DATE portion without the TIME!

How do I get the full Date-Time to be stored???

Thanks.

Alex
 
¤ I'm writing data to an Access database using OleDb stuff. Everything works
¤ fine except when I'm working with dates. Here's what happens:
¤
¤ If I write UPDATE SQL with an "@SomeDateTime" parameter and then I create an
¤ OleDbParameter object and I set it's Name field to @SomeDateTime and it's
¤ Value field to a value of type DateTime, and then finally, create an
¤ OleDbCommand with the SQL and then do a Cmd.Parameters.Add() with my
¤ @SomeDateTime Parameter object, I get a runtime error.
¤
¤ So someone told me to change the Parameter.DbType so that it is
¤ "MyParam.DbType = DbType.Date;". When I do this, the error goes away. BUT
¤ HERE's THE PROBLEM: When I do this, the date that is written to my database
¤ is only the DATE portion without the TIME!
¤
¤ How do I get the full Date-Time to be stored???
¤

Does the value you're assigning to the parameter have both a date and time? Could you post an
example?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Paul -

Here's a sample. When I execute this and then go look in the Access table,
only the DATE has been stored. The Time portion is stripped.

SQL = "UPDATE FeedbackT SET PressOrg=@PressOrg, -->
submitDate=@SubmitDate WHERE Type = 'XXX'";
DBC = new OleDbCommand(SQL, m_Conn);

ParamValStr = "New York Times";
P = new OleDbParameter("@PressOrg", (Object)ParamValStr);
DBC.Parameters.Add(P);

//I use DateTime.Now for the value which should have a Time portion...
P = new OleDbParameter("@SubmitDate", (Object)DateTime.Now);
P.DbType = DbType.Date;
DBC.Parameters.Add(P);

DBC.ExecuteNonQuery();

Thanks.

Alex
=======================
 
Hi Alex,

Have you tried to use DbType.DateTime as parameter type and also set
MyParam.Size to 8?

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Kevin -

Hi. I tried what you said:
P = new OleDbParameter("@SubmitDate", (Object)DateTime.Now);
P.DbType = DbType.DateTime;
P.Size = 8;
DBC.Parameters.Add(P);

and I get the following runtime error...
"Data type mismatch in criteria expression"

In MS Access, the column I'm writing to is of type "Date/Time." So what am I
doing wrong???

Alex
 
Maybe it's related but when i update using the command object i had to pass
..ToOaDate() double value.
That works fine.
 
YOU ROCK! Yes, that did the job!

Edwin Knoppert said:
Maybe it's related but when i update using the command object i had to pass
..ToOaDate() double value.
That works fine.
Click to expand...
 
Back
Top