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

Oh Yeh. Got new error Syntax error in query date expression

Yhatz
– Yhatz

2014-03-23 17:45:17 +00:00
Commented Mar 23, 2014 at 17:45 — Yhatz
– Yhatz, Commented Mar 23, 2014 at 17:45

Steve · Accepted Answer · 2014-03-23 17:50:41Z

1

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)

edited Mar 23, 2014 at 17:50

answered Mar 23, 2014 at 17:37

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Yhatz Over a year ago

Yeh I really need them.

Steve Over a year ago

Then you have no choice but use square brackets around them. However the parameters could be written without them

Tun Zarni Kyaw Over a year ago

in the query, there is a field MiddleInitial and no value for it

Yhatz Over a year ago

Oh Yeh. Got it. Got new error too Syntax error in query date expression. >.<

Steve Over a year ago

@TunZarniKyaw nice catch, then another error will be raised

Collectives™ on Stack Overflow

Syntax error in Insert into vb.net and access 2007

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related