Adding TimeStamp

[trims30]

Have Access Database and "TimeStamp" field defined as Date/Time Data Type.

In my VB.Net Code I get error on ExecuteNonQuery saying that "Syntax error in INSERT INTO statement."
If I remove the code relating to TimeStamp program runs without error.

I have read that there's some problem with Access Database and OleDbType.Date but can't figure out whats wrong. Need to store Date and Time in Database as 03/16/2010 12:00:00 AM

Any Suggestions??

Lee

StrSql = "Insert into Equipment ( TimeStamp, Ident, Descr, Serial, Cost)" & _
" Values (@TimeStamp, @Ident, @Descr, @Serial, @Cost)"
oCmd = New OleDbCommand(StrSql, oConn)

oCmd.Parameters.Add(New OleDbParameter("@TimeStamp", OleDbType.Date))
oCmd.Parameters.Add(New OleDbParameter("@Ident", OleDbType.VarChar, 12))
oCmd.Parameters.Add(New OleDbParameter("@Descr", OleDbType.VarChar, 40))
oCmd.Parameters.Add(New OleDbParameter("@Serial", OleDbType.VarChar, 20))
oCmd.Parameters.Add(New OleDbParameter("@Cost", OleDbType.Double))

oCmd.Parameters("@TimeStamp").Value = Now
oCmd.Parameters("@Ident").Value = txtIdent.Text
oCmd.Parameters("@Descr").Value = txtDescr.Text
oCmd.Parameters("@Serial").Value = txtSerial.Text
oCmd.Parameters("@Cost").Value = txtCost.Text

oCmd.ExecuteNonQuery()

 

[InertiaM]

Perhaps TimeStamp is a reserved word, and should not have be used for a field name?

 

[r3plica]

it is but you can always use [TimeStamp] when creating your insert statement.

i.e.

INSERT INTO Equipment
	([TimeStamp], Ident, Descr, Serial, Cost)
Values 
	(@TimeStamp, @Ident, @Descr, @Serial, @Cost)

if you ever get syntax errors, copy the statement and paste it into sql manager and try to run it.
If any of the text is a different color (in this case blue) it means it is a reserved word and no field should be using a reserved word.

 

[JuggaloBrotha]

trims30, you should see about using a DataSet (*.xsd) file for this, that way you have a visual designer and wizards to create the tables and multiple queries in the TableAdapter. Then to use it, it's as simple as

Dim DA As New DataSetNameTableAdapters.TableNameTableAdapter
DA.NameOfQuery(DataSet/DataTable, Param1, Param2, etc)

It'll put the ' around the field names (to avoid using a keyword) and it'll use parametrized queries automatically.

 

[trims30]

Thanks for the help - I'm just doing some practice work with parameters trying to learn basics of ADO.NET.

I changed column name TimeStamp to TimeStampCol in my SQL and Database to solve the problem.

Lee