Jose M. Jose M. - 4 months ago 70
Vb.net Question

Deleting multiple records from sql bound Datagrid

The procedures below allowed me to delete several records at once by checking the checkbox on my datagrid. The procedure was written on ASP.net but now I am using a winform on VB.net.

I have a datagrid with column name "Delete" where the checkboxes are located. The user would check
the records it wants to delete and the would delete those records. I use the "Ticket Number" column values as the parameter for my query.

The issue I have is that since was written for ASP.Net, I cannot find how the winform VB.net equivalent for this line:

Dim chkDelete As CheckBox = DirectCast(grdRoster.Rows(i).Cells(0).FindControl("Delete_Row"), CheckBox)


FindControl is not a member of System.Windows.Forms.DataGridViewCell. Plus I am pretty sure that the whole line is wrong since the checkboxes
are located on a datagrid column set as ColumnType: DataGridViewCheckBoxColumn and are not really individual controls.

How can I get the same result on a winform? Here is my entire code.

Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click

'Create String Collection to store
'IDs of records to be deleted

Dim ticketNumberCollection As New StringCollection()
Dim strTicketNumber As String = String.Empty

'Loop through GridView rows to find checked rows

For i As Integer = 0 To grdRoster.Rows.Count - 1

Dim chkDelete As CheckBox = DirectCast(grdRoster.Rows(i).Cells(0).FindControl("Delete_Row"), CheckBox)

If chkDelete IsNot Nothing Then
If chkDelete.Checked Then
strTicketNumber = grdRoster.Rows(i).Cells(1).ToString
ticketNumberCollection.Add(strTicketNumber)
End If
End If

Next

'Call the method to Delete records
DeleteMultipleRecords(ticketNumberCollection)

' rebind the GridView
grdRoster.DataBind()

End Sub

' Sub to delete multiple records
' @param "idCollection" calls the string collection above
' and deletes the selected record separated by ","

Private Sub DeleteMultipleRecords(ByVal ticketNumberCollection As StringCollection)

Dim IDs As String = ""

'Create string builder to store
'delete commands separated by ,

For Each id As String In ticketNumberCollection

IDs += id.ToString() & ","

Next

Try
Dim strTicketID As String = IDs.Substring(0, IDs.LastIndexOf(","))

DataSheetTableAdapter.DeleteRecord(strTicketID)

Catch ex As Exception

Dim errorMsg As String = "Error in Deletion"
errorMsg += ex.Message
Throw New Exception(errorMsg)

Finally

Me.Close()


End Try
End Sub

Answer

for deleting multiple records from a data bound gridview you should create the DataGridViewCheckBoxColumn at run time

    Dim chk As New DataGridViewCheckBoxColumn()
            DataGridView1.Columns.Add(chk)
            chk.HeaderText = "Select"

'then bind your datagridview with dataset

 Dim sql As String = "SELECT * FROM table_name"
    ' Dim connection As New SqlConnection(connectionString)
    conn.Open()
    sCommand = New SqlCommand(sql, conn)
    sAdapter = New SqlDataAdapter(sCommand)
    sBuilder = New SqlCommandBuilder(sAdapter)
    sDs = New DataSet()
    sAdapter.Fill(sDs, "table_name")
    sTable = sDs.Tables("table_name")

 DataGridView1.DataSource = sDs.Tables("table_name")

'then traverse through each column and get the checked values

Try
        DataGridView1.EndEdit()
        For j = Me.DataGridView1.Rows.Count - 1 To 0 Step -1
            If Not IsDBNull(DataGridView1.Rows(j).Cells(0).Value) Then
                If DataGridView1.Rows(j).Cells(0).Value = True Then
                    check = True
                    If MessageBox.Show("Do you want to delete these records?", "Delete", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                        For i = Me.DataGridView1.Rows.Count - 1 To 0 Step -1
                            If Not IsDBNull(DataGridView1.Rows(i).Cells(0).Value) Then
                                If DataGridView1.Rows(i).Cells(0).Value = True Then

                                 'remove the checked columns and update datatable
                                    DataGridView1.Rows.RemoveAt(i)
                                    sAdapter.Update(sTable)
                                End If
                            End If
                        Next
                    Else
                        Return
                    End If
                Else
                End If
            End If
        Next
        If check = False Then
            MsgBox("Nothing Selected")
        End If

                Catch ex As Exception
        MsgBox(ex.ToString)
    End Try