[SOLVED] Trouble with Filtering a .NET DataTable

Trouble with Filtering a .NET DataTable

I have the following code:

myDataSet.myTable.DefaultView.RowFilter = "Code <> 'X'"

After the line executes, I can see the updated result where no rows contain an X for the Code column, if I hover over DefaultView. But, when hovering over myDataSet.myTable the table still contains records where Code = 'X'.

What would I need to do in order to make the myDataSet.myTable be updated with the results of the filter applied?

Solution

Well, as pointed out, the "view" is in effect another "view" into that data table. Rather similar as to when you use a SQL query against the database. The result of that query is a "view" into the database.

So, you are free to set the "default" view on a data table, and it will filter to that view. And thus you can get BOTH a count of table rows, and a count of filter rows.

And, you can "copy" the results into a new table object if you wish.

This example shows this in action:

    Dim dtHotels As DataTable

    Dim strSQL As String =
        "SELECT * FROM tblHotelsA
        WHERE Active = 1
        ORDER BY HotelName"

    dtHotels = MyRst(strSQL)

    dtHotels.DefaultView.RowFilter = "[City] = 'Edmonton'"

    Dim MyFilterTable As DataTable = dtHotels.DefaultView.ToTable()

    Debug.Print($"dtHotels rows = {dtHotels.Rows.Count}")
    Debug.Print($"dtHotels view filter rows = {dtHotels.DefaultView.Count}")
    Debug.Print($"Filter table (copy) = {MyFilterTable.Rows.Count}")

Output:

dtHotels rows = 16
dtHotels view filter rows = 8
New Filter table (copy) = 8

So, the view is much in effect like a "query" against that data table, and the row count, and original rows still exist in that data table.