Solved

Help highlighting rows with identical columns with same ID column

Posted on 2014-03-08
12
252 Views
Last Modified: 2014-03-10
How do you loop through a DataGridView and based on the same SN values, compare the rows and other columns and if they match highlight the entire row?

For example If I have:

  SN               CTRY      Item
 10411           BEL        BMW
 10411           BEL        BMW
 10421           USA        Jeep
 10451           USA        Jeep

Only the first two rows should be Highlighted (Backcolor Green, Textcolor White) because they both have SN = 10411 and cells for "Item" column in those two rows are the same.

Thanks,

Victor
0
Comment
Question by:vcharles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39915113
Hi Victor;

This code should only color the complete row of those that identical in all columns.

The query will group the rows by all columns having the same value and return the index of those rows if the group has more then one row.

Dim matchingRows = From row As DataGridViewRow In DataGridView1.Rows _
                   Group row By Key = New With {Key .SN = row.Cells("SN").Value, Key .CTRY = row.Cells("CTRY").Value, _
                                                Key .Item = row.Cells("Item").Value, Key .Item2 = row.Cells("Item2").Value} _
                                                Into Group _
                   Where Group.Count() > 1
                   From g In Group _
                   Select g.Index

For Each r In matchingRows
    DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Green
Next

Open in new window

0
 

Author Comment

by:vcharles
ID: 39915225
Hi,

I'm trying to use the code from both solutions on the same grid, but the cells that were previously red remained red, would highlighting the rows on the second solution instead of changing the color to green solve this problem? if yes, How do I highlight the rows instead?

Thanks,

Victor

Dim colorCells = From row As DataGridViewRow In DataGridView1.Rows _
                 Group row By Key = row.Cells("SN").Value Into Group _
                 Where Group.Count() > 1 _
                 Select New With _
                        { _
                            .Idx = Group.Select(Function(idx) idx.Index).ToList(), _
                            .Item = Group.Select(Function(i1) i1.Cells("Item").Value).Distinct().Count(), _
                            .Item2 = Group.Select(Function(i2) i2.Cells("Item2").Value).Distinct().Count() _
                        }

For Each snGroup In colorCells
    For Each idx As Integer In snGroup.Idx
        ' One If statement for each column whos background can change color
        ' Testing Item and Item2. Add one If statement to match the the Item's in Select statement above.
        If snGroup.Item > 1 Then
            DataGridView1.Item(2, idx).Style.BackColor = Color.Red
        End If
        If snGroup.Item2 > 1 Then
            DataGridView1.Item(3, idx).Style.BackColor = Color.Red
        End If
    Next
Next

Dim matchingRows = From row As DataGridViewRow In DataGridView1.Rows _
                   Group row By Key = New With {Key .SN = row.Cells("SN").Value, Key .CTRY = row.Cells("CTRY").Value, _
                                                Key .Item = row.Cells("Item").Value, Key .Item2 = row.Cells("Item2").Value} _
                                                Into Group _
                   Where Group.Count() > 1
                   From g In Group _
                   Select g.Index

For Each r In matchingRows
    DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Green
Next
0
 

Author Comment

by:vcharles
ID: 39915367
Hi,

Is it also possible to include a For Next loop to check the columns values instead of hard coding the columns? I'll will be providing users the option to select columns they want to display, hard coding the columns in the code will cause an error if it's not included in the DataGrid.

Thanks,

Victor
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39915814
Hi Victor;

Try changing this part of the code in your last post:
For Each r In matchingRows
    DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Green
Next

Open in new window

To this code.
For Each r In matchingRows
    For idx As Integer = 0 To DataGridView1.Columns.Count - 1
        DataGridView1.Item(idx, r).Style.BackColor = Color.Green
    Next
Next

Open in new window

0
 

Author Comment

by:vcharles
ID: 39915908
Hi,

It works.

Thank You.

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39915911
Not a problem Victor, glad to help.
0
 

Author Comment

by:vcharles
ID: 39916959
Hi again,

Is it also possible to include a For Next loop to check the columns values instead of hard coding the columns? I'll will be providing users the option to select columns they want to display, hard coding the columns in the code will cause an error if it's not included in the DataGrid.

Thanks,

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39917483
I guess I'm not understanding your follow-up question can you please explain in more detail.
0
 

Author Comment

by:vcharles
ID: 39917549
Hi,

I'm sorry, I included my follow question on the wrong post. It's in regard to the code below from a previous post:

Is it possible to replace Item and Item2 by i in a For Next Loop and apply the for all the other columns starting from Column 3, I tried it but it didn't work. I'm trying to avoid hard coding the columns incase they are not on the Grid because suers will be able to choose columns they want to display.






Dim colorCells = From row As DataGridViewRow In DataGridView1.Rows _
                 Group row By Key = row.Cells("SN").Value Into Group _
                 Where Group.Count() > 1 _
                 Select New With _
                        { _
                            .Idx = Group.Select(Function(idx) idx.Index).ToList(), _
                            .Item = Group.Select(Function(i1) i1.Cells("Item").Value).Distinct().Count(), _
                            .Item2 = Group.Select(Function(i2) i2.Cells("Item2").Value).Distinct().Count() _
                        }

For Each snGroup In colorCells
    For Each idx As Integer In snGroup.Idx
        ' One If statement for each column whos background can change color
        ' Testing Item and Item2. Add one If statement to match the the Item's in Select statement above.
        If snGroup.Item > 1 Then
            DataGridView1.Item(2, idx).Style.BackColor = Color.Red
        End If
        If snGroup.Item2 > 1 Then
            DataGridView1.Item(3, idx).Style.BackColor = Color.Red
        End If
    Next
Next

Attempt:
Dim i as integer = 3
Dim colorCells = From row As DataGridViewRow In DataGridView1.Rows _
                 Group row By Key = row.Cells("SN").Value Into Group _
                 Where Group.Count() > 1 _
                 For I = 3 to 10
                 Select New With _
                        { _
                            .Idx = Group.Select(Function(idx) idx.Index).ToList(), _
                            .Item = Group.Select(Function(i1) i1.Cells(i).Value).Distinct().Count()
                        }
                    Next
For Each snGroup In colorCells
    For Each idx As Integer In snGroup.Idx
        ' One If statement for each column whos background can change color
        ' Testing Item and Item2. Add one If statement to match the the Item's in Select statement above.
        If snGroup.Item > 1 Then
            DataGridView1.Item(2, idx).Style.BackColor = Color.Red
        End If
        If snGroup.Item2 > 1 Then
            DataGridView1.Item(3, idx).Style.BackColor = Color.Red
        End If
    Next
Next

Instead of 10 how do I get the number of columns?

Thanks,

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39917852
Hi Victor;

This can not be placed in the middle of a Linq query:

For I = 3 to 10

Because it is not part of the Linq query language. You will need to take it out of the query and do the rest through code. The following code sample should work.

Dim dontMatch = From row As DataGridViewRow In DataGridView1.Rows _
        Group row By Key = row.Cells("SN").Value Into Group _
        Where Group.Count() > 1 _
        Select Group

For Each snGroup In dontMatch
    Dim Index As List(Of Integer) = snGroup.Select(Function(idx) idx.Index).ToList()
    For i As Integer = 2 To DataGridView1.ColumnCount - 1
        Dim item = snGroup.Select(Function(i1) i1.Cells(i).Value).Distinct().Count()
        If item > 1 Then
            For Each row In Index
                DataGridView1.Item(i, row).Style.BackColor = Color.Red
            Next
        End If
    Next
Next

Open in new window

If you need the number of columns in the DataGridView you can use the property ColumnCount, for example:

DataGridView1.ColumnCount
0
 

Author Comment

by:vcharles
ID: 39918516
Hi,

It worked!!

THANK YOU!

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39918521
Not a problem.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert VB web project to C# 3 66
CSS question 16 63
Help with adding xml file as datasource in MS Report 8 22
Need a quick overview of Selenium testing 2 36
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question