Results 1 to 21 of 21

Thread: [RESOLVED] Paint datagridview cell value based on value

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Resolved [RESOLVED] Paint datagridview cell value based on value

    Hi, this is driving me insane from the weekend...


    I have a dgv and I wish to paint some cells based on value. It is working, but sometimes it can happen that there is no value (deleted, removed, never there). Then I get this error:

    Code:
    System.InvalidCastException: 'Operator '=' is not defined for type 'DBNull' and type 'Integer'.'

    Can someone please say me how to do this the correct way?


    Code:
    Private Sub TblAssessmentDataGridView_Paint(sender As Object, e As PaintEventArgs) Handles TblAssessmentDataGridView.Paint
    
    
    For i As Integer = 0 To TblAssessmentDataGridView.RowCount - 1
    
    
     If TblAssessmentDataGridView.Rows(i).Cells(6).Value = CInt(1) Then
        Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Green
     End If
    
    
    If TblAssessmentDataGridView.Rows(i).Cells(6).Value = CInt(2) Then
       Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Orange
    End If
    
    If TblAssessmentDataGridView.Rows(i).Cells(6).Value = CInt(3) Then
       Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Red
    End If
    
       Next

  2. #2
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Paint datagridview cell value based on value

    Something like this should work (not tested)
    Code:
    If Not IsDBNull(TblAssessmentDataGridView.Rows(i).Cells(6).Value) AndAlso TblAssessmentDataGridView.Rows(i).Cells(6).Value = CInt(1) Then...

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Paint datagridview cell value based on value

    Code:
    For i As Integer = 0 To TblAssessmentDataGridView.RowCount - 1
        if TblAssessmentDataGridView.Rows(i).Cells(6).Value is nothing then continue for
    
        If TblAssessmentDataGridView.Rows(i).Cells(6).Value = 1 Then
            Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Green
        End If
    
        If TblAssessmentDataGridView.Rows(i).Cells(6).Value = 2 Then
            Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Orange
        End If
    
        If TblAssessmentDataGridView.Rows(i).Cells(6).Value = 3 Then
            Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Red
        End If
    
    Next

  4. #4
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

    Re: Paint datagridview cell value based on value

    Just give an other option.

    Not knowing how you get the data, i'm guessing its coming from an ms-sql datasource.
    You could filter the dbNull by using IsNull statement in your query.

    IsNull(Field,-1) <---- when field is null the value is replaced with -1
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Paint datagridview cell value based on value

    another option

    Code:
     Private Sub ApplyFormatting()
            'Dim ColumnMode As DataGridViewAutoSizeColumnMode
            'Dim Autosize As Boolean
            For Each row As DataGridViewRow In DataGridView1.Rows
                'check the Value in Cell 2
    
                Select Case CInt(row.Cells(2).Value)
                    Case Is < 10
                        row.Cells(2).Style.BackColor = Color.Aquamarine
                        'wert Einzelpreis > 100
                    Case Is > 100
                        row.Cells(2).Style.BackColor = Color.Pink
                End Select
            Next
        End Sub
    then just call the Sub where you need it
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by ChrisE View Post
    another option

    Code:
     Private Sub ApplyFormatting()
            'Dim ColumnMode As DataGridViewAutoSizeColumnMode
            'Dim Autosize As Boolean
            For Each row As DataGridViewRow In DataGridView1.Rows
                'check the Value in Cell 2
    
                Select Case CInt(row.Cells(2).Value)
                    Case Is < 10
                        row.Cells(2).Style.BackColor = Color.Aquamarine
                        'wert Einzelpreis > 100
                    Case Is > 100
                        row.Cells(2).Style.BackColor = Color.Pink
                End Select
            Next
        End Sub
    then just call the Sub where you need it
    That includes the newrow and do anything to test that row.Cells(2).Value isn't nothing or ""

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Paint datagridview cell value based on value

    Hi guys, thank you very much for all your help. But my problem is not resolved yet

    I have try all this suggestions. I also try to use a breakpoint (there are many rows to loop through....)

    So I am not getting the DBNULL error anymore - one problem solved and I am not getting the error if there is nothing.


    So, if I do this:

    Code:
     Private Sub TblAssessmentDataGridView_Paint(sender As Object, e As PaintEventArgs) Handles TblAssessmentDataGridView.Paint
    
       For i As Integer = 0 To TblAssessmentDataGridView.RowCount - 1
    
           If TblAssessmentDataGridView.Rows(i).Cells(6).Value Is Nothing Then Continue For
           If TblAssessmentDataGridView.Rows(i).Cells(6).Value Is DBNull.Value Then Continue For
               
    
           If TblAssessmentDataGridView.Rows(i).Cells(6).Value = 1 Then
               Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Green
           End If
    
           If TblAssessmentDataGridView.Rows(i).Cells(6).Value = 2 Then
               Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Orange
           End If
    
           If TblAssessmentDataGridView.Rows(i).Cells(6).Value = 3 Then
               Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Red
           End If
    
            Next
    If there is nothing - the code will continue, if there is DBNull, the code will continue.

    But what if there is ""? I am still getting this error:


    System.InvalidCastException: 'Conversion from string " " to type 'Double' is not valid.'

    I have try this also:


    Code:
    If TblAssessmentDataGridView.Rows(i).Cells(6).Value Is Nothing Then Continue For
    If TblAssessmentDataGridView.Rows(i).Cells(6).Value Is DBNull.Value Then Continue For
    If TblAssessmentDataGridView.Rows(i).Cells(6).Value < 0 Then Continue For
    If TblAssessmentDataGridView.Rows(i).Cells(6).Value > 3 Then Continue For
    I even try this:

    Code:
    If TblAssessmentDataGridView.Rows(i).Cells(6).Value = "" Then Continue For
    Ps. That cell must paint if the value is 1,2,3 ANYTHING else; Nothing / "" / DDBull must not produce an error....

    I also try
    Code:
    On Error Resume Next
    but strange that the form is then very .... sluggish? (slow)
    Last edited by schoemr; Oct 15th, 2019 at 02:36 AM.

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Paint datagridview cell value based on value

    Hi

    try this way

    Code:
     Private Sub ApplyFormatting2()
            
            For Each row As DataGridViewRow In DataGridView1.Rows
                'check the Value in Cell 3
                If Not IsDBNull(row.Cells(3).Value) Then
                    Select Case CInt(row.Cells(3).Value)
                        Case Is < 10
                            row.Cells(3).Style.BackColor = Color.Aquamarine
                            'wert Einzelpreis > 100
                        Case Is > 100
                            row.Cells(3).Style.BackColor = Color.Pink
                    End Select
                End If
                'check for DBNull
                If IsDBNull(row.Cells(3).Value) Then
                    row.Cells(3).Style.BackColor = Color.CadetBlue
                End If
    
            Next
        End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by ChrisE View Post
    Hi

    try this way

    Code:
     Private Sub ApplyFormatting2()
            
            For Each row As DataGridViewRow In DataGridView1.Rows
                'check the Value in Cell 3
                If Not IsDBNull(row.Cells(3).Value) Then
                    Select Case CInt(row.Cells(3).Value)
                        Case Is < 10
                            row.Cells(3).Style.BackColor = Color.Aquamarine
                            'wert Einzelpreis > 100
                        Case Is > 100
                            row.Cells(3).Style.BackColor = Color.Pink
                    End Select
                End If
                'check for DBNull
                If IsDBNull(row.Cells(3).Value) Then
                    row.Cells(3).Style.BackColor = Color.CadetBlue
                End If
    
            Next
        End Sub
    Hi Chris,

    Thank you for your time... I am still getting the same error.... I don't understand this This is very frustrating.....

    I wonder.....what if..... say I declare that cell as a double....?

    Let me try quick

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Paint datagridview cell value based on value

    this seems to work!!

    Code:
    For i As Integer = 0 To TblRiskAssessmentDataGridView.RowCount - 1
    
                If TblAssessmentDataGridView.Rows(i).Cells(6).Value Is Nothing Then Continue For
                If TblAssessmentDataGridView.Rows(i).Cells(6).Value Is DBNull.Value Then Continue For
    
                Dim cell As Double = Val(TblAssessmentDataGridView.Rows(i).Cells(6).Value)
    
                If cell = Nothing Then Continue For
    
                If cell = 1 Then
                    Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Green
                End If
                If cell = 2 Then
                    Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Yellow
                End If
                If cell = 3 Then
                    Me.TblAssessmentDataGridView.Rows(i).Cells(6).Style.BackColor = Color.Orange
                End If
    
            Next
    what do you think?

  11. #11
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

    Re: Paint datagridview cell value based on value

    You could also try the IsNumeric function... I can't test it for DbNUll but for all others it seems to work...

    Code:
            Dim Obj As Object = Nothing
            Dim Str As String = "A"
            Dim Int As Integer = 1
    
            Debug.Print($"{IsNumeric(Obj)}, {IsNumeric(Str)}, {IsNumeric(Int)}")
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  12. #12

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by Goggy View Post
    You could also try the IsNumeric function... I can't test it for DbNUll but for all others it seems to work...

    Code:
            Dim Obj As Object = Nothing
            Dim Str As String = "A"
            Dim Int As Integer = 1
    
            Debug.Print($"{IsNumeric(Obj)}, {IsNumeric(Str)}, {IsNumeric(Int)}")
    Thanks Goggy, We are not on the same page, in fact, I don't think we are reading the same book

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by schoemr View Post
    Hi Chris,

    Thank you for your time... I am still getting the same error.... I don't understand this This is very frustrating.....

    I wonder.....what if..... say I declare that cell as a double....?

    Let me try quick

    well I don't understand it.
    I did a test on the -Order Details- Table from the Northwind Database, and I checked the Quantity Field
    I changed the Database Table field to allow for Null Values, and the Formatting is correct
    see Image

    Name:  nullvalues.jpg
Views: 579
Size:  37.7 KB



    Code:
     Private Sub ApplyFormatting2()
            
            For Each row As DataGridViewRow In DataGridView1.Rows
                'check the Value in Cell 3
                If Not IsDBNull(row.Cells(3).Value) Then
                    Select Case CInt(row.Cells(3).Value)
                        Case Is < 10
                            row.Cells(3).Style.BackColor = Color.Aquamarine
                        Case Is > 55
                            row.Cells(3).Style.BackColor = Color.Pink
                    End Select
                End If
                'check for DBNull
                If IsDBNull(row.Cells(3).Value) Then
                    row.Cells(3).Style.BackColor = Color.BlueViolet
                End If
    
                If row.IsNewRow Then
                    row.Cells(3).Style.BackColor = Color.CornflowerBlue
                    row.Cells(3).Value = "123"
                End If
    
            Next
        End Sub
    Last edited by ChrisE; Oct 15th, 2019 at 03:28 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

    Re: Paint datagridview cell value based on value

    Lol... ok... then disregard my rambling comments
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  15. #15
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

    Re: Paint datagridview cell value based on value

    Just.... well because i can.... (but probably because I am very stubborn)

    Code:
            Dim CurrentCell As DataGridViewCell
    
            For Each Row As DataGridViewRow In TblAssessmentDataGridView.Rows
                CurrentCell = Row.Cells(6)
                If IsNumeric(CurrentCell.Value) Then
                    Select Case Convert.ToInt32(CurrentCell.Value)
                        Case 1
                            CurrentCell.Style.BackColor = Color.Green
                        Case 2
                            CurrentCell.Style.BackColor = Color.Yellow
                        Case 3
                            CurrentCell.Style.BackColor = Color.Orange
                        Case Else
    
                    End Select
                End If
            Next
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  16. #16

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by ChrisE View Post
    well I don't understand it.
    I did a test on the -Order Details- Table from the Northwind Database, and I checked the Quantity Field
    I changed the Database Table field to allow for Null Values, and the Formatting is correct
    see Image

    Name:  nullvalues.jpg
Views: 579
Size:  37.7 KB



    Code:
     Private Sub ApplyFormatting2()
            
            For Each row As DataGridViewRow In DataGridView1.Rows
                'check the Value in Cell 3
                If Not IsDBNull(row.Cells(3).Value) Then
                    Select Case CInt(row.Cells(3).Value)
                        Case Is < 10
                            row.Cells(3).Style.BackColor = Color.Aquamarine
                        Case Is > 55
                            row.Cells(3).Style.BackColor = Color.Pink
                    End Select
                End If
                'check for DBNull
                If IsDBNull(row.Cells(3).Value) Then
                    row.Cells(3).Style.BackColor = Color.BlueViolet
                End If
    
                If row.IsNewRow Then
                    row.Cells(3).Style.BackColor = Color.CornflowerBlue
                    row.Cells(3).Value = "123"
                End If
    
            Next
        End Sub
    Chris thank you for helping me. Much appreciated....

    If I may; a value .. no a let's say a "cell" in a datagridview can be:

    - DBNull (there never was a value)
    - Nothing (there was a value but it is not there anymore)
    - Something (a number or string)

    There is nothing else it could be?

  17. #17

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by Goggy View Post
    Just.... well because i can.... (but probably because I am very stubborn)

    Code:
            Dim CurrentCell As DataGridViewCell
    
            For Each Row As DataGridViewRow In TblAssessmentDataGridView.Rows
                CurrentCell = Row.Cells(6)
                If IsNumeric(CurrentCell.Value) Then
                    Select Case Convert.ToInt32(CurrentCell.Value)
                        Case 1
                            CurrentCell.Style.BackColor = Color.Green
                        Case 2
                            CurrentCell.Style.BackColor = Color.Yellow
                        Case 3
                            CurrentCell.Style.BackColor = Color.Orange
                        Case Else
    
                    End Select
                End If
            Next
    This is also working I like this... Well done!

    Yes you can

  18. #18
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by schoemr View Post
    Chris thank you for helping me. Much appreciated....

    If I may; a value .. no a let's say a "cell" in a datagridview can be:

    - DBNull (there never was a value)
    - Nothing (there was a value but it is not there anymore)
    - Something (a number or string)

    There is nothing else it could be?
    if you want to check a String you have to change it from CInt to CStr

    EDIT:
    if you want to check for Dates
    then like this

    Code:
    Private Sub ApplyFormatting()
    
            For Each row As DataGridViewRow In DataGridView1.Rows
    'Check and color Dates based on Today's Date
                Dim d As Date = Now()
    
                If Not IsDBNull(row.Cells(5).Value) Then
                    Select Case CDate(row.Cells(5).Value) 'your Date Coulumn
                        Case Is >= d.AddDays(+15)
                            row.Cells(5).Style.BackColor = Color.Aquamarine
                        Case Is >= d.AddDays(-1)
                            row.Cells(5).Style.BackColor = Color.Brown
                        Case Else
                            row.Cells(5).Style.BackColor = Color.White
                    End Select
                End If
    
                If IsDBNull(row.Cells(5).Value) Then
                    row.Cells(5).Style.BackColor = Color.CadetBlue
                End If
            Next
        End Sub
    Last edited by ChrisE; Oct 15th, 2019 at 04:03 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  19. #19

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by ChrisE View Post
    if you want to check a String you have to change it from CInt to CStr

    EDIT:
    if you want to check for Dates
    then like this

    Code:
    Private Sub ApplyFormatting()
    
            For Each row As DataGridViewRow In DataGridView1.Rows
    'Check and color Dates based on Today's Date
                Dim d As Date = Now()
    
                If Not IsDBNull(row.Cells(5).Value) Then
                    Select Case CDate(row.Cells(5).Value) 'your Date Coulumn
                        Case Is >= d.AddDays(+15)
                            row.Cells(5).Style.BackColor = Color.Aquamarine
                        Case Is >= d.AddDays(-1)
                            row.Cells(5).Style.BackColor = Color.Brown
                        Case Else
                            row.Cells(5).Style.BackColor = Color.White
                    End Select
                End If
    
                If IsDBNull(row.Cells(5).Value) Then
                    row.Cells(5).Style.BackColor = Color.CadetBlue
                End If
            Next
        End Sub
    Thank you very much Chris

  20. #20
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by schoemr View Post
    But what if there is ""? I am still getting this error:


    System.InvalidCastException: 'Conversion from string " " to type 'Double' is not valid.'

    I even try this:

    Code:
    If TblAssessmentDataGridView.Rows(i).Cells(6).Value = "" Then Continue For
    FWIW, you should have Trimmed the value. You'll note it was complaining about a single space (" "), but you were testing for an empty string (""), which is clearly not the same value. Glad you got it sorted out.

  21. #21

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Paint datagridview cell value based on value

    Quote Originally Posted by topshot View Post
    FWIW, you should have Trimmed the value. You'll note it was complaining about a single space (" "), but you were testing for an empty string (""), which is clearly not the same value. Glad you got it sorted out.
    Hi TopShot, you are very right! This really was driving me mad.. It could not understand this at all.. one assessment that was giving this error, the other assessment did not. I do realize now it was complaining about a space all along!!! GRRRRRRRRRR!!!!!!!!!!!!!!!!!!!

    At least now I am taking care of Nothing and DBNull as well

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width