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
Solved

Help with changing DataBridView's cell colors for unmatching data

Posted on 2014-03-06
6
274 Views
Last Modified: 2014-03-09
Hi,

I'm trying to use the code below to change the backcolor and textcolor of cell where the values are different when comparing rows as I loop through the Grid, but I receive the follow error:

Operator '=' is not defined for type 'DBNull' and type 'DBNull'.

For example (More columns in actual project) if my grid contains four rows the following columns:

CTRY      Item
BEL        BMW
BEL        Toyota
USA        Jeep
USA        Jeep

I would like to change the color of the item column for the first two rows because the data in the Item column is not the same.

Code:
 Dim i, j As Integer
        For i = 1 To C1Screen2.Rows.Count - 1
            For j = 1 To C1Screen2.Columns.Count - 1
                If C1Screen2.Rows(i - 1).Cells("CTRY").Value = C1Screen2.Rows(i).Cells("CTRY").Value Then
                    If Not C1Screen2.Rows(i - 1).Cells(j).Value = C1Screen2.Rows(i).Cells(j).Value Then
                        C1Screen2.Rows(i - 1).Cells(j).Style.BackColor = Drawing.Color.Red
                        C1Screen2.Rows(i - 1).Cells(j).Style.ForeColor = Drawing.Color.White
                        C1Screen2.Rows(i).Cells(j).Style.BackColor = Drawing.Color.Red
                        C1Screen2.Rows(i).Cells(j).Style.ForeColor = Drawing.Color.White
                    Else
                        C1Screen2.Rows(i - 1).Cells(j).Style.BackColor = Drawing.Color.Blue
                        C1Screen2.Rows(i - 1).Cells(j).Style.ForeColor = Drawing.Color.White
                        C1Screen2.Rows(i).Cells(j).Style.BackColor = Drawing.Color.Blue
                        C1Screen2.Rows(i).Cells(j).Style.ForeColor = Drawing.Color.White
                    End If
                End If
            Next
        Next

Open in new window

I used this code a while back in a different project not certain why it's not working now,

Thanks,

Victor
0
Comment
Question by:vcharles
  • 4
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39909141
I would suggest you add another if statement before following

If C1Screen2.Rows(i - 1).Cells("CTRY").Value = C1Screen2.Rows(i).Cells("CTRY").Value

as below
If Not IsDBNull( C1Screen2.Rows(i - 1).Cells("CTRY").Value) AND Not IsDBNull(C1Screen2.Rows(i).Cells("CTRY").Value)
0
 

Author Comment

by:vcharles
ID: 39909269
Hi,

I tried you approach but received error message:

Operator '=' is not defined for string " L534A1" and type default.  (L534A1 is the value in first row of the third column)

On Line :

If Not C1Screen2.Rows(i - 1).Cells(j).Value = C1Screen2.Rows(i).Cells(j).Value Then

Thanks.
0
 
LVL 28

Accepted Solution

by:
Ark earned 500 total points
ID: 39911511
This is because rows(rows.count-1) points to new row (if AllowUserToAddRow property is true), which contains null values - probably in your previous project this property was set to false. Just check
For i = 1 To C1Screen2.Rows.Count - 1
    If C1Screen2.Rows(i).IsNewRow Then Continue For '<=== ADDED

Open in new window

BTW, here is an example for multiple columns
    Private affectedColumns As String() = {"CTRY", "Item"} ' <== Add more columns you need to compare

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With DataGridView1
            .Columns.Add("CTRY", "CTRY")
            .Columns.Add("Item", "Item")
            .Rows.Add("BEL", "BMW")
            .Rows.Add("BEL", "TOYOTA")
            .Rows.Add("USA", "JEEP")
            .Rows.Add("USA", "JEEP")
        End With
    End Sub

    Private Function dgvContainsRow(ByVal dgv As DataGridView, ByVal row As DataGridViewRow) As Boolean
        For Each r As DataGridViewRow In dgv.Rows
            If r.IsNewRow OrElse r.Equals(row) Then Continue For
            Dim matchCount As Integer
            For Each c In affectedColumns
                If r.Cells(c).Value.Equals(row.Cells(c).Value) Then
                    matchCount += 1
                End If
            Next
            If matchCount = affectedColumns.Count Then Return True
        Next
        Return False
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        For Each r As DataGridViewRow In DataGridView1.Rows
            If r.IsNewRow Then Continue For
            Dim foreColor = Color.White
            Dim backColor = Color.Red
            If dgvContainsRow(DataGridView1, r) Then
                backColor = Color.Blue
            End If
            For Each c As DataGridViewCell In r.Cells
                c.Style.BackColor = backColor
                c.Style.ForeColor = foreColor
            Next
        Next
    End Sub

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:vcharles
ID: 39911608
Thanks, will try it and get back to you.
0
 

Author Comment

by:vcharles
ID: 39912510
Hi,

When I add If C1Screen2.Rows(i).IsNewRow Then  in my code thge error does not occur but

  If Not C1Screen2.Rows(i - 1).Cells(j).Value = C1Screen2.Rows(i).Cells(j).Value Then
                 
does not execute.

I tried you code but it does not consider that the SN values must be the same before comparing the rows, is there a way to modify it to work only for identical SN values?

For example If I have:

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

Only the first two rows should be compared because they both have SN = 10411. Also Is it possible to only change the color of the cells that do not match instead of the entire row.

Thanks,

Victor

Thanks,

Victor
0
 

Author Closing Comment

by:vcharles
ID: 39915913
Thank You.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
SQL Query Help Top 1 and Distinct? 6 35
Input parameteres to DragOver 2 19
How to repeat the data 4 17
Upgrade code from VS 2010 to VS 2015 7 12
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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