VB.NET Search with TextBox and DataGridView-CodePudding

From TextBox I search in DataGridView based on (Id), (DateTimePichKer), (Text column), and 5 Columns that have numbers. When I search for 1 or more numbers, it gives me the result in line, that is, I ask (20, 30, 40) it only gives me the sequence as I wrote it while I would like the 3 numbers to be found also in different positions, such as: (30,20,40) or (40,30, 20 ) and so on, how can I do?

enter image description here

CodePudding user response：

Solved Partially, up to 2 numbers works from the third (with 3 or 4 or 5 numbers) onwards no, it gives me the blank row with no data.

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
Private Sub btnFiltraDati_Click(sender As Object, e As EventArgs) Handles btnFiltraDati.Click
    FilterData(txtRefreshFiltra.Text)
End Sub
    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

CodePudding user response：

But now another problem came to me, with CellFormatting of DtaGridView the cells were colored by the search result, when I do with 2 numbers it no longer colors, what should 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