Help with filtering records in C1TrueDBGrid

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

  • 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
  • Replied 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.
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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.
  • Replied 6 April 2020, 7:27 pm EST

    WindowsApp1A_modified.zip
  • Replied 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
  • Replied 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?


    <Countries>
    <COUNTRYID>1</COUNTRYID>
    <COUNTRY>BEL</COUNTRY>
    <COUNTRYNAME>BELGIUM</COUNTRYNAME>
    <CC>13</CC>
    </Countries>

    Thanks,

    Victor
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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.
  • Replied 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
  • Replied 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
  • Replied 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
  • Marked as Answer

    Replied 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.

  • Replied 9 April 2020, 11:29 pm EST

    Hello Prabhat,

    It seems to be working.

    Thank you.

    Victor
  • Replied 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
  • Replied 10 April 2020, 6:45 am EST

    Hi,

    TextBox1.Text = View.rowfilter.Count gives me the wrong record count.
  • Replied 10 April 2020, 7:14 am EST

    Hello,

    Please disregard my last message figured out the solution.

    Victor
Need extra support?

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

Learn More

Forum Channels