[maiOHmai]

i have a datagridview that has one checkbox column which is added programamtically and other the columns came from the database. what i want to happened is to delete rows that is checked in the checked box column.

this is the code im using:

  Dim ds As New DataSet
        For Each row As DataGridViewRow In dgvSearch2.Rows
            If row.Cells("Check").Selected = True Then
                con.Open()
                com.CommandText = "Select Password from login where Username = '" & MainFrm.lblLMCon.Text & "' and AccountType = 'Administrator';"
                com.Connection = con
                c = com.ExecuteReader
                If c.Read = True Then
                    Dim delete As String
                    Dim where As String
                    delete = InputBox("Enter administator password:")
                    where = InStr(c.Item("Password").ToString, delete)
                    If where Then
                        da = New OleDbDataAdapter("Delete * from list where ID = '" & dgvSearch2.CurrentRow.Cells("ID").Value & "'", con)
                        da.Fill(ds)
                        dgvSearch2.Rows.Remove(row)
                    Else
                        MsgBox("Invalid Password. Try Again!")
                    End If
                Else
                    MsgBox("Nothing to delete")
                End If
                con.Close()
            End If
        Next

though this code gave no error, my problem is that it only deletes one row upon button click. for example i checked two checkbox in the datagridview(which must represent the whole row) but only one of this is deleted.

i want to delete it in the database and not only in the dgv. can someone help me pinpoint what is wrong with the code?

[modi123_1]

What are you doing in there? A few things.. you do not need to open a connection inside of a for loop - if you have multiple rows to delete then just delete them with one connection.

Second, try just using a command object, and not an adapter, and writing a single delete statement. See how that works. I am unclear on the DB you are using but most do not require an asterisks in the delete statement.

Lines 13-16 are nonsensical. Say them out loud. You are creating an adapter with a delete statement... then you are using it to fill a dataset.. erk? You fill things when you get data right? Which means that would be a select statement We have some excellent tutorials on database interaction here.. go read up on those and rewrite what you are doing.

[maiOHmai]

thank you for your help, i came up with this code:

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        con.Open()
        For x As Integer = dgvSearch2.Rows.Count - 1 To 0 Step -1
            If dgvSearch2.Rows(x).Cells("Check").Value Then
                dgvSearch2.Rows.Remove(dgvSearch2.Rows(x))
            End If
        Next
con.close()
end sub

but it gives me this error

Quote

Conversion from type 'DBNull' to type 'Boolean' is not valid.

what's wrong with this?

[modi123_1]

Another great example of why debugging is great. What datatype is line 4's "value"? I am betting annotated a boolean. Try a convert to boolean.

[maiOHmai]

i may sound stupid.. but i have no idea how to convert "value" to boolean.. can you give me a hint on how to?

[_HAWK_]

 Convert.ToBoolean({object})

[maiOHmai]

thank you. i've changed my code from

 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        con.Open()
        For x As Integer = dgvSearch2.Rows.Count - 1 To 0 Step -1
            If dgvSearch2.Rows(x).Cells("Check").Value Then
                dgvSearch2.Rows.Remove(dgvSearch2.Rows(x))
            End If
        Next
con.close()
end sub

to this

 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        con.Open()
        For x As Integer = dgvSearch2.Rows.Count - 1 To 0 Step -1

            If dgvSearch2.Rows(x).Cells("RecSel").Value = True Then
                da = New OleDbDataAdapter("Delete from list", con)
                dgvSearch2.Rows.Remove(dgvSearch2.Rows(x))
            End If
        Next
end sub

i was able to delete the selected rows in the datagridview. but i wasn't deleted in the database. how am i able to delete same rows deleted in the datagridview in the database?