Test value in DataGridView column / Cell and change color if invalid? 2

[CraigBest]

Hi Folks, Merry Xmas!

Was hoping someone could help me out here. I have a DataGridView control in my project and in it, one column that I'd like to check the value of against a group of data to see if the value matches one of the items in that group. If it does, I'd like it to display normally, and if not I'd like to change the text color to red. The reference data group would most likely be a datatable but could also be the values in a combobox, or some other construct if necessary.

I remember doing this a few years back with ComponentOne's DBGrid control in a VB6 project but for the life of me I can't even figure out if it is possible to do it with the DataGridView control. Can anyone give me a herads-up or even better, an example of how to do this?

For backgrould I'm using VB.Net 2008, the grid has the column names pre-defined and uses a datatable created through OLEDB from an Access 2003 DB. If any of that means anything.

Happy holidays folks!

CraigHartz

 

[jebenson]

I believe you need to use the CellFormating event for this.

Private Sub DataGridView1_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting

    Dim bMatchFound as Boolean = False

    Dim iColumNumber as Integer = 7 'use the real column index here
    'Only check the column you want
    If e.ColumnIndex = iColumNumber Then 
        'some code to do your check of the data here
        If DataGridView1.Item(iColumNumber, e.RowIndex).Value = <ValueToCheck>
            'Match found
            bMatchFound = True
        Else
            'Match not found
            bMatchFound = False
        End If 
    End If  

    If Not bMatchFound Then 
        DataGridView1.Rows(e.RowIndex).Cells(iColumNumber).Style.ForeColor = Color.Red
    End If 


End Sub

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[ousoonerjoe]

Question: How many rows are typically going to be displayed and how many will be loaded? If the typical result set is relatively small, then jebenson's approach should work. Otherwise, you will need to iterate through each row testing the value when you load the grid, then on CellValidated as the user navigates through the grid. If the grid is ReadOnly, then CellValidated is not needed and quick loop through the rows at load will suffice.

I have found that the CellFormatting can actually return some rather unexpected painting results, but only happened with large result sets.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[CraigBest]

Thanks so much Guys, you both really helped. This is what I finally did to make it work, and it's working really well. Joe, the list is about 12,000 entries long but it only adds about a half-second to the return of the grid data, probably because I figured out how to so a search against a data table I already created (that was worth a bunch of time and help to me).

     Private Sub dgvInd_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvInd.CellFormatting

        Dim iColComm As Integer = 8
        Dim bFound As Boolean = False

        Dim drFoundRows() As DataRow
        Dim Rowz As DataRow

        If e.ColumnIndex = iColComm Then
            drFoundRows = dtComm.Select("CommodityName LIKE '" & e.Value & "'")

            For Each Rowz In drFoundRows
                bFound = True
                Exit For
            Next

            If bFound = False Then
                dgvInd.Rows(e.RowIndex).Cells(iColComm).Style.ForeColor = Color.Red()

            Else
                'see if the commodity is Use = X
                drFoundRows = dtComm.Select("CommodityName LIKE '" & e.Value & "' AND Use = 'X'")
                bFound = False

                For Each Rowz In drFoundRows
                    bFound = True
                    Exit For
                Next

                If bFound = False Then
                    dgvInd.Rows(e.RowIndex).Cells(iColComm).Style.ForeColor = Color.Blue()

                End If

            End If

        End If

CraigHartz