Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help highlighting rows with identical columns with same ID column

Posted on 2014-03-08
12
Medium Priority
?
262 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
  • 6
  • 6
12 Comments
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 64

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 64

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 64

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 64

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 64

Expert Comment

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

886 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