Using "OleDbParameter" with a DateTime type

[Guest]

I'm performing an Update query using an OleDbCommand. The SQL in that command
looks like...

UPDATE FeedbackT SET DateTime=?, Comment=?, PressOrg=? WHERE Type = 'UpdateMe'

When I try to populate the DataTime parameter using

P = new OleDbParameter("@DateTime", (Object)DateTime.Now);

I get a runtime error. Any idea what I might be doing wrong?

Alex

 

[Cor Ligthert [MVP]]

Alex,

We have at the moment a minor problem on our website. (Will be done at
something as gmt 12:00 probably), with linked pages. Therefore the sample in
this text.

This sample is to show how to use SQL Server parameters

Be aware that this shows it very simple.
For a next selection the parameter collection should be used.

--------------------------------------------------------------------------------

Dim cmd As New SQLCommand("SELECT * FROM MyTable WHERE MyDate BETWEEN
@BeginDate And @EndDate", Conn)
cmd.Parameters.Add("@BeginDate", cdate(txtBeginDate.text))
cmd.Parameters.Add("@EndDate", cdate(txtEndDate.text))

I hope this helps,

Cor

 

[Guest]

Cor -

Thanks but I'm still having a problem. Maybe this is because you are using a
SQLCommand and I'm using an OleDbCommand? Not sure, but whenever I try to use
any parameters which are DateTimes, even when running your exact example, I
get an "OleDb Exception - Date Type Mismatch in criteria expression". Below
is ALL of the code (pretty simple). Any ideas what I'm doing wrong?

//SELECT IN DATE RANGE...
SQL = "Select * from FeedbackT where SubmitDate BETWEEN @StartDate
and @EndDate";
DBC = new OleDbCommand(SQL, m_Conn);

P = new OleDbParameter("@StartDate",
(Object)(Convert.ToDateTime("5/1/2006")));
DBC.Parameters.Add(P);

P = new OleDbParameter("@EndDate", (Object)DateTime.Now);
DBC.Parameters.Add(P);

DBC.ExecuteReader();

 

[Guest]

OKAY, I've learned more - Turns out that I can make an OleDbParameter work
with an Access database DateTime column if I set the Parameter object "P" as
follows...

P.DbType = DbType.Date;

But the problem with doing this is that then only the Date portion (but not
the Time portion) of the value gets written to the Access database column. If
I set it to

P.DbType = DbType.DateTime;

I get a runtime Data Type Mismatch. There MUST be some way to fix this,
right???

Alex

 

[Cor Ligthert [MVP]]

Alex,

We had deployed our 2.0 version of the website and than got a strange
JavaScript error inside one of the controls, so we have to investigate.

The old one is back and with that the links
http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

You cannot write a date without the time part, that is a part of the
datetime field in Access.

I hope this helps,

Cor