[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help with changing DataBridView's cell colors for unmatching data

Posted on 2014-03-06
6
Medium Priority
?
279 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
[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
  • 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 2000 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
Technology Partners: 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!

 

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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
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…

649 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