Unable to delete from database

Question

Unable to delete data from database in vb.net using SQL Server.

Here is my code for deletion, can you tell me where I have gone wrong?

Name of the table is OneToOne:

 Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    cn.Open()
    With cmd
        .Connection = cn
        .CommandText = " DELETE * FROM OneToOne WHERE SrNo = " & DataGridView1.SelectedRows(0).Cells(0).Value
        i = cmd.ExecuteNonQuery
    End With
    If (i > 0) Then
        MsgBox("Conversation Deleted")
    End If
    cn.Close()
    showdata()
End Sub

Ack. Before you do do anything else you need to read about, understand and start using parameterized queries. This is wide open to sql injection. My little friend bobby tables like to wreak havoc on systems like this. bobby-tables.com — Sean Lange
– Sean Lange, Commented May 10, 2016 at 20:17
Try taking out the *, it is not needed in a DELETE query, though I'm not sure that's the sole cause of the problem. — Tab Alleman
– Tab Alleman, Commented May 10, 2016 at 20:17
try to log theDataGridView1.SelectedRows(0).Cells(0).ValueDataGridView1.SelectedRows(0).Cells(0).Value, and see what value is returning — Oussema Aroua
– Oussema Aroua, Commented May 10, 2016 at 20:18
@SeanLange i've taken out..it's working but data is deleted step by step u can delete one at a time..i want that if i press button the whole should be deleted — Nitin
– Nitin, Commented May 10, 2016 at 20:19

Tab Alleman · Accepted Answer · 2016-05-10 20:27:43Z

2

To delete all rows at once, you can iterate through the selected rows and build a comma-separated string of the Cell(0) values.

Then instead of using WHERE srNo = , use WHERE srNo IN ({your string})

At least, this is the way to do it following your current approach. A better way would be to pass a table-valued parameter to a stored procedure, but that's a broad subject that you would need to research.

And, as with most programming questions, there are multiple other ways this can be done.

answered May 10, 2016 at 20:27

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

Comments

Rupesh Bhandari · Accepted Answer · 2016-05-10 20:43:38Z

0

on_button_click

s = "DELETE FROM OneToOne WHERE SrNo IN(val1,val2.....valn)" 
        cn.Open()
        cmd = New SqlCommand(s, cn)
        i = cmd.ExecuteNonQuery()
       If i > 0 Then
            MsgBox("yup")
        End If
        cn.Close()

val1,val2.. are your SrNo values. It's on you how you retrieve/provide SrNo values

answered May 10, 2016 at 20:43

Rupesh Bhandari

862 silver badges13 bronze badges

Collectives™ on Stack Overflow

Unable to delete from database

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related