Syntax error in 'INSERT INTO' statement VB.NET ACCESS
I'm trying to insert a record into a database using VB.NET using this line but I am given a syntax error? I don't see anything wrong with it I think?
SQL = ("INSERT INTO Orders (Items, CustName, Table, Cost, Price)
VALUES ('" & ItemsString & "', '" & CustName & "', '" & Table &
"', '" & Cost & "', '" & Price & "');")
I am not sure what the parentheses outside the string are for but they are not necessary. Always use parameters. Never concatenate strings to build CommandText
.
Please note that TABLE is a reserved word. Enclosed in brackets.
I had to guess the datatypes for the parameters. Check your database for correct values. Money should be Decimal.
This is what a parametrized query should look like.
Private Sub InsertRecord(ItemsString As String, CustName As String, Table As String, Cost As Decimal, Price As Decimal)
Dim SQL = "INSERT INTO Orders (Items, CustName, [Table], Cost, Price) VALUES (@ItemsString, @CustName, @Table, @Cost, @Price);"
Using cn As New OleDbConnection("Your conneciton string"),
cmd As New OleDbCommand(SQL, cn)
cmd.Parameters.Add("@ItemsString", OleDbType.VarChar).Value = ItemsString
cmd.Parameters.Add("@CustName", OleDbType.VarChar).Value = CustName
cmd.Parameters.Add("@Table", OleDbType.VarChar).Value = Table
cmd.Parameters.Add("@Cost", OleDbType.Decimal).Value = Cost
cmd.Parameters.Add("@Price", OleDbType.Decimal).Value = Price
cn.Open()
cmd.ExecuteNonQuery()
End Using
End Sub