I am trying to save all my data in a DataGridView into an Access database but I'm getting this error: Syntax error in INSERT INTO statement.

Question

0.00/5 (No votes)

See more:

I am trying to save all my data in a DataGridView into an Access database but I'm getting this error:

Syntax error in INSERT INTO statement.

VB

Dim tyresize As String
        Dim rate, pattern As String




        For i As Integer = 0 To Me.dgvorder.Rows.Count - 2
            'qutno = Me.TextBox1.Text
            tyresize = Me.dgvorder.Rows(i).Cells(3).Value.ToString
            rate = Me.dgvorder.Rows(i).Cells(4).Value.ToString
            pattern = Me.dgvorder.Rows(i).Cells(5).Value.ToString
            conecDB()
            initCMD()


            SQL = "Insert into order_item(Sales_Order_No," & _
                "number," & _
                "pattern," & _
                "repair," & _
"quotaction_no)values('" & Me.txtorderno.Text & "','" & tyresize & "' , '" & rate & "' , '" & pattern & "', '" & Me.txtqutno.Text & "')"

            execComDB(SQL)
            closeDB()

        Next

        MsgBox("Successfull Save......")

Posted 17-May-15 1:42am

Member 11696889

Updated 17-May-15 5:44am

Frankie-C

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 17-May-15 7:58am

Check the actual values of you variables - may contain quotes or other illegal characters...

Frankie-C 17-May-15 9:23am

Kornfeld already said all. Just a small suggestion, add:
MsgBox(SQL)
And check if everything is ok.
PS final semicolon is missing...

Michael_Davies 17-May-15 10:45am

Not sure if it matters but the variable rate will be stored in the field pattern and the variable pattern in the field repair...

[no name] 17-May-15 11:48am

There are some incorrect syntax containing the insertion operation..check the symbol u have used in insert section,avoid string type insertion,use paramaterized query its avoid sql injection.

Michael_Davies 18-May-15 5:46am

Another comment; you open, execute command then close the database connection for each record, better performance to open before the For loop execute your inserts and then close after the For loop.

What might help with debugging is to dump the SQL variable so you can see exactly what the INSERT string looks like; you can also walk thru with the debugger and look at the string before it is executed. That way you can spot if there are any characters that need escaping, for instance the character ' is used to enclose variables so can not appear in a variable and has to be escaped like so \', so obviously the \ character needs escaping too...

Private Function StripQuote(ByVal Source As String) As String
Dim strX As New StringBuilder(Source)
'
' Prepare a string for storage in database, do not change the order
'
strX.Replace("\", "\\")
strX.Replace("""", "\""")
strX.Replace("'", "\'")

StripQuote = strX.ToString
End Function

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2015-05-17T04:58:00

Solution 1

With the way you've built your SQL INSERT statement, it's no surprise you're getting syntax errors.

Goggle for "VB.NET parameterized query" and find out how to do it correctly. This will also help with another serious problem you've opened yourself up to: the complete destruction of your database through SQL Injection attacks.

Posted 17-May-15 4:58am

Dave Kreskowiak

Updated 17-May-15 7:16am

v2

Comments

Frankie-C 17-May-15 11:39am

I understood that he is using VBA in Access.

Dave Kreskowiak 17-May-15 13:15pm

Whoops. I forgot he's using VB.NET code. Still, parameterized queries still apply.

Frankie-C 17-May-15 13:21pm

sed. +5

Maciej Los 17-May-15 17:49pm

5ed!
Please see my answer. I added bit more information about possible issues ;)

Maciej Los · Answer 2 · 2015-05-17T11:47:00

VB

SQL = "Insert into order_item(Sales_Order_No," & _
                "number," & _
                "pattern," & _
                "repair," & _
"quotaction_no)values('" & Me.txtorderno.Text & "','" & tyresize & "' , '" & rate & "' , '" & pattern & "', '" & Me.txtqutno.Text & "')"

Above sql statement means that you want to to add string data. I suspect that Sales_Order_No and number fields are numeric fields. Remove single quotes around these data and try again.

As Dave mentioned, your sql code is sql injection[^] vulnerable. So you have to change your code to use parametrized queries.

VB

SQL = "Insert into order_item(Sales_Order_No, [number], pattern, repair, quotaction_no)" & _
"values(?, ?, ?, ?, ?)"

Do not use number as a field name! It's reserved word[^]. To workaround this, i added [] brackets.

Now, you have to create OleDbCommand[^] with parameters[^].

VB

Dim command As New OleDbCommand(queryString, connection)
command.CommandText = SQL;
'1. Sales_Order_no
command.Parameters.Add("@p1", OleDbType.Integer).Value = CInt(Me.txtorderno.Text)
'2. Number
command.Parameters.Add("@p2", OleDbType.Integer).Value = tyresize
'3. pattern
command.Parameters.Add("@p3", OleDbType.Char).Value = rate
'and so on...

Note: the order in which you add parameters is very important!

See: OleDbParameter Constructor (String, OleDbType)[^]