inserting time stamp into database

[weijian]

Here'e the code:Dim objConn As New OleDbConnection(strDSN)
Dim objCmd As New OleDbCommand("nwsInsert", objConn)
objCmd.CommandType = CommandType.StoredProcedure

'get the input time
Dim dttDate As DateTime
dttDate = Date.Now.UtcNow

objParam = New OleDbParameter("@postedon", OleDbType.DBTimeStamp)
objParam.Value = dttDate
objCmd.Parameters.Add(objParam)

'execute the append
objCmd.Connection.Open()
objCmd.ExecuteNonQuery()
objCmd.Connection.Close()
objConn.Dispose()I'm trying to access an Access database, and the @postedon parameter is supposed to be inserted into a field of type Date/Time and the format is General Date. I tried various OleDbType - DBTimeStamp, DBDate, Date, DBTime, Char - all resulted in "System.Data.OleDb.OleDbException: Data type mismatch in criteria expression." What is the right way to add dates?

[pvb]

I created a table(myTable) with two columns('myId' as Numeric and 'myTimeStamp' as Date/Time). Created an action query(qryMyTable_Insert) that took two params to do the insert. Following code executes the query:

Public Sub timeStampTest()

Dim sConnString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\Progs\DotNet\adosql7test\test.mdb;"
Dim oleCn As OleDb.OleDbConnection
Dim sCmdText As String
Dim oleCmd As OleDb.OleDbCommand
Dim oleParam As OleDb.OleDbParameter
oleCn = New OleDb.OleDbConnection(sConnString)
Try
oleCn.Open()
sCmdText = "qryMyTable_Insert"
oleCmd = New OleDb.OleDbCommand(sCmdText, oleCn)
oleCmd.CommandType = CommandType.StoredProcedure

oleParam = New OleDb.OleDbParameter("@newId", OleDbType.Numeric)
oleParam.Value = 5
oleCmd.Parameters.Add(oleParam)

oleParam = New OleDb.OleDbParameter("@newTimeStamp", OleDbType.Date)
oleParam.Value = Date.Now()
oleCmd.Parameters.Add(oleParam)

oleCmd.ExecuteNonQuery()

Catch oleEx As OleDbException
MessageBox.Show(oleEx.Message.ToString())
Finally
oleCmd.Dispose()
oleCn.Close()
oleCn.Dispose()
End Try
MessageBox.Show("Done!")
End Sub

[weijian]

Which means your SQL statement is:

INSERT INTO table ( newID, newTimeStamp )
VALUES ( [@newID], [@newTimeStamp] )

Confusingly, i can add dates but not other values, like news title or news content. :confused: :confused: :confused: Which means:

INSERT INTO table ( theTimeStamp )
VALUES ( [@theTimeStamp] )

would work but

INSERT INTO table ( theTimeStamp, theTitle )
VALUES ( [@theTimeStamp], [@theTitle] )

won't work. Is there really a syntatical error here?

[pvb]

my query is the following:

INSERT INTO myTable ( myId, myTimeStamp )
VALUES ([newId], [newTimeStamp]);

...and again the 'myId' field is Numeric and the 'myTimeStamp' is Date/Time. I'm using MS Access 2000 format if that makes a difference. I haven't upgraded from Beta 2 yet so maybe if you're on the final release something's changed.

[weijian]

I used a loop to check the data type name using objReader.GetDataTypeName and found out that:

1) a field of type Text is DBTYPE_WVARCHAR, while
2) a field of type Memo is DBTYPE_WLONGVARCHAR.

So, I used OleDbType.VarWChar and OleDbType.LongVarWChar instead of OleDbType.Char. Still, it results in Data type mismatch in criteria expression.... what else could result in such an exception ???

[weijian]

I have finally solved the problem. The types are correct - but that is not the problem. The problem is somehow the order of adding parameters matter. That is, if your SQL statement is:INSERT INTO table (field1, field2) VALUES (@field1, @field2)the parameters addition must also follow the same order:Dim objParam as New OleDbParameter("@field1", OleDbType.Integer
objParam.Value = 0
objCmd.Parameters.Add(objParam)

objParam as New OleDbParameter("@field2", OleDbType.Char
objParam.Value = "testing"
objCmd.Parameters.Add(objParam)and this wouldn't work! :Dim objParam as New OleDbParameter("@field2", OleDbType.Char
objParam.Value = "testing"
objCmd.Parameters.Add(objParam)

objParam as New OleDbParameter("@field1", OleDbType.Integer
objParam.Value = 0
objCmd.Parameters.Add(objParam)I hope this helps for anyone stuck with the same problem that I had for 2 days!