Syntax error in INSERT INTO Statement in vb.net

Question

I am getting and syntax error in insert into statement. IF statement is working just fine just getting this error when it's trying to save the information

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If txt12Per.Text >= TextBox12.Text And txtGPer.Text >= TextBox11.Text And TextBox1.Text >= TextBox10.Text Then
            Try
                'Dim da As OleDb.OleDbDataAdapter
                Dim dbprovider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Taher\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\Database1.accdb;Persist Security Info=False;"
                Me.con = New OleDb.OleDbConnection()
                con.ConnectionString = dbprovider
                con.Open()

                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 & "');"
                Dim sqlcommand As New OleDb.OleDbCommand(sqlquery)

                With sqlcommand
                    .CommandText = sqlquery
                    .Connection = con
                    .ExecuteNonQuery()
                    con.Close()
                    txtUrn.Text = ""
                    txt12Per.Text = ""
                    txtGPer.Text = ""
                    txtName.Text = ""
                    cmbNameofGCourse.Text = ""
                End With
                MsgBox("Record Added")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        Else
            MsgBox("Student Not eligible for the requested company")
        End If
    End Sub

Can somebody help me with this....

getting this error at line 64 which is .executenonquery but says as Syntax Error in INSERT INTO statement — CrashOverride
– CrashOverride, Commented Feb 24, 2013 at 12:59
made the changes still getting the same error.....:( code updated — CrashOverride
– CrashOverride, Commented Feb 24, 2013 at 16:46

8192K · Accepted Answer · 2013-02-24 13:04:29Z

4

You are trying to insert into 13 columns but provide only 11 values!

answered Feb 24, 2013 at 13:04

8192K

5,2904 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guffa · Accepted Answer · 2013-02-24 13:02:48Z

4

You have 13 fields to insert values in, but only 11 values.

answered Feb 24, 2013 at 13:02

Guffa

703k112 gold badges760 silver badges1k bronze badges

Comments

Matt Bryant · Accepted Answer · 2013-08-02 03:02:39Z

2

Use brackets and it will work fine.

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 Aug 2, 2013 at 3:02

Matt Bryant

4,9714 gold badges33 silver badges46 bronze badges

answered Aug 2, 2013 at 2:27

noeladna

211 bronze badge

1 Comment

Matt Bryant Over a year ago

It would be nice if you'd format this answer. It's not readable as is.

CrashOverride · Accepted Answer · 2013-02-24 18:17:23Z

0

had to change the position field to something else don't know why but it worked

Dim sqlquery As String = "INSERT INTO MCAscheduled (URno,sname,hsc,gper,pgper,pstatus,cname,hrname,hscinter,ginter,pginter,comments,post,course,pcourse)" + "VALUES (" & CInt(txtUrn.Text) & ",'" & txtName.Text & "'," & CInt(txt12Per.Text) & "," & CInt(txtGPer.Text) & "," & CInt(TextBox1.Text) & ",'" & ComboBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "'," & CInt(TextBox12.Text) & "," & CInt(TextBox11.Text) & "," & CInt(TextBox10.Text) & ",'" & TextBox9.Text & "','" & ComboBox1.Text & "','" & cmbNameofGCourse.Text & "','" & TextBox5.Text & "');"

answered Feb 24, 2013 at 18:17

community wiki

CrashOverride

Collectives™ on Stack Overflow

Syntax error in INSERT INTO Statement in vb.net

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related