DataGridView TextBox How to colorate more and different values-CodePudding

enter image description here

I have a problem, with DataGridView's CellFormatting. The cells are colored by the search result from a TextBox. When I search for 2 numbers together, they are no longer colored. What should I do?

I state that I am using CONCAT_WS to load the table in DataGridView. What can I do?

Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
    Try
        If e.ColumnIndex = 3 And e.Value IsNot Nothing Or e.ColumnIndex = 4 And e.Value IsNot Nothing Or e.ColumnIndex = 5 And e.Value IsNot Nothing Or e.ColumnIndex = 6 And e.Value IsNot Nothing Or e.ColumnIndex = 7 And e.Value IsNot Nothing Then
            If String.IsNullOrEmpty(txtRefreshFiltra.Text) Then
                txtRefreshFiltra.Text = ""
            End If
            Dim sum6 As String = Convert.ToInt32(e.Value)
            If sum6 = txtRefreshFiltra.Text Then
                e.CellStyle.BackColor = Color.Gold
                e.CellStyle.ForeColor = Color.Black
            End If
        End If
    Catch ex As Exception
        MsgBox(ex.Message) 'show error msg'
    End Try
End Sub

enter image description here

My connection

  Public Sub FilterData(ValueToSearch As String)
    Try
        Dim SearchQyery As String = "SELECT * FROM LottoDeaBendata WHERE CONCAT_WS([Estratto1],[Estratto2],[Estratto3],[Estratto4],[Estratto5])LIKE'%" & ValueToSearch & "%'"
        Dim command As New SqlCommand(SearchQyery, connection)
        connection.Open()
        Dim table As New DataTable()
        Dim adapter As New SqlDataAdapter(command)
        adapter.Fill(table)
        DataGridView1.DataSource = table
        connection.Close()
    Catch ex As Exception
        MsgBox(ex.Message) 'show error msg'
    End Try
End Sub

Upload by button

  Private Sub btnFiltraDati_Click(sender As Object, e As EventArgs) Handles btnFiltraDati.Click
    FilterData(txtRefreshFiltra.Text)
End Sub

CodePudding user response：

There are a few things you may want to consider to color the cells as you describe. First, using the grids CellFormatting event may not necessarily be the best choice. This event will fire once for each cell when the data is loaded into the grid and this is fine and colors the cells as we want when the data is loaded, however, it also may fire if the user simply moves the cursor over a cell or the user scrolls the grid.

In both the cases of the user moving the cursor over a cell or scrolling the grid, clearly demonstrates that the cells will get re-colored unnecessarily. In other words, if the text in the text box has not changed or a cells value has not changed, then, re-coloring the cell(s) is superfluous.

Given this, the only drawback to NOT using the grids CellFormatting event is that our code will have to color the cells AFTER the grid is loaded with data. This means we will need a method to loop through all the rows of the grid to check and color the cells. This method to color all the cells is also going to be needed if the text in the text box changes. So, making this method makes sense so we can call it when the data is loaded and also when the text box text changes.

So given all this, to simplify things, I suggest you create a method that takes a single DataGridViewCell. The method will get the comma separated values from the text box and compare the cells value to the values in the text box and if one matches, then we simply color the cell, otherwise do not color the cell.

This method is below. First, we check if the cell is not null and actually has some value. Then, we take the string in the text box and split it on commas. Then we start a loop through all the values in the split string from the text box and if a match is found, then we simply color the cell and exit the for each loop.

Private Sub ColorCell(cell As DataGridViewCell)
    If (cell.Value IsNot Nothing) Then
        Dim target = cell.Value.ToString()
        If (Not String.IsNullOrEmpty(target)) Then
            cell.Style.BackColor = Color.White
            cell.Style.ForeColor = Color.Black
            Dim split = txtRefreshFiltra.Text.Trim().Split(",")
            For Each s As String In split
                If (target = s.Trim()) Then
                    cell.Style.BackColor = Color.Gold
                    cell.Style.ForeColor = Color.Black
                    Exit For
                End If
            Next
        End If
    End If
End Sub

The method above should simplify looping through all the rows in the grid to color the proper cells. This method may look something like below and we would call this method once after the data is loaded into the grid and also when the text in the text box changes.

Private Sub ColorAllCells()
    For Each row As DataGridViewRow In DataGridView1.Rows
        ColorCell(row.Cells(3))
        ColorCell(row.Cells(4))
        ColorCell(row.Cells(5))
        ColorCell(row.Cells(6))
        ColorCell(row.Cells(7))
    Next
End Sub

Lastly, the two event methods that we need to capture when the user changes a cells value in the grid in addition to when the user changes the text in the text box.

Private Sub txtRefreshFiltra_TextChanged(sender As Object, e As EventArgs) Handles txtRefreshFiltra.TextChanged
    ColorAllCells()
End Sub

Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
    If (e.RowIndex >= 0) Then
        If (e.ColumnIndex = 3 Or e.ColumnIndex = 4 Or e.ColumnIndex = 5 Or e.ColumnIndex = 6 Or e.ColumnIndex = 7) Then
            ColorCell(DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex))
        End If
    End If
End Sub

Edit per OP comment…

There are definitely a couple of things we can do to speed up the current code above, like take out the call to ColorAllCells in the text boxes TextChanged event.

The TextChanged event will fire when the user types a single character. Example, if the user wants to color the cells that are “55”, then, when the user types the first “5” into the text box… then the TextChanged event will fire and the code will color all the cells with “5”. Then when the user types the second “5”, the cells will be un-colored/colored again.

So, one way we can prevent the unnecessary coloring as described above is to NOT call the ColorAllCells method in the text boxes TextChanged event and simply put the ColorAllCells method into a button click. In other words, the user types what they want into the text box… THEN clicks a button to color the cells.

In addition, if you look at the ColorCell method, you may note that each time the method is called, the code is splitting the same string over and over with … Dim split = txtRefreshFiltra.Text.Trim().Split(",") … this is potentially redundant in a sense that the text … txtRefreshFiltra.Text may not have changed.

Therefore, to remedy this and only split the txtRefreshFiltra.Text when needed, we will use a global variable called something like currentSplit that holds the current split of the text box. Then we would “update” the currentSplit variable only when needed… like in its TextChanged event.

This should somewhat speed things up. In my small tests, it took approximately 10 seconds to color the cells the FIRST time. Subsequent coloring of the cells when the text box value was changed took less than 1 second.

First make a global variable to hold the current text boxes split values…

Dim currentSplit As String()

Then change the other methods as shown below…

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim dt = GetDT()
    DataGridView1.DataSource = dt
    currentSplit = txtRefreshFiltra.Text.Trim().Split(",")
End Sub


Private Sub ColorCell(cell As DataGridViewCell)
    If (cell.Value IsNot Nothing) Then
        Dim target = cell.Value.ToString()
        If (Not String.IsNullOrEmpty(target)) Then
            cell.Style.BackColor = Color.White
            cell.Style.ForeColor = Color.Black
            For Each s As String In currentSplit
                If (target = s.Trim()) Then
                    cell.Style.BackColor = Color.Gold
                    cell.Style.ForeColor = Color.Black
                    Exit For
                End If
            Next
        End If
    End If
End Sub



Private Sub txtRefreshFiltra_TextChanged(sender As Object, e As EventArgs) Handles txtRefreshFiltra.TextChanged
    currentSplit = txtRefreshFiltra.Text.Trim().Split(",")
End Sub

And finally, a button click event to color the cells. I added a stop watch to time how long it takes to color the cells.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim sw As Stopwatch = New Stopwatch()
    Debug.WriteLine("Starting coloring of cells -------")
    sw.Start()
    ColorAllCells()
    sw.Stop()
    Debug.WriteLine("It took: "   sw.Elapsed.TotalSeconds.ToString()   " to color the cells of 100,000 rows with 10 columns")
End Sub

CodePudding user response：

It doesn't work, with me, GetDT () tells me it's not declared. I put my connection on top, maybe I'm doing something wrong.