Getting Syntax Error on INSERT INTO command on Visual Basic 2010 to MS ACCESS 2007

Question

I keep getting a syntax error when I run a debug on the following code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles add.Click
    Dim cmd As New OleDb.OleDbCommand
    If Not cnn.State = ConnectionState.Open Then
        'Open Connection if not yet Open
        cnn.Open()  End If
    cmd.Connection = cnn

    If Me.sn.Tag & "" = "" Then            
        cmd.CommandText = "INSERT INTO First_Year(Student_No,Lastname,Firstname,Year_Level,Enroll_Date,SEX,SY,CIVIL_STATUS,Religion,Birthdate,TEL_NO,Father,Occupation_F,Mother,Occupation_m,School Last Attended,Address School,Middle_Name)" +
                          "VALUES ('" & Me.sn.Text & "','" & Me.fn.Text & "','" & Me.ln.Text & "' ,'" & Me.Year.Text & "','" & Me.ed.Value & "','" & Me.s.Text & "','" & Me.sy.Text & "','" & Me.cs.Text & "','" & Me.re.Text & "'," & Me.cn.Text & ",'" & Me.bd.Value & "','" & Me.fa.Text & "','" & Me.fo.Text & "','" & Me.ma.Text & "','" & Me.mo.Text & "','" & Me.lad.Text & "','" & Me.ad.Text & "','" & Me.mi.Text & "')"
        cmd.ExecuteNonQuery()

Can some please point out to me whats wrong with it?

Could you also please include either a screenshot or verbatim write up of the error message that you're getting please. — Jujhar Singh, Aug 07 '14 at 15:22

score 1 · Accepted Answer · edited May 23 '17 at 11:51

You have some fields name that contains spaces. To use these fields names you need to enclose them in square brackets

 cmd.CommandText = "INSERT INTO First_Year " & _
            "(Student_No,Lastname,Firstname,Year_Level,Enroll_Date,SEX, " & _
            "SY,CIVIL_STATUS,Religion,Birthdate,TEL_NO,Father,Occupation_F,Mother, " & 
            "Occupation_m,[School Last Attended],[Address School],Middle_Name) " & 
            "...... "

Said that, remember that string concatenations like yours lead to Sql Injection and problem in parsing strings that contains quotes (O'Brien) or decimal numbers or date

Search about Sql Injection and Parameterized queries

A parameterized approach to your query would be

cmd.CommandText = "INSERT INTO First_Year " & _
        "(Student_No,Lastname,Firstname,Year_Level,Enroll_Date,SEX, " & _
        "SY,CIVIL_STATUS,Religion,Birthdate,TEL_NO,Father,Occupation_F,Mother, " & 
        "Occupation_m,[School Last Attended],[Address School],Middle_Name) " & 
        "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cmd.Parameters.AddWithValue("@p1", Me.sn.Text)
cmd.Parameters.AddWithValue("@p2", Me.fn.Text)
... and so on for the remainder 16 parameters placeholders 
... respecting their position and converting to the appropriate datatype

score 0 · Answer 2 · answered Aug 07 '14 at 15:20

0

you need to remove the space here (in your query) :

......School Last Attended,Address School.......

or write it like this :

..........[School Last Attended],[Address School]..........

answered Aug 07 '14 at 15:20

Youness

1,468
1
9
19

Getting Syntax Error on INSERT INTO command on Visual Basic 2010 to MS ACCESS 2007

2 Answers2