Incorrect UPDATE syntax near '='

Question

I got some error "incorrect syntax near '='" in update.

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    conn.Open()
    For i = 0 To Me.Dgv_sql.Rows.Count - 1
        Dim idx As String = Dgv_sql.Rows(i).Cells("ID").Value
        query = "UPDATE kkpsurabaya SET late = '" & Dgv_sql.Rows(i).Cells("late").Value & "' WHERE ID = " & idx

        If (UpdateData(query)) Then

        End If

    Next
    conn.Close()
    MessageBox.Show("Data Berhasil Di Update", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information)
    'dgv_sql_isi()
End Sub

Print to console the variables being used in the query along with the resulting query (before execution), and post the result here. — FDavidov, Jul 28 '16 at 07:26
Are you sure the idx exists and is assigned to the code well ? Please place breakpoint on line where you execute the query and look at the command itself — Hynek Bernard, Jul 28 '16 at 07:29
First step in debugging such a sql statement: what is the *real* statement that is executed? Maybe there is a value (containing quotes) that breaks your statement, or you need extra quotes. So the second step is: replace all values with parameters - then all sorts of problems go away.. — Hans Keﬆing, Jul 28 '16 at 07:31
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Jul 28 '16 at 08:26

score 0 · Accepted Answer · edited May 23 '17 at 12:08

0

Idx is a string so when you just do where ID = (and place string here) it'll fail.

Change to:

query = "UPDATE kkpsurabaya SET late = '" & Dgv_sql.Rows(i).Cells("late").Value & "' WHERE ID = '" & idx &"'"

But a better solution will just be to use Parameterized Queries all together.. - Will help you avoid SQL Injection

edited May 23 '17 at 12:08

Community

1
1

answered Jul 28 '16 at 07:28

Gilad Green

36,708
7
61
95

`idx` is a string, but is the `id` a (n)varchar? The question doesn't say. – Hans Keﬆing Jul 28 '16 at 07:29
1

True but I don't think it matters if `id` is a (n)varchar or not. It fails because of the lack of `''`. If it was because of the type it would have been a different error – Gilad Green Jul 28 '16 at 07:31

score -1 · Answer 2 · answered Jul 28 '16 at 07:28

-1

Create a variable and assign the value to that variable first.

vara=Dgv_sql.Rows(i).Cells("late").Value
query = "UPDATE kkpsurabaya SET late = '" & vara & "' WHERE ID = " & idx

Hope it will help.

answered Jul 28 '16 at 07:28

Pirate

2,886
4
24
42

Incorrect UPDATE syntax near '='

2 Answers2