OLEDB Insert Statement Syntax Error

[John_88]

I got some issues with Syntax error for my insert statement.

I can read access database without any issues.

I tried to put in all data as simple string but still not working.

Executing in Access works fine though.

Please help.

cmd.CommandText = "INSERT INTO POSITION (positionID,stockSymbol,stockName,cashTransaction,lastUpdate,initialQuantity,initialValue,currentQuantity,currentValue,positionStatus) VALUES ('2','2','2','2','2','2','2','2','2','2')"

cmd.ExecuteNonQuery()

[Reverend Jim]

Try

cmd.CommandText = "INSERT INTO POSITION " &
    "(positionID,stockSymbol,stockName,cashTransaction," &
    "lastUpdate,initialQuantity,initialValue,currentQuantity," &
    "currentValue,positionStatus)" &
    " VALUES (2,2,2,2,2,2,2,2,2,2)"

You don't need quotes around numeric values.

[mikeybware]

Your INSERT statement looks good, but you do have to wrap your values according to the data type you have it stored as in your database. For example:

cmd.CommandText = "INSERT INTO DATATABLE (ID,someString,someDate,someNumber) VALUES (2,'2',#11/23/2015#,16)"
cmd.ExecuteNonQuery()

A date value needs # around the value, a string needs single quotes ', and integers and IDs do not need the single quotes.

[Reverend Jim]

If you use Parameterized Queries then you don't have to worry about what delimiters to use.

[Begginnerdev]

Just an additional note: if using OleDB then your queries will need to have '?' instead of a paramter name.

For example:

cmd.CommandText="INSERT INTO tblMyTable (someString,someDate,someNumber) VALUES (?,?,?)"
cmd.Parameters.AddWithValue("@someString",MyStringValue)
cmd.Parameters.AddWithValue("@someDate",MyDateValue)
cmd.Parameters.AddWithValue("@someNumber",MyNumberValue)

cmd.ExecuteNonQuery()