连接字符串-插入vb.net并访问2007时出现语法错误

Question

这个真的需要帮助…*插入语句语法错误*

Private Sub Button2_Click(sender As Object, e As EventArgs)处理Button2。点击

    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

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

 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来说很重要，因为它不能通过名称识别参数)