Filter datatable not using if or for each

[yash.choursia]

Hi Team,

I have one datatable with the above structure.
I have another datatable which contains only 1 column “InvoiceNo.”.
I want to filter datatable1. Like, if datatable1 contains Values in InvoiceNo. column of the Datatable 2, i want to delete that particular row from datatable1.

Please help.

[supermanPunch (Arpan)]

@yash.choursia

I have one datatable with the above structure.
I have another datatable which contains only 1 column “InvoiceNo.”.
I want to filter datatable1. Like, if datatable1 contains Values in InvoiceNo. column of the Datatable 2, i want to delete that particular row from datatable1.

Hi @yash.choursia ,

A bit of a confusion with what is the Datatable1 and Datatable2. But eventually we should be able to perform the below steps :

1. Get the invoices values as Array of String from the Datatable which contains only 1 column Invoices. We can do it in the below way :

invoicesList = DT1.AsEnumerable.Select(Function(x)x("Invoices").ToString).ToArray

Here, invoicesList is a variable of Type Array of String.

2. Next, we can filter the DT2 with the invoices available in the created list like below :

DT2 = DT2.AsEnumerable.Where(Function(x)Not invoicesList.Contains(x("InvoiceNo.").ToString)).CopyToDatatable

DT2 is the datatable that is having the columns as provided in your screenshot

Direct Conversion to Datatable after Filter could result in Errors, we could handle it in the below manner :

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.As…

[rlgandu (Rajyalakshmi Gandu)]

@yash.choursia

I have one datatable with the above structure.
I have another datatable which contains only 1 column “InvoiceNo.”.
I want to filter datatable1. Like, if datatable1 contains Values in InvoiceNo. column of the Datatable 2, i want to delete that particular row from datatable1.

@yash.choursia

Assign:
filteredTable = (From row1 In datatable1.AsEnumerable()
Where Not (From row2 In datatable2.AsEnumerable()
Select row2.Field(Of String)(“InvoiceNo.”))
.Contains(row1.Field(Of String)(“InvoiceNo.”))
Select row1).CopyToDataTable()

Assign:
datatable1 = filteredTable