CODEWITHSUNDEEP
vb.netms-accesssql-insertexecutenonquery
quell2727
quell2727

Reputation: 13

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

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','[email protected]','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()

Upvotes: 1

Views: 57

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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.

Upvotes: 2

MatSnow
MatSnow

Reputation: 7517

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], ..."

Upvotes: 1

Ayush Singh
Ayush Singh

Reputation: 53

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 & "');"

Upvotes: 0

Related Questions