Help with filtering data using multiple selection from a listbox or Grid control using VB.NET

Hi again,

To clarify further, For example, If I select NMN, AGD and NSN columns from my GridA , I would like my GridB to only display records  where the values columns selected have two or more identical records.

Thanks,

Victor

I am confused, does your form have 2 grids, 1 combobox and 1 grid or 1 list box and 1 grid?
In your question title you have mentioned combo box, in the question text you have mentioned list box and in the last comment you have mentioned 2 grids (Grid A and Grid B).
What I understand from your question so far is you:
1> have a control (either combo or list box that shows all the SN names), the same data is also displayed in the only 1 grid that you have below the combo or list box.
2> user can select multiple values from combo or list box.
3> based on the selection in step 2, data in grid should get filtered to show only those rows with matching SN' names, and number of records should be 2 or more for those matching SN' names, only those should be show in the grid.
Is this understanding correct?

Ok, since you have not responded, I have gone as per your last comment, and assumed that you have two grids, at the top Grid B and at the bottom Grid A.
Grid A displays lot for records with columns, ID and SN (integer and string). There could be multiple rows with same SN value like below,
Grid A
1, ABC
2, DEF
3, ABC
4, DEF
5, GHI
6, XYZ
7. ABC
In Grid B, there is one column in which some strings are entered on each row.
Say user enters
ABC,
DEF,
GHI
And presses some button to filter the Grid A based on values entered in Grid B.
Now Grid A should only display. (Rows containing SN Values 2 more times from your question).
1, ABC
2, DEF
3, ABC
4, DEF
7. ABC
To achieve this below code is written, inline comments present in the code below.
I have also attached a sample program built in VB.NET in VS 2010.
1> To run it open the project and build it.
2> In top you shall see a grid, in which you can enter the filter criteria (values of grid B mentioned above). Just double click the row and enter the values ABC then press enter, second row DEF and enter and then GHI press enter
3> In the text box above the bottom grid enter comma separated values for Grid A like below, (just copy paste this value): ABC,DEF,ABC,DEF,GHI,XYZ,ABC
4> Then press Add to grid button, this shall add the comma separated values to the bottom grid.
5> Then press filter and you shall see that based on values entered in top grid, bottom grid would have filtered (hidden) and only show below values:
1, ABC
2, DEF
3, ABC
4, DEF
7. ABC
6> Press Reset to bring back all the rows in bottom grid (show all rows again)

Private Sub btnFilter_Click(sender As System.Object, e As System.EventArgs) Handles btnFilter.Click
        Dim filtersourcedatatable As DataTable = DataGridView2.DataSource 'Top grid in which filter criteria is entered

        Dim destdatatable As DataTable = DataGridView1.DataSource
        'First fire a LINQ query to get all the items in bottom grid that are present in top grid (filter criteria) and 
        'which have two or more values in the bottom grid (2 or more rows with identical SN values in bottom grid)
        'This query output shall be in form of SNVALUE, count, example below
        '1st Row: ABC, 2
        '2nd Row: DEF,3
        Dim query1 = From dt1 In destdatatable.AsEnumerable _
                    Join dt2 In filtersourcedatatable.AsEnumerable _
                    On dt1.Field(Of String)("SN") Equals dt2.Field(Of String)("Filter") _
                    Group By snValue = dt1.Field(Of String)("SN") _
                    Into totalCount = Count(dt1.Field(Of String)("SN") = dt2.Field(Of String)("Filter")) _
                    Where totalCount >= 2
        'Remove the count part from above query to only contain the required SN Value
        '1st Row: ABC,
        '2nd Row: DEF
        Dim strlist = From temp In query1 Select temp.snValue
        'Then fire a not in clause in data grid view at bottom to get all rows except the rows found in the first query so that they can be hidden
        'Basically get all rows in bottom grid that do not match in top grid or it matches but has only 1 row present
        Dim lastqry = From theRow As DataGridViewRow In DataGridView1.Rows _
                      Where Not strlist.Contains(theRow.Cells(1).Value.ToString)
                    Select theRow
        'Hide all the rows that are not present in the top grid or are present but have only 1 entry.
        For Each dvrow As DataGridViewRow In lastqry
            DataGridView1.CurrentCell = Nothing ' This is required to else row cannot be set to null since it might contain the current cell
            dvrow.Visible = False
        Next
        DataGridView1.Refresh()
    End Sub

Select allOpen in new window

Hope this is helpful.
VCharlesGrid_28894200.vbproj
Form1.vb
Form1.Designer.vb
Form1.resx
App.config

Hi,

Sorry for the late reply. Below is a clearer explanation.

I'm trying to select field names from Grid A and display in GridB two or more records that have the same values. The users would select rows from Grid A which are the fields names and the code should search the fields with two or more identical values ad display the results in Grid B.

For example if I select NSN and AGD from Grid A

The query should search for two or more records with identical values in NSN and AGD and display the records in Grid B.

The would like to use the AND logic, where records need to be identical in fields selected, I would also like to use the OR logic to display two or more records in  NSN "OR" AGD.

This feature is to help identify identical values using field names rather than entering specific values.

Thanks,

Victor

Thanks victor, I have implemented it for 2 or more identical records with and condition in the sample I shared, so please try it out and get back to me if you have more questions.
Regards,
karrtik

Hi again,

I just read your post, the same approach can be taken using a Grid to display the data I want to filter instead of filtering from a table but users need to select the field(s) from a Grid control to run the query.

Thanks,

Victor

Thanks, please let me know if you need anything else from my end to make this happen.

Hi,

In the Dim query1 section, I don't follow why you have "SN", the values filtered will depend on rows selected to run the query. I can't hard code any field names because users will choose which fields hey want to filter.

Thanks,

Victor

Hi,

When users select columns they will build a string, for example IF the rows are selected and s = 'NSN','AGD','FIF', can't figure out how to use the values in the s with an AND statement.

Victor

Hi again,

I'm using the code below to filter a C1TrueDBGrid based on column name (NSN) from a ComboBox. How do I modify it to work with multiple column names selected from another Grid?. For example I would like to select three rows from a Grid which contains the column names (NSN, AGD, NSC) and would like to filter my Grid where two or more records contain identical values for those  three columns. Is it possible to have a solution without hard coding the column names selected from the Grid? The users will  select different names from the Grid.

If RFilter.Checked Then
            Dim str As New StringBuilder
            str.Append("")
            For i As Integer = 0 To C1Screen1.Splits(0).Rows.Count - 1
                For j As Integer = i + 1 To C1Screen1.Splits(0).Rows.Count - 1
                    If C1Screen2(i, CBox1.Text) <> "" And C1Screen2(i, CBox1.Text) <> "N/A" Then
                        If C1Screen1(i, CBox1.Text) = C1Screen1(j, CBox1.Text) Then
                            If Not str.ToString.Contains(C1Screen1(i, CBox1.Text).ToString) Then
                                str.Append(C1Screen1(i, CBox1.Text) + ",")
                            End If
                            Exit For
                        End If
                    End If
                Next
            Next
            C1Screen1.Columns("NSN").FilterMultiSelect = True
            C1Screen1.Columns("NSN").FilterSeparator = ","
            C1Screen1.Columns("NSN").FilterText = str.ToString().Remove(str.Length - 1, 1)

        End If

Select allOpen in new window

I shall respond soon, give me sometime.

Karrtik,

Did you get a chance to look into a solution.

Victor

Sorry for the delay Victor, I was not keeping well yesterday, so I shall do it today, apologies once again for the delay.

Hi Victor, So far I have been able to do the AND Clause, OR clause is still pending. However wanted to share with you what I have come up with to find out if that was what you are looking for.
Please download the attached files.
1> open the VB project
2> Click on browse column and select an excel sheet (office 2010 or above). In the sample I have attached Sample.xls, please browse and select that file.
3> And click on load button.
4> Then in the top grid all the columns of bottom grid would be populated.
5> Now in the top grid, enter the filter criteria as comma separated for each column, say:
SN: 1,2
Name: ABC,DEF
6> Then press filter
7> The data in the bottom grid shall be filtered and only rows matching that criteria shall be shown.
Please note that right now even if 1 row matches this criteria it is shown.
Apologies once again for the delay.
Hope this is helpful.
Thanks,
karrtik
Form1.vb
Form1.Designer.vb
Form1.resx
Sample.xlsx
VCharlesGrid_28894200.vbproj
App.config

karrtik,

Thank you very much, will test it and get back to you. The main goal is to identify two or more records which contain identical records in selected columns.

Victor

Hi,

Is it possible to send me a zip file? I'm having trouble downloading your files.

Thanks,

Victor

Hi Victor,
Or operation is also implemented now. But even if one matching row is found it is displayed. I have not implemented the group by and count >=2 solution which I had implemented in my solution.(https://www.experts-exchange.com/questions/28894200/Help-with-filtering-data-using-multiple-selection-from-a-listbox-or-Grid-control-using-VB-NET.html?anchorAnswerId=41356099#a41356099). May be you can use the same and improve the zip code attached below to include that logic as well.

As per EE Policy I am unable to upload zip file, hence I have uploaded in Drop Box and shared the link below.

https://www.dropbox.com/s/coeojhqga6a3hlo/VCharlesGrid_28894200.zip?dl=0

Thanks and regards,
Karrtik

Hi,

I was able to run the application. Thank you for all the efforts.

Is there a way to select the columns instead of entering values to filter? For example if I select the two column and put them in a string variable s = "'SI#No','Name'"

Is there a way to filter records for identical values only for those two columns using the AND logic?  for example if my Grid has the following records.

SI#No             Name

1                           1
2                           5
4                            7
6                            8
9                            9

The filter should only display

SI#No             Name

1                           1
9                            9

I'm sorry for the confusion.

Thanks,

Victor

Yes, it is possible, in the top grid, instead of comma separated string, we can provide check box against each column.
Say in the top grid against SI and Name check box is clicked.
And on Filter button click, we shall get the columns against which check box is checked. And for that we can write a query to fetch all the rows where, SI and Name are same.  Is it okay if I just give you a query or do you want me to modify the app and give you the entire code?

Hi

The Query would be great.

Thank you.

Victor

Hi Victor, based on your earlier comments, I have modified the code to suit your requirement as illustrated in your comment . Please use the below code in Form1.vb (copy/paste into the project that I earlier shared or download the attached files and use them in the project I shared earlier) and use the attached sample xls for your testing which I have used.
I have added inline comments to the modified code.

Imports System.ComponentModel
Imports System.Linq
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Xml.Linq

Public Class Form1
    Private _xlFileName As String
    Private Sub SetUpFilterGrid()
        'Set up the second grid for entering the filter criteria
        If DataGridView2.Columns.Count = 2 Then
            Exit Sub
        End If
        DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        Dim dt As New DataTable

        Dim colname As New DataColumn
        colname.DataType = System.Type.GetType("System.String")
        colname.ColumnName = "Column Name"
        dt.Columns.Add(colname)

        Dim colfilter As New DataGridViewCheckBoxColumn()
        colfilter.HeaderText = "Filter Criteria"
        colfilter.Name = "colfilter"
        
        'Add columns in the bottom grid as rows so that user can enter the filter criteria.
        Dim btmgridcol As DataGridViewColumnCollection = DataGridView1.Columns
        For Each col As DataGridViewColumn In btmgridcol
            dt.Rows.Add(col.Name)
        Next
        DataGridView2.DataSource = dt
        DataGridView2.Columns.Add(colfilter)
    End Sub
    'Form the filter criteria and filter the XLS data
    Private Sub btnFilter_Click(sender As System.Object, e As System.EventArgs) Handles btnFilter.Click
        Dim destdatatable As DataTable = DataGridView1.DataSource
        'Get all columns to filter on
        Dim colNamestoFilter As List(Of String) = (From row As DataGridViewRow In DataGridView2.Rows Let checkedval = row.Cells(1).Value Let colname = row.Cells(0).Value Where checkedval = True Select colname).Cast(Of String)().ToList()
        If colNamestoFilter.Count < 2 Then 'At least 2 columns should be selected for filtering
            MsgBox("Please select at least 2 columns")
            Exit Sub
        End If
        'Get the values of first column (say SN) from all rows
        Dim lastqry As DataTable = destdatatable.Clone() ' So that lastqry gets all the columns
        lastqry.Clear() 'Clear if any rows has been copied, this should never happen but just in case
        'Import all rows of the dest table to last qry now the filter shall happen only the lastqry variable
        For Each orow As DataRow In destdatatable.Rows
            lastqry.ImportRow(orow)
        Next
        Dim prvcolname As String = colNamestoFilter(0) 'Initialiaze first col as the prev column
        For Each colname As String In colNamestoFilter
            If colname = prvcolname Then ' We need two columns to match at a time so no need to consider the first item
                Continue For
            End If
            Dim qry = From eachrow In lastqry _
                      Where eachrow.Item(colname).ToString().Equals(eachrow.Item(prvcolname).ToString()) _
                        Select eachrow
            lastqry = destdatatable.Clone() 'Reset lastqry to contain all the columns and empty the rows collection
            For Each drow As DataRow In qry
                lastqry.ImportRow(drow) ' Import all the rows from the first filter qry
            Next
            qry = Nothing
            prvcolname = colname
        Next

        Dim dtclone = destdatatable.Clone()
        dtclone.Clear()
        For Each drow As DataRow In lastqry.Rows
            dtclone.ImportRow(drow)
        Next

        DataGridView1.DataSource = dtclone
        DataGridView1.Sort(DataGridView1.Columns(0), ListSortDirection.Ascending)
        DataGridView1.Refresh()

    End Sub
    'Reset the bottom grid to original data, on click of reset button
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        LoadExcelSheet()
    End Sub
    'Don't allow column name editing in top row
    Private Sub DataGridView2_CellBeginEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DataGridView2.CellBeginEdit
        If e.ColumnIndex = 0 Then 'Don't allow editing on any rows of column name since it is coming from the bottom grid 
            e.Cancel = True
        End If
    End Sub
    'Read Excel and load the bottom grid
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        LoadExcelSheet()
    End Sub

    Private Sub LoadExcelSheet()

        Try
            Dim ds As DataSet = New DataSet()
            Dim conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " & "data source='" & _xlFileName & "';" & "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"" ")
            conn.Open()
            Dim dt As New DataTable()
            Dim sheetName As String = getSheetName(conn)
            Dim adap = New System.Data.OleDb.OleDbDataAdapter("select * from [" & sheetName & "]", conn)
            adap.Fill(dt)
            ds.Tables.Add(dt)
            conn.Close()
            DataGridView1.DataSource = dt
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            SetUpFilterGrid()
        Catch
            MsgBox(Err.Description)
        End Try
    End Sub
    'Browse button , open dialog to select XLS file
    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim openFileDialog1 As New OpenFileDialog()
        openFileDialog1.InitialDirectory = "."
        openFileDialog1.Filter = "XLS files (*.xls)|*.xls|All files (*.xlsx)|*.xlsx"
        openFileDialog1.FilterIndex = 2
        openFileDialog1.RestoreDirectory = True
        If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            _xlFileName = openFileDialog1.FileName
            TextBox1.Text = _xlFileName
        End If
    End Sub
    Private Function getSheetName(objConn As OleDbConnection) As String
        Dim strSheetName As String = String.Empty
        Dim dtSheetNames As DataTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
        If dtSheetNames.Rows.Count > 0 Then
            Dim rowCollection As DataRowCollection = dtSheetNames.Rows
            strSheetName = rowCollection(0).Item("TABLE_NAME")
        End If
        Return strSheetName
    End Function
End Class

Select allOpen in new window

Sample.xlsx
Form1.vb
Form1.Designer.vb
Form1.resx

Hi,

Thanks for the code, can you please send me the application using Drop Box? I'm having difficulties recreating the project.

Thanks,

Victor

Please use the link below to download the files and project.

https://www.dropbox.com/s/m5ru2qpe45thpql/VCharlesGrid_28894200_latest_12_10.zip?dl=0

Thank you.

Did you get get a chance to look at my solution (sample app) for your question coloring last 3 items of combo box? This was few days back.

Hi,

I just downloaded it, will get back to you soon.

Thanks again,

V.

Hi,

It's working, but I have to select two columns, how do you modify the code to also work when only choosing one column?

Thanks,

Victor

Check Filter button click method, there is a message to shown to select two columns.

Search for  MsgBox("Please select at least 2 columns") in the code.

Hi,

I meant I would like to be able to select one column. Users will also need to identify one column with duplicate values.

when I remove code below, the filter does not work/

'If colNamestoFilter.Count < 2 Then 'At least 2 columns should be selected for filtering
        'MsgBox("Please select at least 2 columns")
        'Exit Sub
        'End If

Also can't figure out how to modify the code when I use the following format when selecting multiple columns: "column1",'column2', 'columns3'.

Thanks,

Victor

1> For your first question, can you demonstrate using example like below on what should be the behavior when one column is selected?

For example if I select the two column and put them in a string variable s = "'SI#No','Name'"

Is there a way to filter records for identical values only for those two columns using the AND logic?  for example if my Grid has the following records.

SI#No             Name

1                           1
2                           5
4                            7
6                            8
9                            9

The filter should only display

SI#No             Name

1                           1
9                            9

Select allOpen in new window

2> Also can't figure out how to modify the code when I use the following format when selecting multiple columns: "column1",'column2', 'columns3'.
Karrtik: Sorry I am unable to understand the above question.

Hi,

I will get back to you in the morning.

Thanks,

Victor

Hi Karrtik,


I will replace SiNo# with LName to give a better explanation.

I would like to build a string when selecting the columns, for example if I select LName only s = 'LName' and if I select LName and FName, s = 'LName','FName' than use s to search the Grids. However if not possible to use in this approach I will use the checkbox approach.

In regards to filtering by 1 column, If I select 'LName' only, my filter should display records where two or more records have the same value under LName.

and when I select  'LName','FName' my filter should display records where two or more records have identical values in both columns (Using the AND) logic

When using the OR logic, my filter should display records where two or more records have identical values in either  columns.

For example if I only select 'LName' from TableA, the filter should return


LName             FName

 Charles             Marc
 Charles              Victor

if I select 'LName','FName' from TableA, using AND logic the filter should return

LName             FName

 Charles              Victor        
 Charles                Victor

if I select 'LName','FName' from TableA, using OR logic the filter should return

LName             FName

 Charles             Marc
 Charles              Victor
 Smith                  Bill
 Biden                   Bill



TableA:

LName             FName

 Charles             Marc
 Charles              Victor
 Smith                  Bill
 Biden                   Bill
 Adams                  John        
 Charles                Victor

 
The xml file in my project contains a lot more fields and data, hopefully my example was clear; I'm sorry for not being clear in my first explanation.

 Thanks,

 Victor

Hi,

I'm trying to use your code with loading an xml file in DataGridView1 instead of an excel file, all the columns in my xml file appear in DataGridView2, but when I select two columns with identical values, the records are not displayed in DatagridView1, any ideas why it is not working?

For example:

DataGridView1 contains:

SN            NASC
10411      9999
10411      9999
10511      8888
10521      7777

When I select SN and NASC from DataGridView2, the following should have appeared in DataGridView1:

SN            NASC
10411      9999
10411      9999

 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        Dim dtset1 As New DataSet
        Dim fs1 As System.IO.FileStream
        fs1 = New System.IO.FileStream(Application.StartupPath + "\AOP40.xml", IO.FileMode.Open)

        dtset1.ReadXml(fs1)
        ''  MsgBox(dtset1.Tables(0).Rows.Count)
        fs1.Close()
        Me.DataGridView1.DataSource = dtset1.Tables(0)
        SetUpFilterGrid()
    End Sub

Private Sub SetUpFilterGrid()
        'Set up the second grid for entering the filter criteria
        If DataGridView2.Columns.Count = 2 Then
            Exit Sub
        End If
        DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        Dim dt As New DataTable

        Dim colname As New DataColumn
        colname.DataType = System.Type.GetType("System.String")
        colname.ColumnName = "Column Name"
        dt.Columns.Add(colname)

        Dim colfilter As New DataGridViewCheckBoxColumn()
        colfilter.HeaderText = "Filter Criteria"
        colfilter.Name = "colfilter"
        
        'Add columns in the bottom grid as rows so that user can enter the filter criteria.
        Dim btmgridcol As DataGridViewColumnCollection = DataGridView1.Columns
        For Each col As DataGridViewColumn In btmgridcol
            dt.Rows.Add(col.Name)
        Next
        DataGridView2.DataSource = dt
        DataGridView2.Columns.Add(colfilter)
    End Sub

Select allOpen in new window

Thanks,

Victor

Hi Victor, Have not had chance to look at two of your last comments. (the latest and previous one). Shall take a look at it tomorrow. meanwhile would it possible for you to share the entire code that you have created so far by uploading all the files?.

Thanks,
Karrtik

Hi,

I am using the following code to filter the data.

  Private Sub btnFilter_Click(sender As System.Object, e As System.EventArgs) Handles btnFilter.Click
        Dim destdatatable As DataTable = DataGridView1.DataSource
        'Get all columns to filter on
        Dim colNamestoFilter As List(Of String) = (From row As DataGridViewRow In DataGridView2.Rows Let checkedval = row.Cells(1).Value Let colname = row.Cells(0).Value Where checkedval = True Select colname).Cast(Of String)().ToList()

        'If colNamestoFilter.Count < 2 Then 'At least 2 columns should be selected for filtering
        'MsgBox("Please select at least 2 columns")
        'Exit Sub
        'End If

        'Get the values of first column (say SN) from all rows
        Dim lastqry As DataTable = destdatatable.Clone() ' So that lastqry gets all the columns
        lastqry.Clear() 'Clear if any rows has been copied, this should never happen but just in case
        'Import all rows of the dest table to last qry now the filter shall happen only the lastqry variable
        For Each orow As DataRow In destdatatable.Rows
            lastqry.ImportRow(orow)
        Next
        Dim prvcolname As String = colNamestoFilter(0) 'Initialiaze first col as the prev column
        For Each colname As String In colNamestoFilter
            If colname = prvcolname Then ' We need two columns to match at a time so no need to consider the first item
                Continue For
            End If
            Dim qry = From eachrow In lastqry _
                      Where eachrow.Item(colname).ToString().Equals(eachrow.Item(prvcolname).ToString()) _
                        Select eachrow
            lastqry = destdatatable.Clone() 'Reset lastqry to contain all the columns and empty the rows collection
            For Each drow As DataRow In qry
                lastqry.ImportRow(drow) ' Import all the rows from the first filter qry
            Next
            qry = Nothing
            prvcolname = colname
        Next

        Dim dtclone = destdatatable.Clone()
        dtclone.Clear()
        For Each drow As DataRow In lastqry.Rows
            dtclone.ImportRow(drow)
        Next

        DataGridView1.DataSource = dtclone
        DataGridView1.Sort(DataGridView1.Columns(0), ListSortDirection.Ascending)
        DataGridView1.Refresh()

    End Sub

Select allOpen in new window

Hi,

Below is the xml file I'm using to test the code. It works with your excel file, but for some reason it doesn't work with the xml file. Based on the xml file below, when I select SN and NSN, DataGridView1 should have displayed:


SN               NSN
10411         9999
10411         9999
10421         9999
10421         9999

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<NewDataSet>
  <AOP40>
    <SN>10411</SN>
    <NSN>9999</NSN>
  </AOP40>
  <AOP40>
    <SN>10411</SN>
    <NSN>9999</NSN>
  </AOP40>
<AOP40>
    <SN>10811</SN>
    <NSN>9999</NSN>
  </AOP40>
  <AOP40>
    <SN>10421</SN>
    <NSN>9999</NSN>
  </AOP40>
<AOP40>
    <SN>10421</SN>
    <NSN>9999</NSN>
  </AOP40>
  <AOP40>
    <SN>10511</SN>
    <NSN>6666</NSN>
  </AOP40>
</NewDataSet>

Thanks,

Victor

Hi,

All the code used in the project are listed below:

1. Load Grids

Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        Dim dtset1 As New DataSet
        Dim fs1 As System.IO.FileStream
        fs1 = New System.IO.FileStream(Application.StartupPath + "\AOP40.xml", IO.FileMode.Open)

        dtset1.ReadXml(fs1)
        ''  MsgBox(dtset1.Tables(0).Rows.Count)
        fs1.Close()
        Me.DataGridView1.DataSource = dtset1.Tables(0)
        SetUpFilterGrid()
    End Sub

Private Sub SetUpFilterGrid()
        'Set up the second grid for entering the filter criteria
        If DataGridView2.Columns.Count = 2 Then
            Exit Sub
        End If
        DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        Dim dt As New DataTable

        Dim colname As New DataColumn
        colname.DataType = System.Type.GetType("System.String")
        colname.ColumnName = "Column Name"
        dt.Columns.Add(colname)

        Dim colfilter As New DataGridViewCheckBoxColumn()
        colfilter.HeaderText = "Filter Criteria"
        colfilter.Name = "colfilter"
        
        'Add columns in the bottom grid as rows so that user can enter the filter criteria.
        Dim btmgridcol As DataGridViewColumnCollection = DataGridView1.Columns
        For Each col As DataGridViewColumn In btmgridcol
            dt.Rows.Add(col.Name)
        Next
        DataGridView2.DataSource = dt
        DataGridView2.Columns.Add(colfilter)
    End Sub

2. Filter Data

Private Sub btnFilter_Click(sender As System.Object, e As System.EventArgs) Handles btnFilter.Click
        Dim destdatatable As DataTable = DataGridView1.DataSource
        'Get all columns to filter on
        Dim colNamestoFilter As List(Of String) = (From row As DataGridViewRow In DataGridView2.Rows Let checkedval = row.Cells(1).Value Let colname = row.Cells(0).Value Where checkedval = True Select colname).Cast(Of String)().ToList()

        'If colNamestoFilter.Count < 2 Then 'At least 2 columns should be selected for filtering
        'MsgBox("Please select at least 2 columns")
        'Exit Sub
        'End If

        'Get the values of first column (say SN) from all rows
        Dim lastqry As DataTable = destdatatable.Clone() ' So that lastqry gets all the columns
        lastqry.Clear() 'Clear if any rows has been copied, this should never happen but just in case
        'Import all rows of the dest table to last qry now the filter shall happen only the lastqry variable
        For Each orow As DataRow In destdatatable.Rows
            lastqry.ImportRow(orow)
        Next
        Dim prvcolname As String = colNamestoFilter(0) 'Initialiaze first col as the prev column
        For Each colname As String In colNamestoFilter
            If colname = prvcolname Then ' We need two columns to match at a time so no need to consider the first item
                Continue For
            End If
            Dim qry = From eachrow In lastqry _
                      Where eachrow.Item(colname).ToString().Equals(eachrow.Item(prvcolname).ToString()) _
                        Select eachrow
            lastqry = destdatatable.Clone() 'Reset lastqry to contain all the columns and empty the rows collection
            For Each drow As DataRow In qry
                lastqry.ImportRow(drow) ' Import all the rows from the first filter qry
            Next
            qry = Nothing
            prvcolname = colname
        Next

        Dim dtclone = destdatatable.Clone()
        dtclone.Clear()
        For Each drow As DataRow In lastqry.Rows
            dtclone.ImportRow(drow)
        Next

        DataGridView1.DataSource = dtclone
        DataGridView1.Sort(DataGridView1.Columns(0), ListSortDirection.Ascending)
        DataGridView1.Refresh()

    End Sub

Select allOpen in new window

Thanks Victor, now I have understood your requirements completely, good examples you have taken in your last post. I am working on it, give me sometime and I shall help you out in getting these results.

Karrtik,

I noticed when using the code to search by Text Entry from the initial project you sent me, the code related to Private Function GetIntersection(filtersourcedatatable as DataTable, ........) rerurns error message: Conversion from Type "DBNull to type 'String' is not valid.

on line: Dim filter as string = row.Item(1)

The error occurs when data searched is not in DataGridView1 and another search is attempted.

How do I fix this error?

Thanks,

Victor

Please disregard last message, it works when using Dim filter as string = row.Item(1).ToString.

Karrtik,

I think I know the reason why your code doesn't with my project. Is it possible to pass a column's name to "colnamestoFilter" code  by clicking on a checkbox in DataGridView2, instead of hardcoding the values in colnamestoFilter? The DataGridView2 has about 10 rows, users will select any combination.

For example:

Dim colnamestoFilter As List(of string) - (From row As DataGridViewRow in DataGridView2.Rows Let checkedVal = row.cells(0).value

will not work because users will not always click on the first row.

Thanks,

Victor

Hi Karrtik,

Do you have any information on a solution for this issue.

Thanks,

Victor

Hi Victor, apologies for the delay, yes I shall reply on your problem statement soon.
Thanks,
Karrtik

Thank You. I just want to clarify the columns selected by the users will vary because the table/Grid will have about 10 columns. If at all possible would be great if the code can also work when only selecting one column.

Thanks,

Victor

Sure Victor, thanks for the additional clarifications.

Hi Victor, based on your post above, consider a case below. In the cases mentioned below, either A & D are possible or B & C are possible. But A & C and B & D are not possible, so please select one of them.

your table contains: TableA:
LName             FName
 Charles             Marc
 Charles              Victor
 Smith                  Bill
 Biden                   Bill
 Adams                  John        
 Charles                Victor

Select allOpen in new window

A> For example if I only select 'LName' from TableA, the filter shall return (since there 3 records with LName count > =2)

LName             FName
 Charles             Marc
 Charles              Victor
 Charles              Victor

Select allOpen in new window

B> if your expectation is that it should only display, 2 rows like below

LName             FName
 Charles             Marc
 Charles              Victor

Select allOpen in new window

Then in the below case, it shall only display 1 row
C> if I select 'LName','FName' from TableA, using AND logic the filter shall return

LName             FName
 Charles              Victor        

Select allOpen in new window

D>It shall not display below, since to satisfy your requirement B above, distinct rows shall be removed.
if I select 'LName','FName' from TableA, using AND logic the filter should return

LName             FName
 Charles              Victor        
 Charles                Victor

Select allOpen in new window

Hi Victor, Based on the inputs provided by you in your comment: FinalRequirements, I have come with a document that describes how the application behaves in various scenarios, and at the bottom there is a zip that contains the source code for this implementation. Please refer the same. I hope with this you can take your project ahead :-)

Thanks and regards,
Karrtik
Victor_Charles_Steps_Filter_Grid.docx

Hi Karrtik,

I woild choose A&D, will read the link you sent me.

Thank You.

The word document attached has the source code at the end as zip file. Right now B&C is used, you just need to uncomment one line in source code with comment distinct at the end of filter function.

I meant comment not uncomment.

Hi,

Is it possible to post the project in Dropbox? Some of the contents in the word document are blocked and I can't see the code.

Thanks,

Victor

THANK YOU!

You are welcome, hope this helped and solved all your filtering requirements that you had mentioned in the question.

Thank you again, I will post another question regarding Combining AND and OR by having a column to select AND or OR on each row.

Hi Karrtik,

How do you use the AND or OR dropdown box when searching by text entry for multiple columns?

Thanks,

Victor

Well, the app and code I had shared in that top text box becomes a data grid view similar to one I had sent earlier with check boxes, but instead of check boxes it shall be a drop down now.
In the code on click of filter button new code has to be written to filter records based on both and or. Currently the one I shared is either AND of all columns or OR of all columns.
First I would say write use cases and test cases for various permutations of AND and OR for a given data set, what should be the result when the various combinations of filters are applied before writing any code.

Karrtik
I will look more into it, I am able to accomplish this using textboxes but did not atempt with with dropdown boxes., will share the code for text boxes when back in the office.
Thanks,

Victor

Hi Karrtik,

I tried using your code in another project but apparently I'm missing a reference because I receive the following errors:

Error      53      Expression of type 'System.Windows.Forms.DataGridViewCellCollection' is not queryable. Make sure you are not missing an assembly reference and/or namespace import for the LINQ provider.

Error      46      Type 'List' is not defined.  (In several lines)

Ho do I fix these errors?

code

Imports System.Xml
Imports System.ComponentModel

Public Class Form1A
    Private Rowcount As Integer = 1

    Private Sub btnBrowse_Click(sender As System.Object, e As System.EventArgs) Handles btnBrowse.Click


        Dim CheckBoxCol As New DataGridViewCheckBoxColumn
        With CheckBoxCol
            .Name = "CheckBoxColumn"
            .HeaderText = "Select"
            .Width = 40
        End With
        Me.DataGridView1.Columns.Insert(0, CheckBoxCol)

        ' Dim openFileDialog1 As New OpenFileDialog()
        ' openFileDialog1.InitialDirectory = "."
        ' openFileDialog1.Filter = "XML files (*.xml)|*.xml"
        ' openFileDialog1.FilterIndex = 2
        ' openFileDialog1.RestoreDirectory = True
        ' If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
        ' txtXMLPath.Text = openFileDialog1.FileName
        ' LoadXmlGrid()
        ' End If
    End Sub
    Private Sub LoadXmlGrid()
        Dim xmlFile As XmlReader
        xmlFile = XmlReader.Create(txtXMLPath.Text, New XmlReaderSettings())
        Dim ds As New DataSet
        ds.ReadXml(xmlFile)
        Rowcount = ds.Tables(0).Rows.Count
        DGVXMLData.DataSource = ds.Tables(0)
    End Sub

    Private Sub btnFilter_Click(sender As System.Object, e As System.EventArgs) Handles btnFilter.Click
        loadgrid() 'MsgBox(s)
        If s <> "" And DataGridView1.RowCount <> 0 Then
            Dim filtercriteria As String = s.Trim()
            'txtFilter.Text.Trim()
            '    s.Trim("N/A")
            Dim filteredcolnames As List(Of String) = filtercriteria.Split(",").ToList()
            'If filteredcolnames.Count <= 0 Or txtFilter.Text.Trim().Length <= 0 Then
            If filteredcolnames.Count <= 0 Or s.Length <= 0 Then
                MsgBox("No columns to filter!")
                Exit Sub
            End If
            Dim allcolnames As New List(Of String)
            For Each dgcol As DataGridViewColumn In DGVXMLData.Columns
                allcolnames.Add(dgcol.Name)
            Next
            Dim ds As DataTable = DGVXMLData.DataSource
            Dim viewtofilter As DataView = ds.DefaultView
            'And clause
            ' If rbAnd.Checked Then
            ds = GetForAndClause(filteredcolnames, ds, viewtofilter)
            'Else
            '   ds = GetForOrClause(filteredcolnames, ds, viewtofilter)
            '  End If
            ds = ds.DefaultView.ToTable(True, allcolnames.ToArray()) 'Karrtik: To make distinct rows if required
            DGVXMLData.DataSource = ds

            ' Create DataView
            Dim view As New DataView(DGVXMLData.DataSource)
            ' MsgBox(s)
            ' Sort by specified columns in ascending order
            ' view.Sort = "NSN  ASC, NAS ASC, FIF ASC"
            view.Sort = s
            DGVXMLData.DataSource = view
            DGVXMLData.Refresh()




            ' Dim dt As DataTable
            ' MsgBox(s)
            ' dt.DefaultView.Sort = " &  s & """
            ' DGVXMLData.DataSource = dt
            ' DGVXMLData.Sort(DGVXMLData.Columns("NSN"), ListSortDirection.Ascending)
            s = ""
        End If
    End Sub

    Private Function GetForOrClause(filteredcolnames As List(Of String), ds As DataTable, viewtofilter As DataView) As DataTable

        'Or clause
        Dim rowList As New List(Of DataRow)
        For Each colname In filteredcolnames
            Dim query1 = From dt1 In viewtofilter.Table _
                    Group By colToFilter = dt1.Field(Of String)(colname) _
                    Into totalCount = Count()
                    Where totalCount >= 2 Select colToFilter
            Dim rowcollection = From dt1 In viewtofilter.Table Where query1.Contains(dt1.Item(colname).ToString()) Select dt1
            rowList = rowcollection.Union(rowList.AsEnumerable()).ToList()
        Next
        If rowList.Count > 0 Then
            ds = rowList.AsEnumerable().CopyToDataTable()
        Else
            ds.Clear()
        End If
        Return ds
    End Function

    Private Function GetForAndClause(filteredcolnames As List(Of String), ds As DataTable, viewtofilter As DataView) As DataTable

        For Each colname In filteredcolnames
            Dim query1 = From dt1 In viewtofilter.Table _
                    Group By colToFilter = dt1.Field(Of String)(colname) _
                    Into totalCount = Count()
                    Where totalCount >= 2 Select colToFilter
            Dim rowcollection = From dt1 In viewtofilter.Table Where query1.Contains(dt1.Item(colname).ToString()) Select dt1
            If rowcollection.Count() > 0 Then
                ds = rowcollection.CopyToDataTable()
            Else
                ds.Clear()
            End If
            viewtofilter = ds.DefaultView
        Next
        Return ds
    End Function

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ' rbAnd.Checked = True

        Dim dtset1 As New DataSet
        Dim fs1 As System.IO.FileStream
        fs1 = New System.IO.FileStream(Application.StartupPath + "\AOP5F.xml", IO.FileMode.Open)

        dtset1.ReadXml(fs1)
        'MsgBox(dtset1.Tables(0).Rows.Count)
        fs1.Close()
        Me.DataGridView1.DataSource = dtset1.Tables(0)
        Me.DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        Me.DataGridView1.MultiSelect = True
        Dim dta As DataTable
        dta = dtset1.Tables(0)


        loadgrid()
    End Sub
    Private Sub loadgrid()

        Dim dtset1a As New DataSet
        Dim fs1a As System.IO.FileStream
        ' MsgBox("LLLLLLLL")
        fs1a = New System.IO.FileStream(Application.StartupPath + "\AOP5.xml", IO.FileMode.Open)

        dtset1a.ReadXml(fs1a)
        'MsgBox("LLL" & dtset1a.Tables(0).Rows.Count)
        fs1a.Close()
        Me.DGVXMLData.DataSource = dtset1a.Tables(0)
        Me.DGVXMLData.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        Me.DGVXMLData.MultiSelect = True
        Dim dtaa As DataTable
        dtaa = dtset1a.Tables(0)
    End Sub
    Private _selectedstrings As New List(Of String)
    Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick

        ' If e.RowIndex < 0 Then
        ' Exit Sub
        ' End If
        ' Dim currentcellstring As String = DataGridView1.Rows(e.RowIndex).Cells("SN").Value.ToString()
        ' If Not _selectedfields.Contains(currentcellstring) Then
        ' _selectedfields.Add(currentcellstring)
        ' _selectedvalues.Add(DataGridView1.Rows(e.RowIndex).Cells("NSN").Value.ToString())
        ' End If
        ' MsgBox(String.Join(",", _selectedfields))
        ' Dim selectedvaluesfinalstr As String
        ' Dim index As Integer = 0
        ' For Each val As String In _selectedvalues
        ' If String.IsNullOrEmpty(selectedvaluesfinalstr) Then
        ' selectedvaluesfinalstr = _selectedfields(index) & " like " & Val()
        ' Else
        ' selectedvaluesfinalstr = selectedvaluesfinalstr & " And " & _selectedfields(index) & " like " & Val()
        ' End If
        ' index = index + 1
        ' Next
        ' MsgBox(selectedvaluesfinalstr)

        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Dim currentcellstring As String = DataGridView1.Rows(e.RowIndex).Cells("Fields").Value.ToString()
        Dim currentcell As DataGridViewCell = DataGridView1.Rows(e.RowIndex).Cells("Fields")
        If currentcell.Selected Then
            If Not _selectedstrings.Contains(currentcellstring) Then
                _selectedstrings.Add(currentcellstring)
            End If
        Else
            If _selectedstrings.Contains(currentcellstring) Then
                _selectedstrings.Remove(currentcellstring)
            End If
        End If
        '   MsgBox(String.Join(",", _selectedstrings))
        s = String.Join(",", _selectedstrings)
        MsgBox(s)
    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    Private Sub DGVXMLData_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGVXMLData.CellContentClick

    End Sub

    Private Sub DGVXMLData_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DGVXMLData.CellFormatting
        Exit Sub
        If (e.RowIndex < 0 Or e.ColumnIndex < 0 Or e.RowIndex >= Rowcount) Then
            Exit Sub
        End If

        If DGVXMLData.Rows(e.RowIndex).Cells.Count <= 0 Then
            Exit Sub
        End If
        Dim svalobj = DGVXMLData.Rows(e.RowIndex).Cells(0)
        Dim snvalue As String = String.Empty
        If Not svalobj Is Nothing Then
            snvalue = svalobj.Value.ToString()
        End If
        Dim currrow As DataGridViewRow = DGVXMLData.Rows(e.RowIndex)
        Dim celltext As String = DGVXMLData.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString()
        Dim currcell As DataGridViewCell = DGVXMLData.Rows(e.RowIndex).Cells(e.ColumnIndex)
        Dim qry = From row As DataGridViewRow In DGVXMLData.Rows Where Not row.Cells(0).Value Is Nothing AndAlso row.Cells(0).Value.ToString() = snvalue AndAlso row.Index <> currrow.Index Select row
        For Each rw As DataGridViewRow In qry
            Dim cells = From cell As DataGridViewCell In rw.Cells Where Not cell.Value Is Nothing AndAlso cell.Value.ToString() <> celltext AndAlso cell.ColumnIndex <> currcell.ColumnIndex Select cell
            '    Dim cells = From cell As DataGridViewCell In rw.Cells Where Not cell.Value Is Nothing AndAlso cell.Value.ToString() = celltext AndAlso cell.ColumnIndex = currcell.ColumnIndex AndAlso cell.Value.ToString().Trim() <> "" AndAlso cell.Value.ToString() <> "N/A" Select cell
            For Each cell As DataGridViewCell In cells
                If cell.ColumnIndex = 0 Then Continue For
                'cell.Style.BackColor = Color.Green
                'cell.Style.ForeColor = Color.White
                'currcell.Style.BackColor = Color.Green
                'currcell.Style.ForeColor = Color.White

                cell.Style.BackColor = Color.Red
                cell.Style.ForeColor = Color.White
                currcell.Style.BackColor = Color.Red
                currcell.Style.ForeColor = Color.White
            Next
        Next
    End Sub
    Private Sub ChangeColor()
        For Each row As DataGridViewRow In DGVXMLData.Rows
            For Each cell As DataGridViewCell In row.Cells
            Next
        Next
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs)
        '    Dim Rowcount As Integer = 1
        ChangeColor()
        Exit Sub
        DGVXMLData.Columns.Add("SN", "SN")
        DGVXMLData.Columns.Add("AGD", "AGD")
        DGVXMLData.Columns.Add("FIF", "FIF")
        DGVXMLData.Columns.Add("NAS", "NAS")
        DGVXMLData.Columns.Add("TEP", "TEP")

        DGVXMLData.Rows.Add("10411", "AAA", "BBB", "CCC", "DDD")
        DGVXMLData.Rows.Add("10411", "AAA", "EEE", "NNN", "DDD")
        DGVXMLData.Rows.Add("10411", "EEE", "CCC", "UUU", "KKK")
        DGVXMLData.Rows.Add("10411", "ANA", "BBB", "FRF", "DDD")
        DGVXMLData.Rows.Add("10521", "AAA", "BBB", "NNN", "UUU")
        DGVXMLData.Rows.Add("10521", "AAA", "EEE", "LLL", "OOO")
        Rowcount = 6 'since we added 6 rows.

    End Sub

    Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged



        Try
            If DataGridView1.Columns(e.ColumnIndex).Name = "CheckBoxColumn" Then

                Dim checkCell As DataGridViewCheckBoxCell = _
                    CType(DataGridView1.Rows(e.RowIndex).Cells("CheckBoxColumn"),  _
                    DataGridViewCheckBoxCell)
                If CType(checkCell.Value, [Boolean]) = True Then
                    For i As Integer = 0 To Me.DataGridView1.Columns.Count - 1
                        DataGridView1.Rows(e.RowIndex).Cells(i).Style.BackColor = Color.Red
                        DataGridView1.Rows(e.RowIndex).Cells(i).Style.SelectionBackColor = Color.Red
                    Next
                Else
                    For j As Integer = 0 To Me.DataGridView1.Columns.Count - 1
                        DataGridView1.Rows(e.RowIndex).Cells(j).Style.BackColor = Color.White
                        DataGridView1.Rows(e.RowIndex).Cells(j).Style.SelectionBackColor = Color.White
                    Next
                End If

                DataGridView1.Invalidate()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub DataGridView1_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
        If DataGridView1.IsCurrentCellDirty Then
            DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
        End If

    End Sub

    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click

    End Sub
End Class

Select allOpen in new window

Hi Victor,
Can you add below import statements to the top of your VB form.
1> Add an Import statement for the System.Linq, System.Data.Linq, or System.Xml.Linq namespace to your code file

Hi,

For some reason I was not able to add "imports System.Data.Linq" on top of the Form, but I am not only getting multiple errors listed as :


Error      46      Type 'List' is not defined.  (In several lines)

How do I get rid of that error?

Thanks,

Victor

Can you right click on the project in solution Explorer and click on add reference, go to .net tab, and ensure reference to system, system.core and system.data are added to this project?

For some reason, I could not add system.core, received message it is aleady included when attempting to add it in my reference, ut it does not appear in my reference.

Ok, after adding these references, did any of the errors go away?

Hi,

For some reason I am not able to add system.core as a reference.

Received error message:

A reference to system.core could noy be added, this component is already automatically referenced by the build system.

I have 6 errors with same message:

Type 'List' is not defined.

Victor