Deleting Multiple records from Datagrid using a String Collection

Question

I have build a datagrid with a checkbox column so that the user can select which records to delete. I had posted a similar question a few days ago.

I have only managed to get the procedures to work half way. My procedure as written deletes only if one record is selected. It does not delete the entire list. This is my UI:

Here is my code:

    Private Sub btnDeleteRecord_Click(sender As Object, e As EventArgs) Handles btnDeleteRecord.Click
       'This procedure deletes the selected ticket number
       'from the DataSheet table. The procedure refreshes
       'the Datasheet table and the labels in the frmMainForm
      Dim idCollection As New StringCollection()
      Dim strID As String = String.Empty
        Try
        'Store each selected record on string collection
           For Each row As DataGridViewRow In grdDeleteRecord.Rows
             If row.Cells(0).Value() Then
               strID = row.Cells(1).Value()
               idCollection.Add(strID)
             End If
            Next
        'Call procedure to delete multiple records
        DeleteMultipleRecords(idCollection)
        Catch ex As Exception
           MsgBox(ex.ToString())
        End Try
      End Sub
  Private Sub DeleteMultipleRecords(ByVal idCollection As StringCollection)
     Dim IDs As String = ""
     'Create string builder to store 
     'delete commands separated by ;
       For Each id As String In idCollection
         IDs += id.ToString() & ","
       Next
      Try
        Dim strIDs As String = IDs.Substring(0, IDs.LastIndexOf(","))
        Dim strPrompt As String = "Record deletion cannot be undone. Are you sure you want to delete the record?"
        Dim strTitle As String = "Delete Record"
        Dim msgProceed As MsgBoxResult
      'Warn the user that records cannot be undone
        msgProceed = MsgBox(strPrompt, CType(MsgBoxStyle.YesNo + MsgBoxStyle.Critical, MsgBoxStyle), strTitle)
      If msgProceed = MsgBoxResult.Yes Then
      'Local variables
         Dim strTicketNumber As String = txtTicketNumber.Text
         Dim todayDate As Date = Date.Now
         Dim beginWeek As Date = Public_Subs.MondayOfWeek(todayDate).Date
         Dim endOfWeek As Date = beginWeek.AddDays(6)
         Dim strBeginDay As String = todayDate.ToShortDateString & " 12:00:00 AM"
         Dim strEndDay As String = todayDate.ToShortDateString & " 11:59:59 PM"
         Cursor.Current = Cursors.WaitCursor
         DataSheetTableAdapter.DeleteRecord(strIDs)
      Else
         btnClear.Visible = False
         btnDeleteRecord.Enabled = False
      End If
      Catch ex As Exception
        Dim errorMsg As String = "Error in Deletion"
        errorMsg += ex.Message
        Throw New Exception(errorMsg)
       Finally
      End Try
End Sub

I put a message box on the DeleteMultipleRecords sub and here is the display:

I believe the problem is that my DataSheetTableAdapter.DeleteRecord(strIDs) is not separating the records after each comma and iterating through each record and deleting. Can someone let me know where I am going wrong here?

Answer 1

I'll post this as an answer rather than a comment so I can include code.

1- Add your check box column to the grid in the designer. I assume that you already know how to do that but let us know if you don't.

2- Populate a DataTable and bind it to your grid via a BindingSource.

myAdapter.Fill(myDataTable)
Me.BindingSource1.DataSource = myDataTable
Me.DataGridView1.DataSource = Me.BindingSource1

If you're using a typed DataSet then you can set up the bindings in the designer.

3- After checking the appropriate boxes, delete the corresponding rows from the bound DataTable.

Dim rowsToDelete = (From row In Me.DataGridView1.Rows.Cast(Of DataGridViewRow)()
                    Where CBool(row.Cells(0).Value)
                    Select row.DataBoundItem).Cast(Of DataRowView)().ToArray()

For Each row In rowsToDelete
    row.Delete()
Next

That can be done other ways, either with or without LINQ, but you hopefully you get the idea.

4- Save all the changes back to the database using the same data/table adapter.

myAdapter.Update(myDataTable)