Syntax error in Insert into vb.net and access 2007

Question

Really need help about this... *Syntax error on Insert Into Statement*

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    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

Steve Steve · Accepted Answer · 2014-03-23T17:37:02

When you use characters like # in your column names, you should remember to put these names in square brackets because they are confusing Access

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

As a side note, do you really need them?. You will have this problem every time you write query for this table, so, if it is not too late, change these column names.

As noted below, there is another error in your query, the MiddleInitial field has no parameter set for it, you need to add another parameter for MiddleInitial field after the @FirstName (The position is important for OleDb that doesn't recognize the parameters by their names)

Syntax error in Insert into vb.net and access 2007

1 Answers