I am having an Insert into error with my query in vb.net

Question

I am trying to insert data into an Access database table using an insert query, I get the error Syntax error in INSERT INTO statement.

I have even tried the sql string without parameters and get the same error but if I paste the same query straight into Access it works.

Dim sqlinsert As String
'sqlinsert = "INSERT INTO JobApplicants(Title, Address, Postcode, Phone, Email, Position, Education) VALUES(@Title, @Address, @Postcode, @Phone, @Email, @Position, @Education)"
' sqlinsert = "INSERT INTO JobApplicants(Title, ApplicantName,Address, Postcode, Phone, Email, Position, Education) VALUES('" & comboTitle.Text.ToString & "','" & txtApplicantName.Text & "','" & txtAddress.Text & "','" & txtPostcode.Text & "','" & txtPhone.Text & "','" & txtEmail.Text & "','" & comboPosition.Text & "','" & comboEducation.Text & "')"
sqlinsert = "INSERT INTO JobApplicants(Title, ApplicantName,Address, Postcode, Phone, Email, Position, Education) VALUES('Mr','freed','12 high st','sa123er','01234567890','a@b.c','head','gcse')"
Dim cmd As New OleDbCommand(sqlinsert, con1)

cmd.Parameters.Add(New OleDbParameter("@Title", comboTitle.Text))
cmd.Parameters.Add(New OleDbParameter("@Address", txtAddress.Text))
cmd.Parameters.Add(New OleDbParameter("@Postcode", txtPostcode.Text))
cmd.Parameters.Add(New OleDbParameter("@Phone", txtPhone.Text))
cmd.Parameters.Add(New OleDbParameter("@Email", txtEmail.Text))
cmd.Parameters.Add(New OleDbParameter("@Position", comboPosition.Text))
cmd.Parameters.Add(New OleDbParameter("@Education", comboEducation.Text))

con1.Open()
cmd.ExecuteNonQuery()
con1.Close()

Tim Biegeleisen · Answer 1 · 2019-07-05T01:09:20.097

You are binding 7 parameters to a prepared statement which doesn't actually have any placeholders. The insert statement, as raw SQL, happens to be valid, but I suspect binding parameters which do not exist in the statement is the source of the error. So, try using placeholders in the prepared statement:

sqlinsert = "INSERT INTO JobApplicants (Title, ApplicantName,Address, Postcode, Phone, Email, [Position], Education) VALUES (@Title, @ApplicantName, @Address, @Postcode, @Phone, @Email, @Position, @Education)"
Dim cmd As New OleDbCommand(sqlinsert, con1)

cmd.Parameters.Add(New OleDbParameter("@Title", comboTitle.Text))
cmd.Parameters.Add(New OleDbParameter("@ApplicantName", txtApplicant.Text))
cmd.Parameters.Add(New OleDbParameter("@Address", txtAddress.Text))
cmd.Parameters.Add(New OleDbParameter("@Postcode", txtPostcode.Text))
cmd.Parameters.Add(New OleDbParameter("@Phone", txtPhone.Text))
cmd.Parameters.Add(New OleDbParameter("@Email", txtEmail.Text))
cmd.Parameters.Add(New OleDbParameter("@Position", comboPosition.Text))
cmd.Parameters.Add(New OleDbParameter("@Education", comboEducation.Text))

con1.Open()
cmd.ExecuteNonQuery()
con1.Close()

Note that you don't actually bind a value for the applicant's name. Therefore, I am assuming that there is a text box somewhere called txtApplicant, whose text property we can access to get that value.

Sorry I edited out lots of code to show just the bare bones of the issue and accidently removed "cmd.Parameters.Add(New OleDbParameter("@ApplicantName", txtApplicantName.Text))" so that was not the issue, as described below it was position being a reserved word, once I put it in square brackets everything was good. — quell2727, Jul 04 '19 at 21:17

MatSnow · Accepted Answer · 2019-07-04T13:52:30.407

1

POSITION is a reserved word in Access.

So you have to use square brackets in the query.

"INSERT INTO JobApplicants(Title, ApplicantName, Address, Postcode, Phone, Email, [Position], ..."

edited Jul 04 '19 at 13:52

answered Jul 04 '19 at 13:45

MatSnow

7,357
3
19
31

Thank you so much it was driving me crazy. So simple when you know. – quell2727 Jul 04 '19 at 21:19

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

use bracket

Dim sqlquery As String = "INSERT INTO MCAscheduled **([URno],[SName],[hsc],[gper],[pgper],[pstatus],[cname],[hrname],[position],[hscinter],[ginter],[pginter],[comments])"** + "VALUES (" & CInt(txtUrn.Text) & ",'" & txtName.Text & "'," & CInt(txt12Per.Text) & "," & CInt(txtGPer.Text) & "," & CInt(TextBox1.Text) & ",'" & ComboBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & ComboBox4.Text & "'," & CInt(TextBox12.Text) & "," & CInt(TextBox11.Text) & "," & CInt(TextBox10.Text) & ",'" & TextBox9.Text & "');"

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 04 '19 at 13:43

Ayush Singh

55
3

No! Always use Parameters to avoid Sql Injection. Never concatenate strings. Suppose a user enters ";Drop Table MCAscheduled;" into one of those text boxes? – Mary Jul 05 '19 at 03:29

I am having an Insert into error with my query in vb.net

3 Answers3

use bracket