CODEWITHSUNDEEP
vb.net
Samuel Ronald
Samuel Ronald

Reputation: 55

How to delete selected row from database and datagridview

i have data in my database, what happens is that when i select a row and right click it and then click delete, all rows having the same StudentNo get deleted from the datagridview and database. what i want is that only the selected row gets deleted from the database and datagridview. attached is an image of my data. datagridview

this is the code i have.

Try
        If Me.DataGridView1.Rows.Count > 0 Then
            If Me.DataGridView1.SelectedRows.Count > 0 Then
                Dim intStdID As Integer = Me.DataGridView1.SelectedRows(0).Cells("StudentNO").Value
                'open connection
                If Not myconnection.State = ConnectionState.Open Then
                    myconnection.Open()
                End If

                'delete data
                Dim cmd As New SqlCommand
                cmd.Connection = myconnection
                cmd.CommandText = "DELETE FROM AlevelGeneralResults WHERE StudentNO=" & intStdID
                Dim res As DialogResult
                res = MsgBox("Are you sure you want to DELETE the selected Row?", MessageBoxButtons.YesNo)
                If res = Windows.Forms.DialogResult.Yes Then
                    cmd.ExecuteNonQuery()
                Else : Exit Sub
                End If
                'refresh data
                refreshdgv()

                'close connection
                myconnection.Close()
            End If
        End If
    Catch ex As Exception
    End Try

Upvotes: 1

Views: 16306

Answers (1)

Nik Bo
Nik Bo

Reputation: 1410

You could use more criteria in your SQL to make the Row unique.

On the first look I would add the Columns Subject and BOTP1.

Your SQL would look like this:

DELETE FROM AlevelGeneralResults WHERE StudentNO=" & intStdID & " AND Subject='" & strSubject & "' AND BOTP1=" & intBotp

Before your must add two new variable beside intStdID

Dim intStdID As Integer = Me.DataGridView1.SelectedRows(0).Cells("StudentNO").Value
Dim strSubject As String = Me.DataGridView1.SelectedRows(0).Cells("Subject").Value
Dim intBotp As Integer = Me.DataGridView1.SelectedRows(0).Cells("BOTOP1").Value

The best Way would be if you have an Index/ID which could make your data row unique. But in your screenshot has no visible column which could be the ID.

Upvotes: 1

Related Questions