VB.net - You have an error in your SQL syntax;

Question

I have problem, I want to input data in my database but I get an error message (you have an error in your SQL syntax; check the manual that corresponds to ypur MariaDB server version for the right syntax to use near 'unique, partno, boxs, pcs, modul, p_lane) VALUES (1, 'T103','16581-57020',3,1' at line 1)

but after checking the syntax I used accordingly

this is my code

 ElseIf ComboBox1.Text = "TAPPING PLAN TMC" Then
        Dim cmd As New MySqlCommand("insert into s_unpackingtmc(no, unique, partno, boxs, pcs, modul, p_lane) VALUES (@Column1, @Column2, @Column3, @Column4, @Column5, @Column6, @Column7)", conn)
        Try


            For i As Integer = 0 To (DataGridView1.Rows.Count - 2)

                no1 = DataGridView1.Rows(i).Cells(0).Value
                unique1 = DataGridView1.Rows(i).Cells(1).Value
                partno1 = DataGridView1.Rows(i).Cells(2).Value
                boxs1 = DataGridView1.Rows(i).Cells(3).Value
                pcs1 = DataGridView1.Rows(i).Cells(4).Value
                modul1 = DataGridView1.Rows(i).Cells(5).Value
                plane1 = DataGridView1.Rows(i).Cells(6).Value


                cmd.Parameters.AddWithValue("@Column1", no1)
                cmd.Parameters.AddWithValue("@Column2", unique1)
                cmd.Parameters.AddWithValue("@Column3", partno1)
                cmd.Parameters.AddWithValue("@Column4", boxs1)
                cmd.Parameters.AddWithValue("@Column5", pcs1)
                cmd.Parameters.AddWithValue("@Column6", modul1)
                cmd.Parameters.AddWithValue("@Column7", plane1)
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
            Next
            MsgBox("Data Berhasil Diupload", MsgBoxStyle.Information, "Information")
            Call tampildata3()

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Information, "Information")
            cmd.Dispose()
            Call tampildata3()



        End Try

Perhaps because `unique` is a reserved word? I believe the identifier quoting character in MariaDB is a backtick, so I'd suggest trying ``... no, `unique`, partno, ...`` and see if you have any luck. — Tyler Roper, Aug 06 '18 at 04:14
@TylerRoper okk thanks, the problem is solve I replace variable unique with other variable — Muhammad Ruhiyat, Aug 06 '18 at 04:32

score 0 · Answer 1 · answered Aug 06 '18 at 06:18

According to MariaDB documentation keywords and constraints, UNIQUE is a reserved keyword for table unique constraint definition:

The UNIQUE keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index.

For UNIQUE keys, PRIMARY KEYs and FOREIGN KEYs, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages.

You can use either backtick or double quotes depending on each modes (standard or ANSI SQL mode respectively, see related issue) or rename it to other non-keyword identifier:

Standard mode

insert into s_unpackingtmc(no, `unique`, partno, boxs, pcs, modul, p_lane) VALUES (@Column1, @Column2, @Column3, @Column4, @Column5, @Column6, @Column7)

ANSI SQL mode

insert into s_unpackingtmc(no, "unique", partno, boxs, pcs, modul, p_lane) VALUES (@Column1, @Column2, @Column3, @Column4, @Column5, @Column6, @Column7)

VB.net - You have an error in your SQL syntax;

1 Answers1