Syntax error when executing INSERT INTO statement

Question

I input the Right dataSource but it didnt i cant fixed the problem cmd.ExecuteNonQuery() saying:

Syntax error in INSERT INTO statement.

Code:

Private Sub btnadd1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd1.Click
    Dim cmd As New OleDb.OleDbCommand
    Dim Printlist1 As New DataTable
    If Not con.State = ConnectionState.Open Then
        con.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0; Data Source=c:Database11.accdb"
        con.Open()
        cmd.Connection = con
    End If
    If Me.text1.Tag & "" = "" Then
        cmd.CommandText = "INSERT INTO Printlist1(StickerCode, Description, Company, Department, Location, User, SerialNumber, DatePurchased, Tagable, Quantity, Brand, Model)" & _
                            " VALUES(" & Me.text1.Text & ",'" & Me.text2.Text & "','" & _
                                Me.text3.Text & "','" & Me.text4.Text & "','" & Me.text5.Text & "','" & _
                                Me.text6.Text & "','" & Me.text7.Text & "','" & Me.text8.Text & "','" & _
                                Me.text9.Text & "','" & Me.text10.Text & "','" & Me.text11.Text & "','" & _
                                Me.text12.Text & "')"
        cmd = New OleDbCommand(cmd.CommandText, con)
        cmd.ExecuteNonQuery()
    Else
        cmd.CommandText = "UPDATE Printlist1 " & _
                        " SET StickerCode='" & Me.text1.Text & _
                        ", Description='" & Me.text2.Text & "'" & _
                        ", Company='" & Me.text3.Text & "'" & _
                        ", Department='" & Me.text4.Text & "'" & _
                        ", Location='" & Me.text5.Text & "'" & _
                        ", User='" & Me.text6.Text & "'" & _
                        ", SerialNumber='" & Me.text7.Text & "'" & _
                        ", DatePurchased='" & Me.text8.Text & "'" & _
                        ", Tagable='" & Me.text9.Text & "'" & _
                        ", Quantity='" & Me.text10.Text & "'" & _
                        ", Brand='" & Me.text11.Text & "'" & _
                        ", Model='" & Me.text12.Text & "'" & _
                        " WHERE text1=" & Me.text1.Tag
        cmd.ExecuteNonQuery()
    End If
    RefreshData()
    Me.btnclear1.PerformClick()
    con.Close()
End Sub

When you trace through the code, what's the value of the cmd.CommandText property right before it gives it to the OleDbCommand? Also, as someone mentioned yesterday, you really ought to be using parameters. — Steven Doggart
– Steven Doggart, Commented Dec 3, 2013 at 16:11
I really dont think you have to use SQL to insert in a datatable, you can do it just with vb net code — bto.rdz
– bto.rdz, Commented Dec 3, 2013 at 16:13

Karl Anderson · Accepted Answer · 2013-12-03 16:25:19Z

6

Use a parameterized query, like this:

cmd.CommandText = "INSERT INTO Printlist1(StickerCode, Description, Company, Department, Location, User, SerialNumber, DatePurchased, Tagable, Quantity, Brand, Model)" & _
                        " VALUES(@StickerCode, @Description, @Company, @Department, @Location, @User, @SerialNumber, @DatePurchased, @Tagable, @Quantity, @Brand, @Model)"

cmd.Parameters.AddWithValue("@StickerCode", Me.Text1.Text)
cmd.Parameters.AddWithValue("@Description", Me.Text2.Text)
cmd.Parameters.AddWithValue("@Company", Me.Text3.Text)
cmd.Parameters.AddWithValue("@Department", Me.Text4.Text)
cmd.Parameters.AddWithValue("@Location", Me.Text5.Text)
cmd.Parameters.AddWithValue("@User", Me.Text6.Text)
cmd.Parameters.AddWithValue("@SerialNumber", Me.Text7.Text)
cmd.Parameters.AddWithValue("@DatePurchased", Me.Text8.Text)
cmd.Parameters.AddWithValue("@Tagable", Me.Text9.Text)
cmd.Parameters.AddWithValue("@Quantity", Me.Text10.Text)
cmd.Parameters.AddWithValue("@Brand", Me.Text11.Text)
cmd.Parameters.AddWithValue("@Model", Me.Text12.Text)

Note: It is best to keep the order of the parameters in line with the query, as databases like Microsoft Access will not execute the query correctly if the order is altered.

answered Dec 3, 2013 at 16:25

Karl Anderson

34.9k12 gold badges69 silver badges83 bronze badges

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

1 Comment

user3049808 Over a year ago

i will try and i will try to understand the Parameterizes query..thankyou.

RBarryYoung · Accepted Answer · 2013-12-03 16:21:46Z

3

It is likely that one of your Me.textN.Text values has an apostrophe in it or some other unexpected character that is breaking your SQL quotes. The solution to this is to use parametized queries and/or stored procedure instead.

This incidentally, will also protect you form the SQL Injection attacks that take advantage of the same shortcoming in composing SQL commands as strings in the client application.

(NOTE: I am assuming the Me.text1.Text as the StickerCode is a number. Otherwise that's the problem as you are not quoting it the way you do with the other columns.)

edited Dec 3, 2013 at 16:21

answered Dec 3, 2013 at 16:14

RBarryYoung

57k15 gold badges99 silver badges143 bronze badges

Comments

David Sdot · Accepted Answer · 2013-12-03 16:14:48Z

1

First line is missing as '

...
"SET StickerCode='" & Me.text1.Text & "'" & _ 
...

answered Dec 3, 2013 at 16:14

David Sdot

2,3331 gold badge21 silver badges28 bronze badges

2 Comments

Steven Doggart Over a year ago

But that's an error in the update command, not the insert command.

Antonio Over a year ago

@StevenDoggart Or both. If StickerCode is a text field then the quotes are missing on the INSERT statement too.

Rodney Schuler · Accepted Answer · 2013-12-03 16:15:59Z

0

You are missing single quotes around your first value. Try

" VALUES('" & Me.text1.Text & "','" & Me.text2.Text & "','" & _
    Me.text3.Text & "','" & Me.text4.Text & "','" & Me.text5.Text & "','" & _
    Me.text6.Text & "','" & Me.text7.Text & "','" & Me.text8.Text & "','" & _
    Me.text9.Text & "','" & Me.text10.Text & "','" & Me.text11.Text & "','" & _
    Me.text12.Text & "')"

answered Dec 3, 2013 at 16:15

Rodney Schuler

2,1584 gold badges23 silver badges34 bronze badges

2 Comments

Fred Over a year ago

Doesnt that depend on the datatype?

Rodney Schuler Over a year ago

Yes it does depend on the data type of the column. From the variable name, text1, I assumed the column is a string type.

Collectives™ on Stack Overflow

Syntax error when executing INSERT INTO statement

4 Answers 4

1 Comment

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related