Question Deleting Multiple rows in DataTable

[gchq]

Hi there

To delete a specific row in a DataTable I would

Dim vIndex as integer 
For Each row as DataRow in DT.rows
If x = y then
vIndex = DT.Rows.IndexOf(Row)
End iF
Next
DT.rows(vIndex).Delete()
DT.AcceptChanges()

The issue is - what do I do if x = y for more than one row?

 

[gchq]

Solution

Here is the solution


Add a primary key to the DataTable

Dim vStateInvoices As New DataTable
        With vStateInvoices.Columns
            .Add("Key", GetType(Integer)).AutoIncrement = True
            .Add("Customer_ID", GetType(Integer))
            .Add("Type", GetType(String))
            .Add("Invoice_No", GetType(Integer))
            .Add("InvDate", GetType(Date))
            .Add("Debit", GetType(Double))
            .Add("Credit", GetType(Double))
            .Add("Description", GetType(String))
        End With
        vStateInvoices.PrimaryKey = New DataColumn() {vStateInvoices.Columns("Key")}

Add all the data

For Each Row As DataRow In vCust.Rows
                Dim vIDRR() As DataRow = vST.Select("Customer_ID = " & Row("Customer_ID"))
                For Each Subrow As DataRow In vIDRR
                    Dim vInvoiceNo As Integer = NullValues(Subrow("Invoice_No"))
                    Dim vNomCode() As DataRow = vInv.Select("Invoice_No = " & vInvoiceNo, Nothing)
                    For Each InnerRow As DataRow In vNomCode
                        With vStateInvoices.Rows
                            Dim vKey As Integer = 1
                            If vStateInvoices.Rows.Count > 0 Then
                                vKey = NullInteger(vStateInvoices.Compute("MAX(Key)", Nothing)) + 1
                            End If
                            .Add(vKey, Subrow("Customer_ID"), Subrow("Type"), Subrow("Invoice_No"), Subrow("Document_Date"), Subrow("Debit"), Subrow("Credit"), Subrow("S_Description"))
                        End With
                    Next
                Next
            Next

Add another DataTable to hold the deleted rows

Dim vDelRows As New DataTable
            With vDelRows.Columns
                .Add("KeyNo", GetType(Integer))
            End With

'Determine the rows to be deleted and add to the new DataTable

For Each KVP As KeyValuePair(Of String, Integer) In Dic
                Dim vUsedNom As Integer = KVP.Value
                Dim vIndex As Integer = 0
                For Each Row As DataRow In vStateInvoices.Rows
                    Dim vInvoiceNo As Integer = NullValues(Row("Invoice_No"))
                    Dim vNomCode() As DataRow = vInv.Select("Invoice_No = " & vInvoiceNo, Nothing)
                    For Each InnerRow As DataRow In vNomCode
                        If InnerRow("Nominal_ID") = vUsedNom Then
                           'Delete this row
                             With vDelRows.Rows
                               .Add(Row("Key"))
                            End With
                        End If
                    Next
                                  Next
                
            Next

How interate through the 'detete' DataTable and remove the rows

For Each Row As DataRow In vDelRows.Rows
                AppBox.Show(Row("KeyNo").ToString, MessageBoxButtons.OK, MessageBoxIcon.Information)
                vStateInvoices.Rows.Remove(vStateInvoices.Rows.Find(Row("KeyNo")))
            Next