-
Oct 14th, 2019, 07:55 AM
#1
Thread Starter
Frenzied Member
[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
-
Oct 14th, 2019, 08:23 AM
#2
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...
-
Oct 14th, 2019, 08:24 AM
#3
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Oct 14th, 2019, 08:32 AM
#4
Addicted Member
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
-
Oct 14th, 2019, 10:42 AM
#5
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.
-
Oct 14th, 2019, 02:06 PM
#6
Re: Paint datagridview cell value based on value
Originally Posted by ChrisE
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 ""
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Oct 15th, 2019, 02:31 AM
#7
Thread Starter
Frenzied Member
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.
-
Oct 15th, 2019, 02:46 AM
#8
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.
-
Oct 15th, 2019, 03:01 AM
#9
Thread Starter
Frenzied Member
Re: Paint datagridview cell value based on value
Originally Posted by ChrisE
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
-
Oct 15th, 2019, 03:05 AM
#10
Thread Starter
Frenzied Member
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?
-
Oct 15th, 2019, 03:11 AM
#11
Addicted Member
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
-
Oct 15th, 2019, 03:21 AM
#12
Thread Starter
Frenzied Member
Re: Paint datagridview cell value based on value
Originally Posted by Goggy
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
-
Oct 15th, 2019, 03:23 AM
#13
Re: Paint datagridview cell value based on value
Originally Posted by schoemr
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
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.
-
Oct 15th, 2019, 03:24 AM
#14
Addicted Member
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
-
Oct 15th, 2019, 03:37 AM
#15
Addicted Member
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
-
Oct 15th, 2019, 03:45 AM
#16
Thread Starter
Frenzied Member
Re: Paint datagridview cell value based on value
Originally Posted by ChrisE
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
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?
-
Oct 15th, 2019, 03:52 AM
#17
Thread Starter
Frenzied Member
Re: Paint datagridview cell value based on value
Originally Posted by Goggy
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
-
Oct 15th, 2019, 03:55 AM
#18
Re: Paint datagridview cell value based on value
Originally Posted by schoemr
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.
-
Oct 15th, 2019, 04:06 AM
#19
Thread Starter
Frenzied Member
Re: Paint datagridview cell value based on value
Originally Posted by ChrisE
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
-
Oct 15th, 2019, 06:53 AM
#20
Re: Paint datagridview cell value based on value
Originally Posted by schoemr
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.
-
Oct 15th, 2019, 07:18 AM
#21
Thread Starter
Frenzied Member
Re: Paint datagridview cell value based on value
Originally Posted by topshot
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|