database - 从 Visual Basic 2010 到 MS ACCESS 2007 上的 INSERT INTO 命令出现语法错误

Question

当我对以下代码运行调试时，我不断收到语法错误:

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()

有人能指出我有什么问题吗？

Answer 1

您有一些包含空格的字段名称。要使用这些字段名称，您需要将它们括在方括号中

 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) " & 
            "...... "

也就是说，请记住，像您这样的字符串连接会导致 Sql 注入(inject)和解析包含引号 (O'Brien) 或十进制数字或日期的字符串时出现问题

搜索关于 Sql Injection和 Parameterized queries

您的查询的参数化方法是

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