Insert date time into database

Question

I'm using OleDbCommand to insert a Date/Time field using parameters, and getting an exception stating that "Data type mismatch in criteria expression."

I've identified the parameter causing my issue, and executing the following code causes my error:

insertCmd.CommandText = "INSERT INTO myTable ([ParameterName]) VALUES (?)"
insertCmd.Parameters.AddWithValue("@ParameterName", Now)
insertCmd.Connection = _connection
return insertCmd.ExecuteNonQuery()

Checking out the parameter that's causing the issue, I get the following for parameter {@ParameterName}

ChangeID: 1
DbType: DateTime {6}
Direction: Input {1}
IsNullable: False
Offset: 0
OleDbType: DBTimeStamp {135}
ParameterName: "@ParameterName"
Precision: 0
PrecisionInternal: 0
Scale: 0
ScaleInternal: 0
Size: 0
SourceColumn: ""
SourceColumnNullMapping: False
SourceVersion: Current {512}
Value: #10/9/2014 11:26:15 AM# {Date}

It appears to be recognized as DbType DateTime and the Object inserted into the parameters is a Date object, but It still is telling me that there is a problem with the data type.

Most of the places I've seen this issue have been trying to write a string into a Date/Time field, which I am definitely not doing.

you should be formatting it, for better compatibility, as yyyy-MM-dd — Ahmed ilyas
are you using OleDB with SQL server maybe? what is the actual database column data type? — Ňɏssa Pøngjǣrdenlarp
My best guess is that your db table column is not defined as datetime. — Bjørn-Roger Kringsjå
I'm using OleDb and my field Data Type is Date/Time in design view. — Andrew Miller

LarsTech LarsTech · Accepted Answer · 2014-10-09T15:58:42

Try using just the Date value since it appears that your database field is a date value only (I'm guessing):

insertCmd.Parameters.AddWithValue("@ParameterName", Now.Date)

or it could be that OleDB is just confused by the type, so set it yourself:

Dim p As New OleDbParameter("@parameterName", OleDbType.Date)
p.Value = Now
insertCmd.Parameters.Add(p)

Insert date time into database

1 Answers