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
– Steve, Commented Sep 23, 2013 at 14:42

Community · Accepted Answer · 2017-05-23 12:33:21Z

6

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

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Sep 23, 2013 at 14:30

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Comments

GeoffWilson · Accepted Answer · 2013-09-23 14:29:09Z

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, 2013 at 14:29

GeoffWilson

4337 silver badges21 bronze badges

Comments

Khan · Accepted Answer · 2013-09-23 14:31:09Z

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, 2013 at 14:31

Khan

18.2k5 gold badges52 silver badges60 bronze badges

2 Comments

user2775540 Over a year ago

i get 'the connection's current state is closed' what must i do

Khan Over a year ago

Me.SqlConnection1.Open() before the Fill and Me.SqlConnection1.Close() after.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related