插入vb.net和access 2007中的语法错误

Question

真的需要这方面的帮助... *插入语句时的语法错误*

私有子按钮2_Click（发件人作为对象，e作为事件参数）处理按钮2.单击

    Try
        connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =|DataDirectory|\StJudeData2.accdb;persist security info = false"
        conn.ConnectionString = connstring


        Dim SqlQuery As String = "INSERT INTO StJudeData2(FirstName,MiddleInitial,LastName,Block#,Lot#,Telephone,HomeSticker#,Phase#) VALUES (@FirstName,@LastName,@Block#,@Lot#,@Telephone,@HomeSticker#,@Phase#)"
        Dim SqlCommand As New OleDbCommand(SqlQuery, conn)


        With SqlCommand
            .CommandText = SqlQuery
            .Connection = conn
            conn.Open()
            SqlCommand.Parameters.AddWithValue("@FirstName", TextBox1.Text)
            SqlCommand.Parameters.AddWithValue("@LastName", TextBox2.Text)
            SqlCommand.Parameters.AddWithValue("@MiddleIntial", TextBox3.Text)
            SqlCommand.Parameters.AddWithValue("@Block#", TextBox4.Text)
            SqlCommand.Parameters.AddWithValue("@Lot#", TextBox5.Text)
            SqlCommand.Parameters.AddWithValue("@Telephone", TextBox6.Text)
            SqlCommand.Parameters.AddWithValue("@HomeSticker#", TextBox7.Text)
            SqlCommand.Parameters.AddWithValue("@Phase#", TextBox8.Text)

            SqlCommand.ExecuteNonQuery()

        End With

        MsgBox("Records had been save!")
    Catch ex As Exception
        MsgBox(ex.ToString)
        conn.Close()


    End Try

Answer 1

当您在列名称中使用类似 # 的字符时，您应该记住将这些名称放在方括号中，因为它们会混淆访问

 Dim SqlQuery As String = "INSERT INTO StJudeData2 " & _
  "(FirstName,MiddleInitial,LastName,[Block#],[Lot#],Telephone,[HomeSticker#],[Phase#]) " & _
  "VALUES (@FirstName,@LastName,@Block,@Lot,@Telephone,@HomeSticker,@Phase)"

顺便说一句，你真的需要它们吗？。每次为此表编写查询时都会遇到这个问题，所以，如果还不算太晚，请更改这些列名。

如下所述，您的查询中还有另一个错误，MiddleInitial字段没有为其设置参数，您需要在@FirstName之后为MiddleInitial字段添加另一个参数(该位置对于无法通过名称识别参数的OleDb非常重要)