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. 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!