Forums Home / ComponentOne / WinForms Edition
Posted by: Victor.m.charles.civ on 10 February 2020, 5:11 pm EST
Victor.m.charles.civ
Posted 10 February 2020, 5:11 pm 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
prabhat.sharma
Posted 11 February 2020, 12:28 am 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
Victor.m.charles.civ
Posted 12 February 2020, 12:58 am 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
Victor.m.charles.civ
Posted 12 February 2020, 1:26 am 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
Victor.m.charles.civ
Posted 12 February 2020, 9: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
prabhat.sharma
Posted 13 February 2020, 6:57 am 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.
Victor.m.charles.civ
Posted 13 February 2020, 7:51 am 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
prabhat.sharma
Posted 14 February 2020, 6:38 am 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
Victor.m.charles.civ
Posted 14 February 2020, 5:37 pm EST
Hello Prabhat,
I am currently away from work until 26 Feb, will get back to you by 28 Feb.
Thanks,
Victor
Victor.m.charles.civ
Posted 17 February 2020, 8:26 pm EST
Hello,
Meanwhile until back In the office on 26 Feb, can you please send me the code without the application.
Thanks,
Victor
Victor.m.charles.civ
Posted 17 February 2020, 8:27 pm EST
Hello,
Meanwhile until back In the office on 26 Feb, can you please send me the code without the application.
Thanks,
Victor
prabhat.sharma
Posted 18 February 2020, 5:20 am 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
Victor.m.charles.civ
Posted 18 February 2020, 9: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
prabhat.sharma
Posted 19 February 2020, 1:18 am 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.
Victor.m.charles.civ
Posted 19 February 2020, 5:06 am EST
Hello Prabhat,
Thank you very much.
Victor
Victor.m.charles.civ
Posted 19 February 2020, 5:41 am 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
Victor.m.charles.civ
Posted 19 February 2020, 6:08 am 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
prabhat.sharma
Posted 20 February 2020, 9:07 am EST
Hi Victor,
We are preparing the sample as per your need and will provide you shortly.
Regards,
Prabhat
Victor.m.charles.civ
Posted 20 February 2020, 12:49 pm 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
prabhat.sharma
Posted 24 February 2020, 8:43 am EST
Hi Victor,
>>Or is possible to only use one combobox to select multiple field names and modify the code?
We have used C1MultiSelect control which can select multiple values from the combolist and show the desired result.
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
For i As Integer = 0 To C1TrueDBGrid2.Columns.Count - 1
C1MultiSelect1.Items.Add(C1TrueDBGrid2.Columns(i).Caption)
Next
C1MultiSelect1.TagWrap = True
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'Set the all rows to visible initially
For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1
C1TrueDBGrid1.Splits(0).Rows(i).Visible = True
Next
Dim b As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {}
'If any column is selected by C1MultiSelect
If C1MultiSelect1.SelectedItems.Count > 0 Then
'Iterate for all rows of main TDB
For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1
'Iterate for each column selected by C1MultiSelect
For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1
b(k) = False
'Iterate with all rows of comparing TDB
For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
If C1MultiSelect1.SelectedItems(k).Value IsNot Nothing Then
If C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid2.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(j).ToString Then
b(k) = True
Exit For
End If
End If
Next
Next
Dim temp = False
For l As Integer = 0 To b.Count - 1
If b(l) = False Then
temp = True
Exit For
End If
Next
If temp Then
C1TrueDBGrid1.Splits(0).Rows(i).Visible = False
End If
Next
End If
End Sub
End Class
Regards,
Prabhat
Victor.m.charles.civ
Posted 25 February 2020, 1:37 pm EST
Hello,
I modified the code in my project to load the Grids with xml files, but when I click on the button1 for filter Grid1 based on fields selected, it does not work, after running some test, notice MsgBox(“nnn”) does nor execute even though both fields gave identical data in both Grids. can you please help this issue.
Imports System.Data.OleDb
Public Class Form1
Public fsLinkCHPT As System.IO.FileStream
Public dtsetLinkCHPT As New DataSet
Public fsLinkCTRY As System.IO.FileStream
Public dtsetLinkCTRY As New DataSet
Public vc11 As Integer = 0
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
fsLinkCHPT = New System.IO.FileStream(Application.StartupPath + "\AOP5A.xml", IO.FileMode.Open) dtsetLinkCHPT.Clear() dtsetLinkCHPT.ReadXml(fsLinkCHPT) fsLinkCHPT.Close() Me.C1TrueDBGrid1.DataSource = dtsetLinkCHPT.Tables(0) 'LOAD COUNTRIES fsLinkCTRY = New System.IO.FileStream(Application.StartupPath + "\AOP5B.xml", IO.FileMode.Open) dtsetLinkCTRY.Clear() dtsetLinkCTRY.ReadXml(fsLinkCTRY) fsLinkCTRY.Close() Me.C1TrueDBGrid2.DataSource = dtsetLinkCTRY.Tables(0) For i As Integer = 0 To C1TrueDBGrid2.Columns.Count - 1 C1MultiSelect1.Items.Add(C1TrueDBGrid2.Columns(i).Caption) Next C1MultiSelect1.TagWrap = True For i As Integer = 0 To C1TrueDBGrid2.Columns.Count - 1 C1MultiSelect1.Items.Add(C1TrueDBGrid2.Columns(i).Caption) Next C1MultiSelect1.TagWrap = True End Sub Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 'Set the all rows to visible initially For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1 C1TrueDBGrid1.Splits(0).Rows(i).Visible = True Next Dim b As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {} 'If any column is selected by C1MultiSelect If C1MultiSelect1.SelectedItems.Count > 0 Then 'Iterate for all rows of main TDB For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1 'Iterate for each column selected by C1MultiSelect For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1 b(k) = False 'Iterate with all rows of comparing TDB For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1 If C1MultiSelect1.SelectedItems(k).Value IsNot Nothing Then If C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid2.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(j).ToString Then b(k) = True Exit For End If End If Next Next MsgBox("lll") Dim temp = False For l As Integer = 0 To b.Count - 1 If b(l) = False Then temp = True Exit For End If Next MsgBox("mm") If temp Then MsgBox("nnn") ' ************* Does not execute C1TrueDBGrid1.Splits(0).Rows(i).Visible = False End If Next End If End Sub
Victor.m.charles.civ
Posted 25 February 2020, 1:38 pm EST
Hello,
I modified the code in my project to load the Grids with xml files, but when I click on the button1 for filter Grid1 based on fields selected, it does not work, after running some test, notice MsgBox(“nnn”) does nor execute even though both fields gave identical data in both Grids. can you please help this issue.
Imports System.Data.OleDb
Public Class Form1
Public fsLinkCHPT As System.IO.FileStream
Public dtsetLinkCHPT As New DataSet
Public fsLinkCTRY As System.IO.FileStream
Public dtsetLinkCTRY As New DataSet
Public vc11 As Integer = 0
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
fsLinkCHPT = New System.IO.FileStream(Application.StartupPath + "\AOP5A.xml", IO.FileMode.Open) dtsetLinkCHPT.Clear() dtsetLinkCHPT.ReadXml(fsLinkCHPT) fsLinkCHPT.Close() Me.C1TrueDBGrid1.DataSource = dtsetLinkCHPT.Tables(0) 'LOAD COUNTRIES fsLinkCTRY = New System.IO.FileStream(Application.StartupPath + "\AOP5B.xml", IO.FileMode.Open) dtsetLinkCTRY.Clear() dtsetLinkCTRY.ReadXml(fsLinkCTRY) fsLinkCTRY.Close() Me.C1TrueDBGrid2.DataSource = dtsetLinkCTRY.Tables(0) For i As Integer = 0 To C1TrueDBGrid2.Columns.Count - 1 C1MultiSelect1.Items.Add(C1TrueDBGrid2.Columns(i).Caption) Next C1MultiSelect1.TagWrap = True For i As Integer = 0 To C1TrueDBGrid2.Columns.Count - 1 C1MultiSelect1.Items.Add(C1TrueDBGrid2.Columns(i).Caption) Next C1MultiSelect1.TagWrap = True End Sub Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 'Set the all rows to visible initially For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1 C1TrueDBGrid1.Splits(0).Rows(i).Visible = True Next Dim b As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {} 'If any column is selected by C1MultiSelect If C1MultiSelect1.SelectedItems.Count > 0 Then 'Iterate for all rows of main TDB For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1 'Iterate for each column selected by C1MultiSelect For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1 b(k) = False 'Iterate with all rows of comparing TDB For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1 If C1MultiSelect1.SelectedItems(k).Value IsNot Nothing Then If C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid2.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(j).ToString Then b(k) = True Exit For End If End If Next Next MsgBox("lll") Dim temp = False For l As Integer = 0 To b.Count - 1 If b(l) = False Then temp = True Exit For End If Next MsgBox("mm") If temp Then MsgBox("nnn") ' ************* Does not execute C1TrueDBGrid1.Splits(0).Rows(i).Visible = False End If Next End If End Sub
Victor.m.charles.civ
Posted 25 February 2020, 1:39 pm EST
Hello,
I modified the code in my project to load the Grids with xml files, but when I click on the button1 for filter Grid1 based on fields selected, it does not work, after running some test, notice MsgBox(“nnn”) does nor execute even though both fields gave identical data in both Grids. can you please help this issue.
Imports System.Data.OleDb
Public Class Form1
Public fsLinkCHPT As System.IO.FileStream
Public dtsetLinkCHPT As New DataSet
Public fsLinkCTRY As System.IO.FileStream
Public dtsetLinkCTRY As New DataSet
Public vc11 As Integer = 0
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
fsLinkCHPT = New System.IO.FileStream(Application.StartupPath + "\AOP5A.xml", IO.FileMode.Open) dtsetLinkCHPT.Clear() dtsetLinkCHPT.ReadXml(fsLinkCHPT) fsLinkCHPT.Close() Me.C1TrueDBGrid1.DataSource = dtsetLinkCHPT.Tables(0) 'LOAD COUNTRIES fsLinkCTRY = New System.IO.FileStream(Application.StartupPath + "\AOP5B.xml", IO.FileMode.Open) dtsetLinkCTRY.Clear() dtsetLinkCTRY.ReadXml(fsLinkCTRY) fsLinkCTRY.Close() Me.C1TrueDBGrid2.DataSource = dtsetLinkCTRY.Tables(0) For i As Integer = 0 To C1TrueDBGrid2.Columns.Count - 1 C1MultiSelect1.Items.Add(C1TrueDBGrid2.Columns(i).Caption) Next C1MultiSelect1.TagWrap = True For i As Integer = 0 To C1TrueDBGrid2.Columns.Count - 1 C1MultiSelect1.Items.Add(C1TrueDBGrid2.Columns(i).Caption) Next C1MultiSelect1.TagWrap = True End Sub Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 'Set the all rows to visible initially For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1 C1TrueDBGrid1.Splits(0).Rows(i).Visible = True Next Dim b As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {} 'If any column is selected by C1MultiSelect If C1MultiSelect1.SelectedItems.Count > 0 Then 'Iterate for all rows of main TDB For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1 'Iterate for each column selected by C1MultiSelect For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1 b(k) = False 'Iterate with all rows of comparing TDB For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1 If C1MultiSelect1.SelectedItems(k).Value IsNot Nothing Then If C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid2.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(j).ToString Then b(k) = True Exit For End If End If Next Next MsgBox("lll") Dim temp = False For l As Integer = 0 To b.Count - 1 If b(l) = False Then temp = True Exit For End If Next MsgBox("mm") If temp Then MsgBox("nnn") ' ************* Does not execute C1TrueDBGrid1.Splits(0).Rows(i).Visible = False End If Next End If End Sub
Victor.m.charles.civ
Posted 25 February 2020, 1:40 pm EST
Sorry for the multiple post, didn’t notice it was posted on a page 2.
Victor.m.charles.civ
Posted 25 February 2020, 3:32 pm EST
Hello again,
It seems to be working, it was not working because I was using the same table in both Grids.
Is there a way to modify the code to filter Grid1 using the AND or OR approach with multiple fields, I believe the solution you provided me is using the AND approach. For example using the “AND” approach the rows in Grid1 must have identical data as of Grid2. Using the “OR” approach the rows in Grid1 with be displayed if identical data is eith fields selected.
Thanks,
Victor
Thanks,
Victor
Victor.m.charles.civ
Posted 25 February 2020, 6:45 pm EST
Hello,
I noticed an inconsistency in the solution, You 'll notice this issue when you select the FIF field, second row with NSN ending with 9991 should not be displayed in Grid1 because the data in both Grids don’t match. How do I fix this issue?
It works when I select the NSN field using the default [OR] operator. Also hw do I modify the code if I select the [AND] operator, where the data must be the same in all selected fields for each row in both Grids?
Thanks,
VictorGRIDTESTING022520.zip
prabhat.sharma
Posted 26 February 2020, 4:21 am EST
Hi Victor,
As we are filtering the first TDB based on the similar values in second TDB so when filtering based on the FIF column, all the records of the first TDB in FIF column if matches with any record of the FIF column of second TDB then that row will be visible in second TDB.
We have modified your attached sample with OR and AND both the filters based on the value selected by Radio button, please have a look.
If both the grids have the same number of records and you want to compare data row by row in both the grids then please let us know.
Regards,
Prabhat Sharma,
GRIDTESTING022520.zip
Victor.m.charles.civ
Posted 26 February 2020, 6:38 am EST
Hello Prabhat,
Please take a look at the image, I change NA/99 to NB99 but it should not make a difference, When I filter by FIF, the second row in Grid1 should not be displayed because the value in Grid2 in NB99 and the value in Grid1 is N/A in the FIF column.
I will run more test in the AND option and get back to you. Basically if I select the AND operator, both grids must have identical data in rows for all the selected fields.
For example if I filter by NSN and FIF both grids must have rows with same data for both FIF and NSN.
Thank you,
Victor
prabhat.sharma
Posted 26 February 2020, 7:39 am EST
Hello,
As per our understanding, you want to compare data row by row in both the grids so we have updated the sample accordingly, please have a look and let us know if you find this useful.
Regards,
Prabhat Sharma.
GRIDTESTING022520_Modified.zip
Victor.m.charles.civ
Posted 26 February 2020, 12:03 pm EST
Hi Prabhat,
It seems to be working GREAT!,
THANK YOU
Victor
Victor.m.charles.civ
Posted 26 February 2020, 12:22 pm EST
Hello Prabhat,
One more question on this issue.How do I display the result in another Grid? For example C1TrueDBGrid3, I think it would be user friendlier than having to reload Grid1 for each search.
Thanks,
Victor
prabhat.sharma
Posted 27 February 2020, 2:37 am EST
Hello Victor,
I am glad to know that the last sample worked for you as you desired.
One more question on this issue. How do I display the result in another Grid? For example C1TrueDBGrid3
I have updated the last sample and showed the filter result in another grid i.e. TrueDBGrid3, please have a look.
Regards,
Prabhat Sharma.
GRIDTESTING022520_Mod_2.zip
Victor.m.charles.civ
Posted 27 February 2020, 9:53 am EST
Hello Prabhat,
Thanks for the solution will test it and get back to you. Is it possible to show matching records from both Grid1 and Grid2 in Grid3.
Thanks,
Victor
Victor.m.charles.civ
Posted 27 February 2020, 10:11 am EST
Hello Prabhat,
The solution to display the results in Grid3 id working great.
Thanks,
Victor
Victor.m.charles.civ
Posted 28 February 2020, 2:45 pm EST
Hello Prabhat,
Is it possible to show matching records from both Grid1 and Grid2 in Grid3.
Thanks,
Victor
Victor.m.charles.civ
Posted 29 February 2020, 10:25 am EST
Hello Prabhat,
I tried to modify your code in the bottom part of code below to do the same for Grid2 but receiving error message: “Can not find column 0” on line: C1TrueDBGrid2.Splits(0).DisplayColumns(row).DataColumn.CellText(i)
How do I fix this issue?
In addition of trying to display results for both Grids in Grid3.
I would also like to displays their results in separate Grids (Grid3 for Grid1 and Grid4 for Grid2)
Code:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'Set the all rows to visible initially
For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1
’ C1TrueDBGrid1.Splits(0).Rows(i).Visible = True
Next
C1TrueDBGrid3.Rows.Clear() Dim b As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {} C1TrueDBGrid4.Rows.Clear() Dim c As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {} 'If any column is selected by C1MultiSelect If C1MultiSelect1.SelectedItems.Count > 0 Then 'Iterate for all rows of main TDB For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1 'Iterate for each column selected by C1MultiSelect For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1 b(k) = False If C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i).ToString Then b(k) = True End If Next 'And Filter If RadioButton2.Checked Then 'And Filter Dim temp = False For l As Integer = 0 To b.Count - 1 If b(l) = False Then temp = True Exit For End If Next If Not temp Then ' C1TrueDBGrid1.Splits(0).Rows(i).Visible = False Dim dr As DataRow = Me.C1TrueDBGrid3.NewRow For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid3.Rows.Add(dr) End If If Not temp Then ' C1TrueDBGrid1.Splits(0).Rows(i).Visible = False Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid4.Rows.Add(dr) End If End If 'Or Filter If RadioButton1.Checked Then Dim temp = True For l As Integer = 0 To b.Count - 1 If b(l) = True Then temp = False Exit For End If Next ' MsgBox("mmmmmmmm") If Not temp Then ' MsgBox("nnn") Dim dr As DataRow = Me.C1TrueDBGrid3.NewRow For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid3.Rows.Add(dr) ' C1TrueDBGrid1.Splits(0).Rows(i).Visible = False End If End If Next End If '************************************************************* ' DO SAME RESULTS FOR GRID2 Me.C1TrueDBGrid2.DataSource = dtsetLinkCTRY.Tables(0) If C1MultiSelect1.SelectedItems.Count > 0 Then 'Iterate for all rows of main TDB For i As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1 'Iterate for each column selected by C1MultiSelect For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1 c(k) = False If C1TrueDBGrid2.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i).ToString Then c(k) = True End If Next 'And Filter If RadioButton2.Checked Then 'And Filter Dim temp = False For l As Integer = 0 To c.Count - 1 If c(l) = False Then temp = True Exit For End If Next If Not temp Then ' C1TrueDBGrid2.Splits(0).Rows(i).Visible = False Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow For row As Integer = 0 To C1TrueDBGrid2.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid2.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid4.Rows.Add(dr) End If If Not temp Then ' C1TrueDBGrid2.Splits(0).Rows(i).Visible = False Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid4.Rows.Add(dr) End If End If 'Or Filter If RadioButton1.Checked Then Dim temp = True For l As Integer = 0 To b.Count - 1 If b(l) = True Then temp = False Exit For End If Next ' MsgBox("mmmmmmmm") If Not temp Then ' MsgBox("nnn") Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow For row As Integer = 0 To C1TrueDBGrid2.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid2.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid4.Rows.Add(dr) ' C1TrueDBGrid2.Splits(0).Rows(i).Visible = False End If End If Next End If End Sub
Thanks,
Victor
Victor.m.charles.civ
Posted 29 February 2020, 4:35 pm EST
Hello,
I was able to get the results from Grid2 in Grid4 using the code below, my only issue now to be results from both Grids 1 & 2 in Grid3.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 'Set the all rows to visible initially For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1 ' C1TrueDBGrid1.Splits(0).Rows(i).Visible = True Next C1TrueDBGrid3.Rows.Clear() Dim b As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {} C1TrueDBGrid4.Rows.Clear() Dim c As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {} 'If any column is selected by C1MultiSelect If C1MultiSelect1.SelectedItems.Count > 0 Then 'Iterate for all rows of main TDB For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1 'Iterate for each column selected by C1MultiSelect For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1 b(k) = False If C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid2.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i).ToString Then b(k) = True End If Next 'And Filter If RadioButton2.Checked Then 'And Filter Dim temp = False For l As Integer = 0 To b.Count - 1 If b(l) = False Then temp = True Exit For End If Next If Not temp Then ' C1TrueDBGrid1.Splits(0).Rows(i).Visible = False Dim dr As DataRow = Me.C1TrueDBGrid3.NewRow For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid3.Rows.Add(dr) End If If Not temp Then ' C1TrueDBGrid1.Splits(0).Rows(i).Visible = False Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid4.Rows.Add(dr) End If End If 'Or Filter If RadioButton1.Checked Then Dim temp = True For l As Integer = 0 To b.Count - 1 If b(l) = True Then temp = False Exit For End If Next ' MsgBox("mmmmmmmm") If Not temp Then ' MsgBox("nnn") Dim dr As DataRow = Me.C1TrueDBGrid3.NewRow For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid3.Rows.Add(dr) ' C1TrueDBGrid1.Splits(0).Rows(i).Visible = False End If End If Next End If ' Display data og GRID2 'Set the all rows to visible initially For i As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1 ' C1TrueDBgrid2.Splits(0).Rows(i).Visible = True Next C1TrueDBGrid4.Rows.Clear() 'If any column is selected by C1MultiSelect If C1MultiSelect1.SelectedItems.Count > 0 Then 'Iterate for all rows of main TDB For i As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1 'Iterate for each column selected by C1MultiSelect For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1 b(k) = False If C1TrueDBGrid2.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i).ToString Then b(k) = True End If Next 'And Filter If RadioButton2.Checked Then 'And Filter Dim temp = False For l As Integer = 0 To b.Count - 1 If b(l) = False Then temp = True Exit For End If Next If Not temp Then ' C1TrueDBgrid2.Splits(0).Rows(i).Visible = False Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow For row As Integer = 0 To C1TrueDBGrid2.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid2.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid4.Rows.Add(dr) End If End If 'Or Filter If RadioButton1.Checked Then Dim temp = True For l As Integer = 0 To b.Count - 1 If b(l) = True Then temp = False Exit For End If Next ' MsgBox("mmmmmmmm") If Not temp Then ' MsgBox("nnn") Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow For row As Integer = 0 To C1TrueDBGrid2.Splits(0).DisplayColumns.Count - 1 dr(row) = C1TrueDBGrid2.Splits(0).DisplayColumns(row).DataColumn.CellText(i) Next C1TrueDBGrid4.Rows.Add(dr) ' C1TrueDBgrid2.Splits(0).Rows(i).Visible = False End If End If Next End If End Sub
Thanks,
Victor
prabhat.sharma
Posted 2 March 2020, 5:40 am EST
Hi Victor,
Please modify the code as per your need in the last attached sample and share it back.
It will be very helpful if you could send the screenshot of the output that you are getting at your end and modified screenshot with the requirements that you want to achieve.
Regards,
Prabhat Sharma.
Victor.m.charles.civ
Posted 2 March 2020, 11:18 am EST
Hello Prabhat.
Enclosed is the latest test project. I would like to load Grid5 with data from Grid1 output(Grid3) and Grid 2 output (Grid4) sorted by NSN SCOUNTRY and COUNTRY Columns
Thanks,
Victor
prabhat.sharma
Posted 3 March 2020, 5:30 am EST
Hi,
TrueDBGrid does not have programmatic sorting and we would need to modify the DataSource for applying for a sort order.So we need to take the data identical in both the grids (grid3 and grid4) into a DataTable and bind that DataTable to the Grid5 to achieve the desired scenario.
Regards,
Prabhat Sharma.
GRIDTESTING022520_Grid5_MOD.zip
Victor.m.charles.civ
Posted 3 March 2020, 9:19 pm EST
Hello Prabhat,
It works. Thank you.
Is it possible to sort Grid5 by fields selected in the Multiselect control?
Victor
prabhat.sharma
Posted 4 March 2020, 12:12 am EST
Hello Victor,
Just use the code snippet given below to set the Sort property of the DataView as per your MultiSelect selected values.
Dim str As String = C1MultiSelect1.SelectedItems(0).Value.ToString + " ASC"
For item As Integer = 1 To C1MultiSelect1.SelectedItems.Count - 1
str = str + "," + C1MultiSelect1.SelectedItems(item).Value.ToString + " ASC"
Next
Dim DataView As DataView = dt.AsDataView()
DataView.Sort = str
C1TrueDBGrid5.DataSource = DataView
Regards,
Prabhat Sharma.
Victor.m.charles.civ
Posted 6 March 2020, 2:39 pm EST
Hello Prabhat,
It works. Thanks.
How do I highlight column headers of selected column(s).
Thanks,
Victor
prabhat.sharma
Posted 9 March 2020, 2:45 am EST
Hello,
It is not possible to highlight the headers selected column but you can highlight the column which contains column selector by using code snippet given below:
c1TrueDBGrid1.ColumnSelectorStyle.BackColor = Color.Red
Note: As this is a different case so you are requested to create a new case for this and close this case if the initial requirement of this case is met.
Regards,
Prabhat Sharma.
Victor.m.charles.civ
Posted 14 March 2020, 12:46 am EST
OK. Thanks
Victor.m.charles.civ
Posted 27 May 2020, 10:08 pm EST
Hello Prabhat,
Is it possible to reopen this case or should I open a new case? I noticed when comparing Grid1 and Grid2 for identical data in selected field, not all the matching records in Grid2 are displayed in Grid4. For example if you select the SN field, you’ll notice Grid4 only incudes the exact number of records in Grid1, even though Grid2 contains additional records where SN = 10411. How do I modify the code to fix this issue?
Thanks,
VictorGRIDTESTING022520.zip
prabhat.sharma
Posted 28 May 2020, 5:19 am EST
Hello Victor,
As this thread has been taken too long so you are requested to create a new case if you have any further queries.
To achieve the desired result as, please update the code of Button1 click event as given below:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'Set the all rows to visible initially
For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1
Next
C1TrueDBGrid3.Rows.Clear()
Dim b As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {}
C1TrueDBGrid4.Rows.Clear()
Dim c As Boolean() = New Boolean(C1MultiSelect1.SelectedItems.Count - 1) {}
'If any column is selected by C1MultiSelect
If C1MultiSelect1.SelectedItems.Count > 0 Then
'Iterate for all rows of main TDB
For i As Integer = 0 To C1TrueDBGrid1.Splits(0).Rows.Count - 1
'Iterate for each column selected by C1MultiSelect
For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1
b(k) = False
If C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid2.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i).ToString Then
b(k) = True
End If
Next
'And Filter
If RadioButton2.Checked Then
Dim temp = False
For l As Integer = 0 To b.Count - 1
If b(l) = False Then
temp = True
Exit For
End If
Next
If Not temp Then
Dim dr As DataRow = Me.C1TrueDBGrid3.NewRow
For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1
dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i)
Next
C1TrueDBGrid3.Rows.Add(dr)
End If
If Not temp Then
Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow
For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1
dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i)
Next
C1TrueDBGrid4.Rows.Add(dr)
End If
End If
'Or Filter
If RadioButton1.Checked Then
Dim temp = True
For l As Integer = 0 To b.Count - 1
If b(l) = True Then
temp = False
Exit For
End If
Next
If Not temp Then
Dim dr As DataRow = Me.C1TrueDBGrid3.NewRow
For row As Integer = 0 To C1TrueDBGrid1.Splits(0).DisplayColumns.Count - 1
dr(row) = C1TrueDBGrid1.Splits(0).DisplayColumns(row).DataColumn.CellText(i)
Next
C1TrueDBGrid3.Rows.Add(dr)
End If
End If
Next
End If
' Display data in GRID2
'Set the all rows to visible initially
For i As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
Next
C1TrueDBGrid4.Rows.Clear()
'If any column is selected by C1MultiSelect
If C1MultiSelect1.SelectedItems.Count > 0 Then
'Iterate for all rows of main TDB
For i As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
'Iterate for each column selected by C1MultiSelect
For k As Integer = 0 To C1MultiSelect1.SelectedItems.Count - 1
b(k) = False
For j As Integer = 0 To C1TrueDBGrid2.Splits(0).Rows.Count - 1
If C1TrueDBGrid2.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(i) = C1TrueDBGrid1.Columns(C1MultiSelect1.SelectedItems(k).Value.ToString).CellText(j).ToString Then
b(k) = True
Exit For
End If
Next
Next
'And Filter
If RadioButton2.Checked Then
'And Filter
Dim temp = False
For l As Integer = 0 To b.Count - 1
If b(l) = False Then
temp = True
Exit For
End If
Next
If Not temp Then
Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow
For row As Integer = 0 To C1TrueDBGrid2.Splits(0).DisplayColumns.Count - 1
dr(row) = C1TrueDBGrid2.Splits(0).DisplayColumns(row).DataColumn.CellText(i)
Next
C1TrueDBGrid4.Rows.Add(dr)
End If
End If
'Or Filter
If RadioButton1.Checked Then
Dim temp = True
For l As Integer = 0 To b.Count - 1
If b(l) = True Then
temp = False
Exit For
End If
Next
If Not temp Then
Dim dr As DataRow = Me.C1TrueDBGrid4.NewRow
For row As Integer = 0 To C1TrueDBGrid2.Splits(0).DisplayColumns.Count - 1
dr(row) = C1TrueDBGrid2.Splits(0).DisplayColumns(row).DataColumn.CellText(i)
Next
C1TrueDBGrid4.Rows.Add(dr)
End If
End If
Next
End If
End Sub
Regards,
Prabhat Sharma.
