DateTime DataType Mismatch

  • Thread starter Thread starter rn5a
  • Start date Start date
R

rn5a

I am inserting records in a MS-Access database table. The data type of
one of the columns named *OrderDate* in the DB table is Date/Time.
This is the SQL query I am using to insert the records in the Access
DB table:

===============================
strSQL = "INSERT INTO Cart (CartID, ProductID, Quantity, Total,
OrderDate) VALUES (CID, PID, Qty, TotalAmt, ODate)"

oleDbCmd = New OleDbCommand(strSQL, oleDbConn)

With oleDbCmd
.Parameters.AddWithValue("CID", OleDbType.VarChar).Value =
strCartID
.Parameters.AddWithValue("PID", OleDbType.VarChar).Value =
strProductID
.Parameters.AddWithValue("Qty", OleDbType.Integer).Value = iQty
.Parameters.AddWithValue("TotalAmt", OleDbType.Currency).Value =
CInt(iPrice) * CInt(iQty)
.Parameters.AddWithValue("ODate", OleDbType.DBDate).Value =
DateTime.Now
End With
===============================

I want the *OrderDate* column in the Access DB table to have both the
date & the time. The problem is there is no support for a combination
of both date & time in OLEDB. The datatypes supported allow EITHER
date OR time but not both. Hence the above code generates the
following error:

Data type mismatch in criteria expression.

since DateTime.Now gets both the date & the time. If I replace
DateTime.Now in the above code with DateTime.Now.Date, then the above
SQL query executes successfully & inserts records in the DB table but
it adds only the date part in the *OrderDate* column & omits the time
part whereas I want the records under the *OrderDate* column in the DB
table to have both the date as well as the time. Changing the data
type of the last parameter in the above code from OleDbType.DBDate to
OleDbType.Date also doesn't make any difference. The error still
persists.

How do I resolve this issue?

One workaround is to add a new Date/Time data type column in the DB
table & pass another parameter from the ASP.NET code to insert only
the time part but that's a last option if there isn't any other
workaround.
 
Howdy,

I think dbdate is ok. First, you've missedused AddWithValue function which
expects parameter name and it's value (but you are passing DbType enumeration
as value).Second i suspect there is a problem with insert query at
'values(blabla)'. I might be wrong but this form of passing parameters is not
supported by jet provider (ms access) I think this should go like following:

strSQL = "INSERT INTO Cart (CartID, ProductID, Quantity, Total,
OrderDate) VALUES (?, ?, ?, ?, ?)"

oleDbCmd = New OleDbCommand(strSQL, oleDbConn)

With oleDbCmd.Parameters
.Add("?", OleDbType.VarChar).Value = strCartID
.Add("?", OleDbType.VarChar).Value = strProductID
.Add("?", OleDbType.Integer).Value = iQty
.Add("?", OleDbType.Currency).Value = CInt(iPrice) * CInt(iQty)
.Add("?", OleDbType.DBDate).Value = DateTime.Now
End With

oledbCmd.ExecuteNonQuery()

Aboive snippet should work. By the way, I'm not sure about the logic you
calculate total price as curency:
CInt(iPrice) * CInt(iQty)
which will always return integer.

Hope this helps
 
Howdy,

I think dbdate is ok. First, you've missedused AddWithValue function which
expects parameter name and it's value (but you are passing DbType enumeration
as value).Second i suspect there is a problem with insert query at
'values(blabla)'. I might be wrong but this form of passing parameters is not
supported by jet provider (ms access) I think this should go like following:

strSQL = "INSERT INTO Cart (CartID, ProductID, Quantity, Total,
OrderDate) VALUES (?, ?, ?, ?, ?)"

oleDbCmd = New OleDbCommand(strSQL, oleDbConn)

With oleDbCmd.Parameters
.Add("?", OleDbType.VarChar).Value = strCartID
.Add("?", OleDbType.VarChar).Value = strProductID
.Add("?", OleDbType.Integer).Value = iQty
.Add("?", OleDbType.Currency).Value = CInt(iPrice) * CInt(iQty)
.Add("?", OleDbType.DBDate).Value = DateTime.Now
End With

oledbCmd.ExecuteNonQuery()

Aboive snippet should work. By the way, I'm not sure about the logic you
calculate total price as curency:
CInt(iPrice) * CInt(iQty)
which will always return integer.

Hope this helps
--
Milosz













- Show quoted text -
Click to expand...

Milosz, if I insert records as you have suggested (using '?'), then it
doesn't generate any error & also inserts the record in the DB table
successfully but still only the date part gets inserted & not the time
part in the *OrderDate* column. For e.g. if the current date & time
now is

24/02/2007 6:56:34 PM

then only

24/02/2007 gets inserted in the *OrderDate* column; the time part gets
omitted.

Any other suggestions?
 
I think both date and time are inserted, but the access shows only date part,
to make sure, run this query

select Format(OrderDate, "YYYY/DD/MM hh:mm:ss") from Cart
 
I think both date and time are inserted, but the access shows only date part,
to make sure, run this query

select Format(OrderDate, "YYYY/DD/MM hh:mm:ss") from Cart
--
Milosz










- Show quoted text -
Click to expand...

When I executed your query, Milosz, the record under the *OrderDate*
column showed the date part but it showed the time part as 00:00:00
like for e.g.

24/02/2007 00:00:00

What next??
 
I think both date and time are inserted, but the access shows only date part,
to make sure, run this query
Click to expand...
select Format(OrderDate, "YYYY/DD/MM hh:mm:ss") from Cart
Click to expand...
[email protected] said:
On Feb 24, 10:25 pm, Milosz Skalecki [MCAD]
Howdy,
I think dbdate is ok. First, you've missedused AddWithValue function which
expects parameter name and it's value (but you are passing DbType enumeration
as value).Second i suspect there is a problem with insert query at
'values(blabla)'. I might be wrong but this form of passing parameters is not
supported by jet provider (ms access) I think this should go like following:
strSQL = "INSERT INTO Cart (CartID, ProductID, Quantity, Total,
OrderDate) VALUES (?, ?, ?, ?, ?)"
oleDbCmd = New OleDbCommand(strSQL, oleDbConn)
With oleDbCmd.Parameters
.Add("?", OleDbType.VarChar).Value = strCartID
.Add("?", OleDbType.VarChar).Value = strProductID
.Add("?", OleDbType.Integer).Value = iQty
.Add("?", OleDbType.Currency).Value = CInt(iPrice) * CInt(iQty)
.Add("?", OleDbType.DBDate).Value = DateTime.Now
End With
oledbCmd.ExecuteNonQuery()
Aboive snippet should work. By the way, I'm not sure about the logic you
calculate total price as curency:
CInt(iPrice) * CInt(iQty)
which will always return integer.
Hope this helps
--
Milosz
:
I am inserting records in a MS-Access database table. The data type of
one of the columns named *OrderDate* in the DB table is Date/Time.
This is the SQL query I am using to insert the records in the Access
DB table:
===============================
strSQL = "INSERT INTO Cart (CartID, ProductID, Quantity, Total,
OrderDate) VALUES (CID, PID, Qty, TotalAmt, ODate)"
oleDbCmd = New OleDbCommand(strSQL, oleDbConn)
With oleDbCmd
.Parameters.AddWithValue("CID", OleDbType.VarChar).Value =
strCartID
.Parameters.AddWithValue("PID", OleDbType.VarChar).Value =
strProductID
.Parameters.AddWithValue("Qty", OleDbType.Integer).Value = iQty
.Parameters.AddWithValue("TotalAmt", OleDbType.Currency).Value =
CInt(iPrice) * CInt(iQty)
.Parameters.AddWithValue("ODate", OleDbType.DBDate).Value =
DateTime.Now
End With
===============================
I want the *OrderDate* column in the Access DB table to have both the
date & the time. The problem is there is no support for a combination
of both date & time in OLEDB. The datatypes supported allow EITHER
date OR time but not both. Hence the above code generates the
following error:
Data type mismatch in criteria expression.
since DateTime.Now gets both the date & the time. If I replace
DateTime.Now in the above code with DateTime.Now.Date, then the above
SQL query executes successfully & inserts records in the DB table but
it adds only the date part in the *OrderDate* column & omits the time
part whereas I want the records under the *OrderDate* column in the DB
table to have both the date as well as the time. Changing the data
type of the last parameter in the above code from OleDbType.DBDate to
OleDbType.Date also doesn't make any difference. The error still
persists.
How do I resolve this issue?
One workaround is to add a new Date/Time data type column in the DB
table & pass another parameter from the ASP.NET code to insert only
the time part but that's a last option if there isn't any other
workaround.- Hide quoted text -
- Show quoted text -
Milosz, if I insert records as you have suggested (using '?'), then it
doesn't generate any error & also inserts the record in the DB table
successfully but still only the date part gets inserted & not the time
part in the *OrderDate* column. For e.g. if the current date & time
now is
24/02/2007 6:56:34 PM
then only
24/02/2007 gets inserted in the *OrderDate* column; the time part gets
omitted.
Any other suggestions?- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

When I executed your query, Milosz, the record under the *OrderDate*
column showed the date part but it showed the time part as 00:00:00
like for e.g.

24/02/2007 00:00:00

What next??- Hide quoted text -

- Show quoted text -
Click to expand...

Milosz, the following inserts both date & time in the Access DB
column:

..Parameters.AddWithValue("?", DateTime.Now.ToString)

Even the obsolete Add function can be used like this:

..Parameters.Add("?", DbType.DateTime).Value = DateTime.Now.ToString

Strangely, in both cases, omitting the ToString function generates the

Data type mismatch in criteria expression.

error though the column in the Access DB table is of type Date/Time!

Any idea why?
 
Howdy,

Maybe something else generates mentioned mismatch exception. Could you give
me all the columns names with type please?
--
Milosz


[email protected] said:
I think both date and time are inserted, but the access shows only date part,
to make sure, run this query
Click to expand...
select Format(OrderDate, "YYYY/DD/MM hh:mm:ss") from Cart
Click to expand...
:
On Feb 24, 10:25 pm, Milosz Skalecki [MCAD]
Howdy,
Click to expand...
I think dbdate is ok. First, you've missedused AddWithValue function which
expects parameter name and it's value (but you are passing DbType enumeration
as value).Second i suspect there is a problem with insert query at
'values(blabla)'. I might be wrong but this form of passing parameters is not
supported by jet provider (ms access) I think this should go like following:
Click to expand...
strSQL = "INSERT INTO Cart (CartID, ProductID, Quantity, Total,
OrderDate) VALUES (?, ?, ?, ?, ?)"
Click to expand...
oleDbCmd = New OleDbCommand(strSQL, oleDbConn)
Click to expand...
With oleDbCmd.Parameters
.Add("?", OleDbType.VarChar).Value = strCartID
.Add("?", OleDbType.VarChar).Value = strProductID
.Add("?", OleDbType.Integer).Value = iQty
.Add("?", OleDbType.Currency).Value = CInt(iPrice) * CInt(iQty)
.Add("?", OleDbType.DBDate).Value = DateTime.Now
End With

Aboive snippet should work. By the way, I'm not sure about the logic you
calculate total price as curency:
CInt(iPrice) * CInt(iQty)
which will always return integer.
Click to expand...
Hope this helps
Click to expand...
:
I am inserting records in a MS-Access database table. The data type of
one of the columns named *OrderDate* in the DB table is Date/Time.
This is the SQL query I am using to insert the records in the Access
DB table:
Click to expand...
===============================
strSQL = "INSERT INTO Cart (CartID, ProductID, Quantity, Total,
OrderDate) VALUES (CID, PID, Qty, TotalAmt, ODate)"
Click to expand...
oleDbCmd = New OleDbCommand(strSQL, oleDbConn)
Click to expand...
With oleDbCmd
.Parameters.AddWithValue("CID", OleDbType.VarChar).Value =
strCartID
.Parameters.AddWithValue("PID", OleDbType.VarChar).Value =
strProductID
.Parameters.AddWithValue("Qty", OleDbType.Integer).Value = iQty
.Parameters.AddWithValue("TotalAmt", OleDbType.Currency).Value =
CInt(iPrice) * CInt(iQty)
.Parameters.AddWithValue("ODate", OleDbType.DBDate).Value =
DateTime.Now
End With
===============================
Click to expand...
I want the *OrderDate* column in the Access DB table to have both the
date & the time. The problem is there is no support for a combination
of both date & time in OLEDB. The datatypes supported allow EITHER
date OR time but not both. Hence the above code generates the
following error:
Click to expand...
Data type mismatch in criteria expression.
Click to expand...
since DateTime.Now gets both the date & the time. If I replace
DateTime.Now in the above code with DateTime.Now.Date, then the above
SQL query executes successfully & inserts records in the DB table but
it adds only the date part in the *OrderDate* column & omits the time
part whereas I want the records under the *OrderDate* column in the DB
table to have both the date as well as the time. Changing the data
type of the last parameter in the above code from OleDbType.DBDate to
OleDbType.Date also doesn't make any difference. The error still
persists.
Click to expand...
How do I resolve this issue?
Click to expand...
One workaround is to add a new Date/Time data type column in the DB
table & pass another parameter from the ASP.NET code to insert only
the time part but that's a last option if there isn't any other
workaround.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Milosz, if I insert records as you have suggested (using '?'), then it
doesn't generate any error & also inserts the record in the DB table
successfully but still only the date part gets inserted & not the time
part in the *OrderDate* column. For e.g. if the current date & time
now is
Click to expand...
24/02/2007 6:56:34 PM
Click to expand...
then only
Click to expand...
24/02/2007 gets inserted in the *OrderDate* column; the time part gets
omitted.
Click to expand...
Any other suggestions?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

When I executed your query, Milosz, the record under the *OrderDate*
column showed the date part but it showed the time part as 00:00:00
like for e.g.

24/02/2007 00:00:00

What next??- Hide quoted text -

- Show quoted text -
Click to expand...

Milosz, the following inserts both date & time in the Access DB
column:

..Parameters.AddWithValue("?", DateTime.Now.ToString)

Even the obsolete Add function can be used like this:

..Parameters.Add("?", DbType.DateTime).Value = DateTime.Now.ToString

Strangely, in both cases, omitting the ToString function generates the

Data type mismatch in criteria expression.

error though the column in the Access DB table is of type Date/Time!

Any idea why?
Click to expand...
 
Back
Top