Solved

Help with changing DataBridView's cell colors for unmatching data

Posted on 2014-03-06
6
272 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 27

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
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.

 

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

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
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…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now