locked
color a datagridview cell based on data from another data table. RRS feed

  • Question

  • I am having a loading problem, the problem is my formating code takes way to long to load.

    What I have is a datagridview that is loaded by one table and I want to format the cells based on data from another table, the following code works, but it gets slower and slower as I add more records to the data.

        Private Sub dgvPQR_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvPQR.CellFormatting
            Try
                For x As Integer = 0 To dgvPQR.Rows.Count - 1
                    Me.dsobjPQR3.PQR3_Get_PQRNo.DefaultView.RowFilter = Nothing
                    Me.dsobjPQR3.PQR3_Get_PQRNo.DefaultView.RowFilter = "PQR_No = '" & dgvPQR.Rows(x).Cells("PQRNoDataGridViewTextBoxColumn").Value & "'"
                    If dgvPQR.Rows(x).Cells("PQRNoDataGridViewTextBoxColumn").Value <> Nothing Then
                        If Not IsDBNull(dsobjPQR3.PQR3_Get_PQRNo.DefaultView.Item(0).Item("Date")) Then
                            If (dsobjPQR3.PQR3_Get_PQRNo.DefaultView.Item(0).Item("Date")) <> "" Then
                                dgvPQR.Rows(x).DefaultCellStyle.BackColor = Color.Green
                                dgvPQR.Rows(x).DefaultCellStyle.ForeColor = Color.White
                            End If
                        End If
                    End If
                Next
            Catch ex As Exception
    
            End Try
    
        End Sub

    Where dgvPQR is my datagridview, dsobjPQR3 is my dataset, PQR3_Get_PQRNo is the table with the condition in it and PQR3_Master_All fills the datagridview.

    So my question is, is there another way of doing this that is faster?


    • Edited by RAuxier Friday, May 18, 2012 5:40 PM
    Friday, May 18, 2012 5:39 PM

Answers

  • Try this

    Private Sub dgvPQR_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvPQR.CellFormatting
            'Get the value of the PQR_No cell in this row
            Dim PQRCellValue As Object = dgvPQR.Item("PQR_No", e.RowIndex).Value
            'Test if the value is not DBNull
            If Not IsDBNull(PQRCellValue) Then
                'Convert that PQR_No to string
                Dim PQRNum As String = PQRCellValue.ToString()
                'Test if the string is not blank
                If Not String.IsNullOrEmpty(PQRNum) Then
                    'Now we have a good PQRNum. Use it to get the datarow from the PQR3_Get_PQRNo table
                    Dim foundRows() As DataRow = PQR3_Get_PQRNo.Select(String.Format("PQR_No = '{0}'", PQRNum))
                    If foundRows.Length > 0 Then
                        'We found a row in PQR3_Get_PQRNo table that has the same PQR_No.
                        'Now make sure that the Date is not null and apply the style to the cell.
                        If Not IsDBNull(foundRows(0)("Date")) Then
                            e.CellStyle.BackColor = Color.Green
                            e.CellStyle.ForeColor = Color.White
                        End If
                    End If
                End If
            End If
        End Sub

    • Marked as answer by RAuxier Friday, May 18, 2012 7:24 PM
    Friday, May 18, 2012 6:40 PM

All replies

  • You should not loop thru the entire DGV rows on every cell formating event. That's why it's so slow...

    You should access the cell from the datagridviewcellformattingeventargs, and set the style for it.

    From your code, it's kind of hard to figure out what the conditions you're looking for to apply the style to a cell. If you just list out the conditions, I can help you with the code.

    Friday, May 18, 2012 6:00 PM
  • My conditions are:

    If the "Date" field in the PQR3_Get_PQRNo table is not Null and not blank then the background color of the datagridview changes to green and the fore color to white.

    Where the PQR_No field is equal in both tables.

    Thank you.

    Friday, May 18, 2012 6:09 PM
  • Try this

    Private Sub dgvPQR_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvPQR.CellFormatting
            'Get the value of the PQR_No cell in this row
            Dim PQRCellValue As Object = dgvPQR.Item("PQR_No", e.RowIndex).Value
            'Test if the value is not DBNull
            If Not IsDBNull(PQRCellValue) Then
                'Convert that PQR_No to string
                Dim PQRNum As String = PQRCellValue.ToString()
                'Test if the string is not blank
                If Not String.IsNullOrEmpty(PQRNum) Then
                    'Now we have a good PQRNum. Use it to get the datarow from the PQR3_Get_PQRNo table
                    Dim foundRows() As DataRow = PQR3_Get_PQRNo.Select(String.Format("PQR_No = '{0}'", PQRNum))
                    If foundRows.Length > 0 Then
                        'We found a row in PQR3_Get_PQRNo table that has the same PQR_No.
                        'Now make sure that the Date is not null and apply the style to the cell.
                        If Not IsDBNull(foundRows(0)("Date")) Then
                            e.CellStyle.BackColor = Color.Green
                            e.CellStyle.ForeColor = Color.White
                        End If
                    End If
                End If
            End If
        End Sub

    • Marked as answer by RAuxier Friday, May 18, 2012 7:24 PM
    Friday, May 18, 2012 6:40 PM
  • Thank you, it works perfectly I only had to make a couple of minor changes.

        Private Sub dgvPQR_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvPQR.CellFormatting
            'Get the value of the PQR_No cell in this row
            Dim PQRCellValue As Object = dgvPQR.Item("PQRNoDataGridViewTextBoxColumn", e.RowIndex).Value
            'Test if the value is not DBNull
            If Not IsDBNull(PQRCellValue) Then
                'Convert that PQR_No to string
                Dim PQRNum As String = PQRCellValue.ToString()
                'Test if the string is not blank
                If Not String.IsNullOrEmpty(PQRNum) Then
                    'Now we have a good PQRNum. Use it to get the datarow from the PQR3_Get_PQRNo table
                    Dim foundRows() As DataRow = dsobjPQR3.PQR3_Get_PQRNo.Select(String.Format("PQR_No = '{0}'", PQRNum))
                    If foundRows.Length > 0 Then
                        'We found a row in PQR3_Get_PQRNo table that has the same PQR_No.
                        'Now make sure that the Date is not null and apply the style to the cell.
                        If Not IsDBNull(foundRows(0)("Date")) Then
                            If (foundRows(0)("Date")) <> Nothing Then
                                e.CellStyle.BackColor = Color.Green
                                e.CellStyle.ForeColor = Color.White
                            End If
                        End If
                    End If
                End If
            End If
    
        End Sub
    Thanks again.
    Friday, May 18, 2012 7:24 PM