Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help highlighting rows with identical columns with same ID column

Posted on 2014-03-08
12
Medium Priority
?
260 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 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

688 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