VB .NET SQL Delete error 'incorrect syntax near '='

Question

i have problem trying to delete record from my VS 2012 and i'm using sql server 2012, this is my task from my lecturer, and i cant solved it

now this is what i have

Private Sub bt_hapus_Click(sender As Object, e As EventArgs) Handles bt_hapus.Click
    Try
        Dim sqlda As New SqlClient.SqlDataAdapter("Delete from tabelpasien where No_Rkm_Mds=" & Me.txt_rkm_mds.Text, Me.SqlConnection1)
        sqlda.Fill(dspasien, "tabelpasien")
        MsgBox("Data telah berhasil dihapus")
        bersih()
        pasif()
        normal()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

any help would be greatly apreciated...

Then, the error are the missing quotes around the textbox value, but, as I have explained in my answer below, NEVER use string concatenation to build an SQL command. — Steve, Sep 23 '13 at 14:42

score 6 · Accepted Answer · edited May 23 '17 at 12:33

A delete command is executed using an SqlCommand and the ExecuteNonQuery method.

Your code should be

Try
    Dim cmd = New SqlClient.SqlCommand("Delete from tabelpasien where No_Rkm_Mds=@rkm", Me.SqlConnection1)
    cmd.Parameters.AddWithValue("@rkm", Me.txt_rkm_mds.Text)
    cmd.ExecuteNonQuery()
    ....

Using a parameterized query you don't have to put quotes around your where values (if the underlying field is any kind of char/varchar/nvarchar type) but, the most important benefit of a parameterized query is the elimination of a possible Sql Injection attack

score 1 · Answer 2 · answered Sep 23 '13 at 14:29

1

You have forgotten your single quote marks I.E." ' " from around your condition.

Your statement Should be

Delete From tabelpasien where No_Rkm_Mds='" + Me.txt_rkm_mds.Text + "'"

answered Sep 23 '13 at 14:29

GeoffWilson

443
7
20

score 1 · Answer 3 · answered Sep 23 '13 at 14:31

1

If this is SQL SERVER, there shouldn't be a FROM in the statement.

Dim sqlda As New SqlClient.SqlDataAdapter("DELETE tabelpasien where No_Rkm_Mds=" & Me.txt_rkm_mds.Text, Me.SqlConnection1)

If No_Rkm_Mds is a VARCHAR or NVARCHAR, etc..., the value must be wrapped in 's.

Dim sqlda As New SqlClient.SqlDataAdapter("DELETE tabelpasien where No_Rkm_Mds=`" & Me.txt_rkm_mds.Text & "`", Me.SqlConnection1)

Finally, you should consider using SQL Parameters to avoid SQL injection.

answered Sep 23 '13 at 14:31

Khan

17,118
3
45
58

i get 'the connection's current state is closed' what must i do – user2775540 Sep 23 '13 at 14:46
`Me.SqlConnection1.Open()` before the `Fill` and `Me.SqlConnection1.Close()` after. – Khan Sep 23 '13 at 14:56

VB .NET SQL Delete error 'incorrect syntax near '='

3 Answers3