Help with filtering records in C1TrueDBGrid

Posted by: Victor.m.charles.civ on 2 April 2020, 2:45 pm EST

Please login to follow topic

Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 2 April 2020, 2:45 pm EST

Hello,

I have a Grid with the following 2 columns and would like to filter records where only countries selected are in the same SN column, How do I achieve this using VB.NET

For example if Grid3 contains

SN CTRY

10411 BEL

10411 USA

10411 DNK

10421 USA

10421 DNK

10421 CAN

10431 BEL

10432 USA

10432 BEL

And I select BEL and USA from Grid1 (‘BEL’,‘USA’)

Grid3 should only display the following records:

10411 BEL

10411 USA

10432 USA

10432 BEL

And if I select SN (10411) from Grid2 and Countries (‘BEL’, ‘USA’) from Grid1, Grid3 should only display the following records:

10411 BEL

10411 USA

If possible please send me the solution in a sample project.

Thanks,

Victor
prabhat.sharma
- Post Options:
- Link
  Copy
Posted 3 April 2020, 1:03 am EST

Hi Victor,

To create the sample we need the following information:

1: What data do Grid1 and Grid2 contains?

2: What is the logic behind the given scenario?

Grid3 should only display the following records:

10411 BEL

10411 USA

10432 USA

10432 BEL

Regards,

Prabhat Sharma.
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 3 April 2020, 2:28 am EST

Hello Prabhat,

Enclosed is a sample project with more information, what I am trying to accomplish is commented in the Button click event. The main difference is I need to filter the chapters in Grid3 using the first three characters in the chapter column if Grid1.

Thanks,

VictorWindowsApp1.zip
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 3 April 2020, 3:28 am EST
Hello Prabhat,

Below is some clarification.

'Cross check values in C1TrueDBGrid3 using the following two options for selected chapters and countries

(1) Using option for Radiobutton1 (OR Logic)

Code for selecting Chapert(s) only

When I select 104 in Grid1, Grid3 should only display records where SN starts with 104.

Code for selecting Chapert(s) and Country (ies)

When I select 104 and USA, Grid3 should only display records where SN starts with 104 and Country includes USA.

.

Code for selecting Country(ies) Only

When I select USA, BEL. Grid3 should only display records where Country includes BEL, USA.

'(2) Using option for Radiobutton2 (AND Logic) 'Code for selecting Chapert(s) and Country (ies)

When I select 104 AND USA, BEL. Grid3 should only display records where both Countries includes are included in all SN stating with 104.

'Code for selecting Country(ies) Only

When I select USA, BEL. Grid3 should only display records where both Countries includes are included in the same SN.

I would like to achieve the above with limited code by loading Grid3 with all the data and filter records using the scenarios mentioned above.

Thanks,

Victor
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 4 April 2020, 1:36 am EST

Hello Prabhat,

The solution is option 2 below is the most important in this case. But it would be great if you can send me a solution for both options 1 and 2.

'(2) Using option for Radiobutton2 (AND Logic)

'Code for selecting Chapert(s) and Country (ies)

When I select 104 AND USA, BEL. Grid3 should only display records where both Countries are included in all SN with 104 (e.g. 10411, 10412, 10421).

'Code for selecting Country(ies) Only

When I select USA, BEL. Grid3 should only display all records where both Countries are included in the same SN

Thanks,

Victor

prabhat.sharma

Post Options:
Link
Copy

Posted 5 April 2020, 8:47 pm EST

Hi Victor,

I have updated the sample as per your comments, please have a look into that.

The code of the button click event is given below:

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

        If RadioButton2.Checked Then
            Dim xyz As String = ""
            If C1TrueDBGrid1.SelectedRows.Count = 0 Then
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    xyz = xyz + "[" + C1TrueDBGrid3.Columns("SCOUNTRY").Caption + "]='" + C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row)) + "' OR"
                Next
            Else
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                        xyz = xyz + "[" + C1TrueDBGrid3.Columns("SCOUNTRY").Caption + "]='" + C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row)) + "' AND [" + C1TrueDBGrid3.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                    Next
                Next
            End If
            xyz = xyz.Remove(xyz.Length - 3, 3)
            Console.WriteLine(xyz)
            fsLinkdata = New System.IO.FileStream(Application.StartupPath + "\AOP5D.xml", IO.FileMode.Open)
            dtsetLinkdata.Clear()
            dtsetLinkdata.ReadXml(fsLinkdata)
            fsLinkdata.Close()
            FilteredDTA = dtsetLinkdata.Tables(0)
            Dim view As DataView
            view = New DataView(FilteredDTA)
            view.RowFilter = xyz
            C1TrueDBGrid3.DataSource = view
        End If
        If RadioButton1.Checked Then
            Dim xyz As String = ""
            If C1TrueDBGrid2.SelectedRows.Count = 0 Then
                For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                    xyz = xyz + "[" + C1TrueDBGrid3.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                Next
            ElseIf C1TrueDBGrid1.SelectedRows.Count = 0 Then
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    xyz = xyz + "[" + C1TrueDBGrid3.Columns("SCOUNTRY").Caption + "]='" + C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row)) + "' OR"
                Next
            Else
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                        xyz = xyz + "[" + C1TrueDBGrid3.Columns("SCOUNTRY").Caption + "]='" + C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row)) + "' OR [" + C1TrueDBGrid3.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                    Next
                Next
            End If
            xyz = xyz.Remove(xyz.Length - 3, 3)
            Console.WriteLine(xyz)
            fsLinkdata = New System.IO.FileStream(Application.StartupPath + "\AOP5D.xml", IO.FileMode.Open)
            dtsetLinkdata.Clear()
            dtsetLinkdata.ReadXml(fsLinkdata)
            fsLinkdata.Close()
            FilteredDTA = dtsetLinkdata.Tables(0)
            Dim view As DataView
            view = New DataView(FilteredDTA)
            view.RowFilter = xyz
            C1TrueDBGrid3.DataSource = view
        End If
    End Sub

Regards,

Prabhat Sharma.

WindowsApp.zip

Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 5 April 2020, 10:54 pm EST

Hello Prabhat,

Thank you for the solution but unfortunately it’s not working properly, as shown in enclosed file, [Radiobutton1 Selection] when I select 104 with BEL and USA, only records containing BEL and USA for al records starting with 104 in the SN column should be displayed. Also SCOUNTRY does not need to be included in the code, it’s just a hidden column in the Grid. If I select Radiobutton2 for same BEL and USA selection all records where BEL and USA for records in the same SN (e.g. 10411) should be displayed.

Thanks,

VictorSlideA.zip
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 6 April 2020, 1:15 am EST

Hello Prabhat,

Enclosed is a simplified version of the sample project. As mentioned SCOUNTRY has no impact on my search, only SN and COUNTRY IN Grid2 and CHPT IN Grid1. CHPT should be linked with SN by the first 3 digits.

Thanks,

Victor

WindowsApp1A.zip

prabhat.sharma

Post Options:
Link
Copy

Posted 6 April 2020, 7:21 pm EST

Hi Victor,

Thank you for the modified sample.

I have updated your last sample as per your comments, please have a look into it and let us know if you have any doubt.

  Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim FilteredDTA  As DataTable
        If RadioButton2.Checked Then
            Dim xyz As String = ""
            If C1TrueDBGrid1.SelectedRows.Count = 0 Then
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    Dim str As String
                    If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                        str = "BEL"
                    Else
                        str = "USA"
                    End If
                    xyz = xyz + "[" + C1TrueDBGrid3.Columns("COUNTRY").Caption + "]='" + str + "' OR"
                Next
            Else
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                        Dim str As String
                        If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                            str = "BEL"
                        Else
                            str = "USA"
                        End If
                        xyz = xyz + "[" + C1TrueDBGrid3.Columns("COUNTRY").Caption + "]='" + str + "' AND [" + C1TrueDBGrid3.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                    Next
                Next
            End If
            xyz = xyz.Remove(xyz.Length - 3, 3)
            Console.WriteLine(xyz)
            fsLinkdata = New System.IO.FileStream(Application.StartupPath + "\AOP5D.xml", IO.FileMode.Open)
            dtsetLinkdata.Clear()
            dtsetLinkdata.ReadXml(fsLinkdata)
            fsLinkdata.Close()
            FilteredDTA = dtsetLinkdata.Tables(0)
            Dim view As DataView
            view = New DataView(FilteredDTA)
            view.RowFilter = xyz
            C1TrueDBGrid3.DataSource = view
        End If
        If RadioButton1.Checked Then
            Dim xyz As String = ""
            If C1TrueDBGrid2.SelectedRows.Count = 0 Then
                For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                    xyz = xyz + "[" + C1TrueDBGrid3.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                Next
            ElseIf C1TrueDBGrid1.SelectedRows.Count = 0 Then
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    Dim str As String
                    If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                        str = "BEL"
                    Else
                        str = "USA"
                    End If
                    xyz = xyz + "[" + C1TrueDBGrid3.Columns("COUNTRY").Caption + "]='" + str + "' OR"
                Next
            Else
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    Dim str As String
                    If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                        str = "BEL"
                    Else
                        str = "USA"
                    End If
                    For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                        xyz = xyz + "[" + C1TrueDBGrid3.Columns("COUNTRY").Caption + "]='" + str + "' AND [" + C1TrueDBGrid3.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                    Next
                Next
            End If
            xyz = xyz.Remove(xyz.Length - 3, 3)
            Console.WriteLine(xyz)
            fsLinkdata = New System.IO.FileStream(Application.StartupPath + "\AOP5D.xml", IO.FileMode.Open)
            dtsetLinkdata.Clear()
            dtsetLinkdata.ReadXml(fsLinkdata)
            fsLinkdata.Close()
            FilteredDTA = dtsetLinkdata.Tables(0)
            Dim view As DataView
            view = New DataView(FilteredDTA)
            view.RowFilter = xyz
            C1TrueDBGrid3.DataSource = view
        End If
    End Sub

Regards,

Prabhat Sharma.

prabhat.sharma
- Post Options:
- Link
  Copy
Posted 6 April 2020, 7:27 pm EST

WindowsApp1A_modified.zip
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 6 April 2020, 9:49 pm EST

Hello Prabhat,

Thank you for the solution, is it possible to avoid hard coding the countryname? My actual project contains over 20 countries. For example can we use the country abbreviation directly since included in the Grid eventhough invisible?

Thanks,

Victor
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 6 April 2020, 10:26 pm EST

Hello Prabhat,

The countries table contains the columns below, when I select countries, can I automatically set str = to the country column’s data instead of using multiple if statements to set value to str?
1 BEL BELGIUM 13
Thanks,

Victor
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 6 April 2020, 10:38 pm EST

Hello Prabhat,

Can we use a similar approach to code below to set value to Str?

‘RETREIVE CHAPTER DATA IN C1TRUEDBgrid2

Dim Srow1 As Integer

Dim vc12 As Integer

vc12 = 0

For Each Srow1 In Me.C1TrueDBGrid2.SelectedRows

If vc12 > 0 Then

xyzc = xyzc & “,” & "’" & Me.C1TrueDBGrid2.Columns(1).CellText(Srow1) & “'”

Else

xyzc = “'” & Me.C1TrueDBGrid2.Columns(1).CellText(Srow1) & “'”

End If

vc12 = vc12 + 1

Next

Thanks,

Victor
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 6 April 2020, 11:21 pm EST

Hello Prabhat,

The code seems to work, will try it in my project and get back to you.

str = C1TrueDBGrid2.Columns(“COUNTRY”).CellText(C1TrueDBGrid2.SelectedRows(row))

Thanks,

Victor
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 7 April 2020, 2:02 am EST

Hello Prabhat,

I spoke too soon, it’s not working properly when setting:

str = C1TrueDBGrid2.Columns(“COUNTRY”).CellText(C1TrueDBGrid2.SelectedRows(row))

Can you please send me the correct solution.

Thanks,

Victor
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 7 April 2020, 2:22 am EST

Hello Prabhat,

When I select Radiobutton2 using your code, it’s still not working properly. For example when I select 104 with BEL and USA. The data for USA in SN 10413 is still displayed even though BEL does not have any data in 10413.

Enclosed is another sample with data included for 10413 for USA.

Victor

WindowsApp1A.zip
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 7 April 2020, 4:08 am EST

Hello Prabhat,

Regarding code for Radiobutton2, another option may be to keep the same code and use additional code to remove all records where only one country is available for an SN.

Victor

Victor.m.charles.civ

Post Options:
Link
Copy

Posted 7 April 2020, 5:37 am EST

Hello Prabhat,

I’m also receiving the following error message:

System.InvalidOperationException: ‘Not supported when the grid is bound’

When trying to include number of records from the Grid in a textbox.

TextBox1.Text = C1Screen1.Rows.Count

Code:

    If Both.Checked Then
        Dim xyz As String = ""
        If C1TrueDBGrid2.SelectedRows.Count = 0 Then
            For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                xyz = xyz + "[" + C1Screen1.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
            Next
        ElseIf C1TrueDBGrid1.SelectedRows.Count = 0 Then
            For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                Dim str As String
                str = C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row))
                xyz = xyz + "[" + C1Screen1.Columns("COUNTRY").Caption + "]='" + str + "' OR"
            Next
        Else
            For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                Dim str As String
                str = C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row))
                For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                    xyz = xyz + "[" + C1Screen1.Columns("COUNTRY").Caption + "]='" + str + "' AND [" + C1Screen1.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                Next
            Next
        End If
        xyz = xyz.Remove(xyz.Length - 3, 3)
        Console.WriteLine(xyz)
        fsLinkdata = New System.IO.FileStream(Application.StartupPath + "\AOP40.xml", IO.FileMode.Open)
        dtsetLinkdata.Clear()
        dtsetLinkdata.ReadXml(fsLinkdata)
        fsLinkdata.Close()
        FilteredDTA = dtsetLinkdata.Tables(0)
        Dim view As DataView
        view = New DataView(FilteredDTA)
        view.RowFilter = xyz
        C1Screen1.DataSource = view
        TextBox1.Text = C1Screen1.Rows.Count *****Error
    End If

How do I avoid this error?

Thanks,

Victor

Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 7 April 2020, 7:16 am EST

Hello Prabhat,

I was able to avoid the error with the following code:

TextBox1.Text = view.RowFilter.Count

Victor
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 7 April 2020, 10:49 am EST

Hello Prabhat,

Below is the latest code I’m using, you notice in the enclosed slide, row with SN=10413 should not be displayed when selection Radiobutton2 because two or more nations should have data in the same SN.

Dim FilteredDTA As DataTable

If RadioButton2.Checked Then

Dim xyz As String = “”

If C1TrueDBGrid1.SelectedRows.Count = 0 Then

For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1

Dim str As String

’ If C1TrueDBGrid2.Columns(“COUNTRYNAME”).CellText(C1TrueDBGrid2.SelectedRows(row)) = “BELGIUM” Then

’ str = “BEL”

’ Else

’ str = “USA”

‘End If

str = C1TrueDBGrid2.Columns(“COUNTRY”).CellText(C1TrueDBGrid2.SelectedRows(row))

xyz = xyz + “[” + C1TrueDBGrid3.Columns(“COUNTRY”).Caption + "]=’" + str + “’ OR”

Next

Else

For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1

For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1

Dim str As String

’ If C1TrueDBGrid2.Columns(“COUNTRYNAME”).CellText(C1TrueDBGrid2.SelectedRows(row)) = “BELGIUM” Then

’ str = “BEL”

’ Else

’ str = “USA”

’ End If

str = C1TrueDBGrid2.Columns(“COUNTRY”).CellText(C1TrueDBGrid2.SelectedRows(row))

xyz = xyz + “[” + C1TrueDBGrid3.Columns(“COUNTRY”).Caption + “]='” + str + “’ AND [” + C1TrueDBGrid3.Columns(“SN”).Caption + “] LIKE’” + C1TrueDBGrid1.Columns(“CHPT”).CellText(C1TrueDBGrid1.SelectedRows(row1)) + “%’ OR”

Next

Next

End If

xyz = xyz.Remove(xyz.Length - 3, 3)

Console.WriteLine(xyz)

fsLinkdata = New System.IO.FileStream(Application.StartupPath + “\AOP5D.xml”, IO.FileMode.Open)

dtsetLinkdata.Clear()

dtsetLinkdata.ReadXml(fsLinkdata)

fsLinkdata.Close()

FilteredDTA = dtsetLinkdata.Tables(0)

Dim view As DataView

view = New DataView(FilteredDTA)

view.RowFilter = xyz

C1TrueDBGrid3.DataSource = view

End If

If RadioButton1.Checked Then

Dim xyz As String = “”

If C1TrueDBGrid2.SelectedRows.Count = 0 Then

For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1

xyz = xyz + “[” + C1TrueDBGrid3.Columns(“SN”).Caption + “] LIKE’” + C1TrueDBGrid1.Columns(“CHPT”).CellText(C1TrueDBGrid1.SelectedRows(row1)) + “%’ OR”

Next

ElseIf C1TrueDBGrid1.SelectedRows.Count = 0 Then

For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1

Dim str As String

‘If C1TrueDBGrid2.Columns(“COUNTRYNAME”).CellText(C1TrueDBGrid2.SelectedRows(row)) = “BELGIUM” Then

’ str = “BEL”

’ Else

’ str = “USA”

’ End If

str = C1TrueDBGrid2.Columns(“COUNTRY”).CellText(C1TrueDBGrid2.SelectedRows(row))

xyz = xyz + “[” + C1TrueDBGrid3.Columns(“COUNTRY”).Caption + "]=’" + str + “’ OR”

Next

Else

For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1

Dim str As String

’ If C1TrueDBGrid2.Columns(“COUNTRYNAME”).CellText(C1TrueDBGrid2.SelectedRows(row)) = “BELGIUM” Then

’ str = “BEL”

’ Else

’ str = “USA”

’ End If

str = C1TrueDBGrid2.Columns(“COUNTRY”).CellText(C1TrueDBGrid2.SelectedRows(row))

’ MsgBox(str)

For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1

xyz = xyz + “[” + C1TrueDBGrid3.Columns(“COUNTRY”).Caption + “]='” + str + “’ AND [” + C1TrueDBGrid3.Columns(“SN”).Caption + “] LIKE’” + C1TrueDBGrid1.Columns(“CHPT”).CellText(C1TrueDBGrid1.SelectedRows(row1)) + “%’ OR”

Next

Next

End If

xyz = xyz.Remove(xyz.Length - 3, 3)

Console.WriteLine(xyz)

fsLinkdata = New System.IO.FileStream(Application.StartupPath + “\AOP5D.xml”, IO.FileMode.Open)

dtsetLinkdata.Clear()

dtsetLinkdata.ReadXml(fsLinkdata)

fsLinkdata.Close()

FilteredDTA = dtsetLinkdata.Tables(0)

Dim view As DataView

view = New DataView(FilteredDTA)

view.RowFilter = xyz

C1TrueDBGrid3.DataSource = view

End If

End Sub

Thanks,

Victor

ScreenshotB.zip

prabhat.sharma

Post Options:
Link
Copy

Posted 8 April 2020, 12:27 am EST

Hi Victor,

To make the AND filter work as you want use the code snippet given below:

Dim FilteredDTA As DataTable
        If RadioButton2.Checked Then
            Dim xyz As String = ""
            If C1TrueDBGrid1.SelectedRows.Count = 0 Then
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    Dim str As String
                    If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                        str = "BEL"
                    Else
                        str = "USA"
                    End If
                    xyz = xyz + "[" + C1TrueDBGrid3.Columns("COUNTRY").Caption + "]='" + str + "' OR"
                Next
            Else
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                        Dim str As String
                        If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                            str = "BEL"
                        Else
                            str = "USA"
                        End If
                        xyz = xyz + "[" + C1TrueDBGrid3.Columns("COUNTRY").Caption + "]='" + str + "' AND [" + C1TrueDBGrid3.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                    Next
                Next
            End If
            xyz = xyz.Remove(xyz.Length - 3, 3)
            Console.WriteLine(xyz)
            fsLinkdata = New System.IO.FileStream(Application.StartupPath + "\AOP5D.xml", IO.FileMode.Open)
            dtsetLinkdata.Clear()
            dtsetLinkdata.ReadXml(fsLinkdata)
            fsLinkdata.Close()
            FilteredDTA = dtsetLinkdata.Tables(0)
            Dim view As DataView
            view = New DataView(FilteredDTA)
            view.RowFilter = xyz
            C1TrueDBGrid3.DataSource = view
            Dim visible = True
            For row = 0 To C1TrueDBGrid3.Splits(0).Rows.Count - 1
                visible = False
                For row1 = 0 To C1TrueDBGrid3.Splits(0).Rows.Count - 1
                    If row <> row1 Then
                        If C1TrueDBGrid3.Columns("SN").CellValue(row) = C1TrueDBGrid3.Columns("SN").CellValue(row1) Then
                            visible = True
                            Exit For
                        End If
                    End If
                Next
                C1TrueDBGrid3.Splits(0).Rows(row).Visible = visible
            Next
        End If

If you have any other query then please share an appropriate example with the desired output so that we can assist you accordingly.

Regards,

Prabhat Sharma.

Victor.m.charles.civ

Post Options:
Link
Copy

Posted 8 April 2020, 5:08 am EST

Hello Prabhat,

I’m using the code below in my project but my Grid is still displaying records where only one nation has information under an SN (e.g. 10411). The only change made was in str and I renamed C1TrueDDGrid3 to C1Screen1. Any idea why it’s not working in my project? I still have the same issue when I use your approach for str, for just BEL and USA.

Code:

    Dim FilteredDTA As DataTable
    If Interchange.Checked Then
        Dim xyz As String = ""
        If C1TrueDBGrid1.SelectedRows.Count = 0 Then
            For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                Dim str As String
                '        If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                '       str = "BEL"
                '      Else
                '     str = "USA"
                'End If
                str = C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row))
                xyz = xyz + "[" + C1Screen1.Columns("COUNTRY").Caption + "]='" + str + "' OR"
            Next
        Else
            For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                    Dim str As String
                    'If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                    ' str = "BEL"
                    ' Else
                    ' str = "USA"
                    ' End If
                    str = C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row))
                    xyz = xyz + "[" + C1Screen1.Columns("COUNTRY").Caption + "]='" + str + "' AND [" + C1Screen1.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                Next
            Next
        End If
        xyz = xyz.Remove(xyz.Length - 3, 3)
        Console.WriteLine(xyz)
        fsLinkdata = New System.IO.FileStream(Application.StartupPath + "\AOP40.xml", IO.FileMode.Open)
        dtsetLinkdata.Clear()
        dtsetLinkdata.ReadXml(fsLinkdata)
        fsLinkdata.Close()
        FilteredDTA = dtsetLinkdata.Tables(0)
        Dim view As DataView
        view = New DataView(FilteredDTA)
        view.RowFilter = xyz
        C1Screen1.DataSource = view
        Dim visible = True
        For row = 0 To C1Screen1.Splits(0).Rows.Count - 1
            visible = False
            For row1 = 0 To C1Screen1.Splits(0).Rows.Count - 1
                If row <> row1 Then
                    If C1Screen1.Columns("SN").CellValue(row) = C1Screen1.Columns("SN").CellValue(row1) Then
                        visible = True
                        Exit For
                    End If
                End If
            Next
            C1Screen1.Splits(0).Rows(row).Visible = visible
        Next
    End If

Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 8 April 2020, 7:21 am EST

Hello Prabhat,

Enclosed is another sample which duplicates my issue with the [AND] option. You’ll notice when you select 104 with BEL, USA, Three records for BEL under 10411 is displayed even though USA does not have data in 10411. However, for some reason the record for BEL under 10412 does not appear, which is correct. it does not seem to work when a nation has multiple records with no other nations in the same SN.

Thanks,

VictorWindowsApp1A.zip

prabhat.sharma

Post Options:
Link
Copy

Posted 9 April 2020, 12:40 am EST

Hi Victor,

it does not seem to work when a nation has multiple records with no other nations in the same SN.

You can use the code snippet given below to achieve the desired result.

  If RadioButton2.Checked Then
            Dim xyz As String = ""
            If C1TrueDBGrid1.SelectedRows.Count = 0 Then
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    Dim str As String
                    '        If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                    '       str = "BEL"
                    '      Else
                    '     str = "USA"
                    'End If
                    str = C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row))
                    xyz = xyz + "[" + C1TrueDBGrid3.Columns("COUNTRY").Caption + "]='" + str + "' OR"
                Next
            Else
                For row = 0 To C1TrueDBGrid2.SelectedRows.Count - 1
                    For row1 = 0 To C1TrueDBGrid1.SelectedRows.Count - 1
                        Dim str As String
                        'If C1TrueDBGrid2.Columns("COUNTRYNAME").CellText(C1TrueDBGrid2.SelectedRows(row)) = "BELGIUM" Then
                        ' str = "BEL"
                        ' Else
                        ' str = "USA"
                        ' End If
                        str = C1TrueDBGrid2.Columns("COUNTRY").CellText(C1TrueDBGrid2.SelectedRows(row))
                        xyz = xyz + "[" + C1TrueDBGrid3.Columns("COUNTRY").Caption + "]='" + str + "' AND [" + C1TrueDBGrid3.Columns("SN").Caption + "] LIKE'" + C1TrueDBGrid1.Columns("CHPT").CellText(C1TrueDBGrid1.SelectedRows(row1)) + "%' OR"
                    Next
                Next
            End If
            xyz = xyz.Remove(xyz.Length - 3, 3)
            Console.WriteLine(xyz)
            fsLinkdata = New System.IO.FileStream(Application.StartupPath + "\AOP5D.xml", IO.FileMode.Open)
            dtsetLinkdata.Clear()
            dtsetLinkdata.ReadXml(fsLinkdata)
            fsLinkdata.Close()
            FilteredDTA = dtsetLinkdata.Tables(0)
            Dim view As DataView
            view = New DataView(FilteredDTA)
            view.RowFilter = xyz
            C1TrueDBGrid3.DataSource = view
            Dim visible = True
            For row = 0 To C1TrueDBGrid3.Splits(0).Rows.Count - 1
                visible = False
                For row1 = 0 To C1TrueDBGrid3.Splits(0).Rows.Count - 1
                    If row <> row1 Then
                        If C1TrueDBGrid3.Columns("SN").CellValue(row) = C1TrueDBGrid3.Columns("SN").CellValue(row1) AndAlso C1TrueDBGrid3.Columns("COUNTRY").CellValue(row) <> C1TrueDBGrid3.Columns("COUNTRY").CellValue(row1) Then
                            visible = True
                            Exit For
                        End If
                    End If
                Next
                C1TrueDBGrid3.Splits(0).Rows(row).Visible = visible
            Next

        End If

Regards,

Prabhat Sharma.

Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 9 April 2020, 11:29 pm EST

Hello Prabhat,

It seems to be working.

Thank you.

Victor
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 10 April 2020, 5:34 am EST

Hi Prabhat,

I’m still having issues with setting records found or the grid’s row count. What is the solution to get the right count. When trying C1TrueDBGrid3.rows.count, receiving error message.

System.InvalidOperationException: ‘Not supported when the grid is bound’

Thanks,

Victor
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 10 April 2020, 6:45 am EST

Hi,

TextBox1.Text = View.rowfilter.Count gives me the wrong record count.
Victor.m.charles.civ
- Post Options:
- Link
  Copy
Posted 10 April 2020, 7:14 am EST

Hello,

Please disregard my last message figured out the solution.

Victor

Please login to reply to thread

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels

ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.
ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server
Spread

Forums for all current versions of Spread .NET spreadsheets, SpreadJS JavaScript spreadsheets, and SpreadCOM spreadsheets.
Wijmo

Forums for all Wijmo products, including Wijmo Core, FinancialChart, FlexSheet, MultiRow, OLAP, and ReportViewer
- General Discussion
Document Solutions

Forums for all Document Solutions products, including Document Solutions for PDF, Word, Excel (.NET and Java), and Imaging.