Help with comparing two C1TrueDBGrids and filtering based on selected field(s)

Posted by: Victor.m.charles.civ on 10 February 2020, 8:11 am EST

  • Posted 10 February 2020, 8:11 am EST

    How do I compare Grid1 and Grid2 and display all records in Grid1 with identical data in Grid2 based on selected data fields?

    For example If Grid1 contains 3 records with same data in selected (FirstName, LastName, Zipcode) in Grid2, I would like to only display those records in Grid1 or if necessary display them in Grid3 if easier than using Grid1.

    Thanks,

    Victor
  • Replied 10 February 2020, 3:28 pm EST

    Hi Victor,

    There is no direct method to achieve this scenario but you can iterate among cells and get it done.
    We have attached a sample implementing the same, please have a look.

    If you have any other issue regarding this then please modify the sample accordingly.

    Regards,
    Prabhat
    TDBIdenticalRecords.zip
  • Replied 11 February 2020, 3:58 pm EST

    Hi,

    How do I resolve the error below received when attempting to run the program?

    Severity Code Description Project File Line Suppression State
    Error Couldn't process file Form1.resx due to its being in the Internet or Restricted zone or having the mark of the web on the file. Remove the mark of the web if you want to process these files. TDBIdenticalRecords

    Thanks,

    Victor
  • Replied 11 February 2020, 4:26 pm EST

    Hello,

    I was able to view the code, can you please explain what the code is doing.

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Dim dt As DataTable = New DataTable()
    Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "ComponentOne Samples\Common\C1NWind.mdb") & ";"
    Dim conn As OleDbConnection = New OleDbConnection(connectionString)
    Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("Select * from Data", conn)
    adapter.Fill(dt)
    C1TrueDBGrid1.DataSource = dt
    Dim dt1 As DataTable = New DataTable()
    adapter = New OleDbDataAdapter("Select * from Data where Qt=1", conn)
    adapter.Fill(dt1)
    C1TrueDBGrid2.DataSource = dt1
    For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1
    Dim b As Boolean = False
    For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
    If C1TrueDBGrid1.Columns("Id").CellText(i).ToString = C1TrueDBGrid2.Columns("Id").CellText(j).ToString Then
    b = True
    Exit For
    End If
    Next
    Dim c As Boolean = False
    For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
    If C1TrueDBGrid1.Columns("Company").CellText(i).ToString = C1TrueDBGrid2.Columns("Id").CellText(j).ToString Then
    c = True
    Exit For
    End If
    Next
    Dim d As Boolean = False
    For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
    If C1TrueDBGrid1.Co


    I would like to select the fields to be compared by selecting the code below, instead of hard coding the fields. How do I implement it with your code?

    If ComboBox2.SelectedIndex = 0 Then
    yx = ""
    For Each Srow In Me.DBGrid1.SelectedRows
    If vc1 > 0 Then
    yx = yx & "," & "'" & Me.DBGrid1.Columns(0).CellText(Srow) & "'"
    Else
    yx = "'" & Me.DBGrid1.Columns(0).CellText(Srow) & "'"
    End If

    vc1 = vc1 + 1
    If yx.StartsWith(",") Then
    yx = yx.Substring(1, yx.Length - 1)
    End If
    ' yx.TrimStart(",")
    Next
    apply = True
    C1AOP5.Refresh()
    C1AOP5.FetchRowStyles = True
    TextBox9.Visible = True
    Panel1.Visible = True
    If ccc = 1 Then
    ccc = 0
    End If
    End If

    Thanks,

    Victor
  • Replied 12 February 2020, 12:24 am EST

    Hello again,

    Unfortunately still can nit run the application. I'm assuming Grid1 is looking for identical data shared by same Id and company columns. I'm assuming the ID column can be any other columns because the data in the auto generated ID column will never match both tables.

    If possible can you please send me the code (VB.NET) to just copy and paste in my project, having difficulties opening applications received.

    Thanks,

    Victor
  • Replied 12 February 2020, 9:57 pm EST

    Hi Victor,

    The sample that I shared is bind to the Data table of the C1NWind database which is shipped along with the control. Please make sure the database file is available in your sample at location: ..\Documents\ComponentOne Samples\Common

    If you still can not run the application then can you please share a GIF file showing the error message which you are getting at your end.

    >>I'm assuming the ID column can be any other columns
    Yes, you are right. As I have also taken Company and Cost column for the comparison.

    And we didn't get what your code is actually doing so can you please modify the attached sample or share your stripped-down sample replicating the use-case.

    Regards,
    Prabhat Sharma.
  • Replied 12 February 2020, 10:51 pm EST

    Hello Prabhat,

    The code below is creating a string of the columns I want to compare (e.g. 'ID', 'Customer', 'cost')
    How do I modify your code to include the string instead of hard coding the columns?

    Code:
    yx = ""
    For Each Srow In Me.DBGrid1.SelectedRows
    If vc1 > 0 Then
    yx = yx & "," & "'" & Me.DBGrid1.Columns(0).CellText(Srow) & "'"
    Else
    yx = "'" & Me.DBGrid1.Columns(0).CellText(Srow) & "'"
    End If
    vc1 = vc1 + 1
    If yx.StartsWith(",") Then
    yx = yx.Substring(1, yx.Length - 1)

    Thanks,

    Victor
  • Replied 13 February 2020, 9:38 pm EST

    Hi Victor,

    Sorry, but your code has numbers of unknows like yx ,vc1... so it is not possible to use this code.
    But as per our understanding, you want to select the columns at runtime through Combobox which was hardcoded in my last attached sample so we have updated the sample as per this understanding. Please have a look.

    >>Severity Code Description Project File Line Suppression State Error Couldn't process file Form1.resx due to its being in the Internet or Restricted zone or having the mark of the web on the file.
    If you downloaded the file from the internet, either separately or inside a .zip file or similar, it may have been "locked" because it is flagged as coming from the internet zone. Many programs will use this as a sign that the content should not be trusted.

    The simplest solution is to right-click the file in Windows Explorer, select Properties, and along the bottom of this dialog, you should have an "Unblock" option. Remember to click OK to accept the change.


    Regards,
    Prabhat Sharma
    TDBIdenticalRecords_Modifed.zip
  • Replied 14 February 2020, 8:37 am EST

    Hello Prabhat,

    I am currently away from work until 26 Feb, will get back to you by 28 Feb.

    Thanks,

    Victor
  • Replied 17 February 2020, 11:26 am EST

    Hello,

    Meanwhile until back In the office on 26 Feb, can you please send me the code without the application.

    Thanks,

    Victor
  • Replied 17 February 2020, 11:27 am EST

    Hello,

    Meanwhile until back In the office on 26 Feb, can you please send me the code without the application.

    Thanks,

    Victor
  • Replied 17 February 2020, 8:20 pm EST

    Hi Victor,

    You can look at the Form1.vb file in the last attached for the code which I used to select the Columns from the ComboBoxes at runtime and showing the result on a button click.

    Regards,
    Prabhat Sharma
  • Replied 18 February 2020, 12:39 am EST

    Hello Prabhat,

    I don't have access to my laptop, was hoping you can post the code for me to review while away from my office.

    Thanks,
    Victor
  • Replied 18 February 2020, 4:18 pm EST

    Hi Victor,

    Here the code is given below:

    Imports System.Data.OleDb

    Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Dim dt As DataTable = New DataTable()
    Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "ComponentOne Samples\Common\C1NWind.mdb") & ";"
    Dim conn As OleDbConnection = New OleDbConnection(connectionString)
    Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("Select * from Data", conn)
    adapter.Fill(dt)
    C1TrueDBGrid1.DataSource = dt
    Dim dt1 As DataTable = New DataTable()
    adapter = New OleDbDataAdapter("Select * from Data where Qt=1", conn)
    adapter.Fill(dt1)
    C1TrueDBGrid2.DataSource = dt1
    ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
    ComboBox2.DropDownStyle = ComboBoxStyle.DropDownList
    ComboBox3.DropDownStyle = ComboBoxStyle.DropDownList
    For i As Integer = 0 To C1TrueDBGrid2.Columns.Count - 1
    ComboBox1.Items.Add(C1TrueDBGrid2.Columns(i).Caption)
    ComboBox2.Items.Add(C1TrueDBGrid2.Columns(i).Caption)
    ComboBox3.Items.Add(C1TrueDBGrid2.Columns(i).Caption)
    Next
    ComboBox1.SelectedIndex = 0
    ComboBox2.SelectedIndex = 0
    ComboBox3.SelectedIndex = 0
    End Sub

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

    For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1
    C1TrueDBGrid1.Splits(0).Rows(i).Visible = True
    Next

    For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1
    Dim b As Boolean = False
    For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
    If ComboBox1.SelectedItem IsNot Nothing Then
    If C1TrueDBGrid1.Columns(ComboBox1.SelectedItem).CellText(i).ToString = C1TrueDBGrid2.Columns(ComboBox1.SelectedItem).CellText(j).ToString Then
    b = True
    Exit For
    End If
    End If

    Next
    Dim c As Boolean = False
    For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
    If ComboBox2.SelectedItem IsNot Nothing Then
    If C1TrueDBGrid1.Columns(ComboBox2.SelectedItem).CellText(i).ToString = C1TrueDBGrid2.Columns(ComboBox2.SelectedItem).CellText(j).ToString Then
    c = True
    Exit For
    End If
    End If
    Next
    Dim d As Boolean = False
    For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
    If ComboBox3.SelectedItem IsNot Nothing Then
    If C1TrueDBGrid1.Columns(ComboBox3.SelectedItem).CellText(i).ToString = C1TrueDBGrid2.Columns(ComboBox3.SelectedItem).CellText(j).ToString Then
    d = True
    Exit For
    End If
    End If
    Next
    If Not d OrElse Not c OrElse Not b Then
    C1TrueDBGrid1.Splits(0).Rows(i).Visible = False
    End If
    Next
    End Sub
    End Class


    Regards,
    Prabhat Sharma.
  • Replied 18 February 2020, 8:06 pm EST

    Hello Prabhat,

    Thank you very much.

    Victor
  • Replied 18 February 2020, 8:41 pm EST

    Hello Prabhat,

    Is is possible to use the values of yx in your code? I'm selecting the fields names from a DBGrid which gives me for example yx= 'fieldname1', 'fieldname2', 'fieldname3'.
    If possible I would like to plug in the values of yx in your initial code to compare the selected fields data in both Grids instead of using aCombobox for each field name, if I understand your code correctly.

    Thanks,

    Victor
  • Replied 18 February 2020, 9:08 pm EST

    Hello again,

    If can only be achieved using Comboboxes, is it possible to create Comboboxes by code and pass values in yx to those Comboboxes while they are invisible? For example if yx contains 3 field names, 3 Comboboxes would be created to accept the 3 field names.

    Or is possible to only use one combobox to select multiple field names and modify the code?

    Thanks,

    Victor
  • Replied 20 February 2020, 12:07 am EST

    Hi Victor,

    We are preparing the sample as per your need and will provide you shortly.

    Regards,
    Prabhat
  • Replied 20 February 2020, 3:49 am EST

    Hello Prahabat,

    Can you please send me the code with comments, will run the test project when back to work next Wednesday.

    Thank you.

    Victor
Need extra support?

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

Learn More

Forum Channels