Solved

Help highlighting rows with identical columns with same ID column

Posted on 2014-03-08
12
247 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 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql server insert 13 32
How to open form using item in Listbox. 8 21
VB.NET 2008 - Windows Protected Your PC 7 17
vb.net class 3 17
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

832 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