CODEWITHSUNDEEP
vb.netfilterdatatable
Ranga Gurusinghe
Ranga Gurusinghe

Reputation: 23

How to filter datatable

How can i filter a datatable as follows ?

I have two datatable as [cancelledOrders] and [allOrders] n my vb.net form both of the has got a column name [OrderNo].

What i want to do is filter [allOrders] data table where [OrderNo] column doesn't contain any value equal to values in [OrderNo] column of [cancelledOrders].

I tried to use List of String. But I wasn't successful. Could someone help me please.

Thanks

Upvotes: 0

Views: 7118

Answers (2)

Christiaan du Plessis
Christiaan du Plessis

Reputation: 9

I would filter in the SQL query. It will look something like this:

SELECT allOrders.* FROM allOrders WHERE NOT EXISTS (SELECT * FROM cancelledOrders WHERE cancelledOrders.orderno = allOrders.orderno)

Upvotes: 1

VBobCat
VBobCat

Reputation: 2712

Your question is not clear about if you need an exhibition filter or a data-fetching filter, so I'll assume the latter. I'll assume your [OrderNo] columns are both of type Integer. So here you go:

Function FilterCancelledOrders(allOrders As DataTable, cancelledOrders As DataTable) As DataRow

    ' Let's use LInQ to collect all the numbers of cancelledOrders into an Array Of Integer
    Dim CancelledOrderNos = cancelledOrders.Select.Select(
        Function(dr) dr.Field(Of Integer)("OrderNo")).ToArray

    ' Now let's collect all dataRows from allOrders where OrderNo is contained in the Array of Integer we just got above:
    Dim CancelledOrderRows = allOrders.Select.Where(
        Function(dr) CancelledOrderNos.Contains(dr.Field(Of Integer)("OrderNo"))).ToArray

    ' If this is enough for your needs, now you have an Array of DataRow containing 
    ' all rows in [allOrders] whose [OrderNo] field info is contained in the corresponding 
    ' field of any row in [cancelledOrders]
    Return CancelledOrderRows  
End Function

EDIT: This version hides unselected rows:

Sub FilterCancelledOrders(allOrdersView As DataGridView, cancelledOrders As DataTable, dgview as DataGridView)

    ' so far, same as before, but type String
    Dim CancelledOrderNos = cancelledOrders.Select.Select(
        Function(dr) dr.Field(Of String)("OrderNo")).ToArray

    ' iterate through DataGridView's rows
    For each dgvr as DataGridViewRow in dgview.Rows

        ' set row visibility according to your criterium
        dgvr.Visible = Not CancelledOrderNos.Contains(dgvr.Cells("OrderNo").Value)

    Next

    ' it's done!

End Sub

OR ELSE you can create a filtered datatable and set it as your DataGridView's datasource:

Function GetPositiveOrdersDataTable(allOrders As DataTable, cancelledOrders As DataTable) As DataTable

    Dim CancelledOrderNos = cancelledOrders.Select.Select(
        Function(dr) dr.Field(Of Integer)("OrderNo")).ToArray

    ' Collect all dataRows from allOrders where OrderNo is NOT contained in the Array of Integer we just got above:
    Dim positiveOrderRows = allOrders.Select.Where(
        Function(dr) Not CancelledOrderNos.Contains(dr.Field(Of Integer)("OrderNo"))).ToArray

    ' create empty table with same structure as allOrders
    Dim positiveOrders = allOrders.Clone()

    ' populate it with orders that were not cancelled
    For each dr in positiveOrderRows
        positiveOrders.ImportRow(dr)
    Next

    Return positiveOrders
End Function

Upvotes: 2

Related Questions