DataGridView refreshing fixed position in row filter textbox in vb.net

Viewed 41

I want when filtering rows in the textbox then the database is updated from other applications then I do a refresh I want the datagridview that I filter in the textbox in a fixed position and also when selected in the combobox as well. Maybe my code isn't perfect. So there may be the best solution.

Thanks VIEW DATAGRIDVIEW

Public Class Form1
    Dim Path As String = Application.StartupPath()
    Dim cn = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & Path & "; Extended Properties=dBase IV"
    Private WithEvents dt As New DataTable
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.PopulateComboBox()
        Me.PopulateDataGridView()
        Me.clearfiltercombobox()
    End Sub
    Private Sub PopulateDataGridView()
        Try
            Dim query = "select ITM,ART,QTY FROM EXAMPLE WHERE QTY > 0 AND BRAND = @BRAND"
            Using con As OleDbConnection = New OleDbConnection(cn)
                Using cmd As OleDbCommand = New OleDbCommand(query, con)
                    cmd.Parameters.AddWithValue("@BRAND", ComboBox1.SelectedValue)
                    Using adapter As New OleDbDataAdapter(cmd)
                        Dim dt As DataTable = New DataTable()
                        adapter.Fill(dt)
                        Me.DataGridView1.DataSource = dt
                    End Using
                End Using
            End Using
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally
        End Try
    End Sub
    Private Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        Try
            CType(DataGridView1.DataSource, DataTable).DefaultView.RowFilter = String.Format("ITM LIKE '%{0}%' OR ITM LIKE '%{0}%'", TextBox1.Text)
        Catch ex As Exception
            MsgBox("No match found")
        End Try
    End Sub
    Private Sub ComboBox1_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectionChangeCommitted
        Me.TextBox1.Text = ""
        Me.PopulateDataGridView()
    End Sub
    Private Sub PopulateComboBox()
        Dim query As String = "SELECT DISTINCT BRAND FROM EXAMPLE WHERE QTY > 0"
        Try
            Using con As OleDbConnection = New OleDbConnection(cn)
                Using sda As OleDbDataAdapter = New OleDbDataAdapter(query, con)
                    'Fill the DataTable with records from Table.
                    Dim dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    'Insert the Default Item to DataTable.
                    Dim row As DataRow = dt.NewRow()
                    row(0) = ""
                    dt.Rows.InsertAt(row, 0)
                    'Assign DataTable as DataSource.
                    ComboBox1.DataSource = dt
                    ComboBox1.DisplayMember = "BRAND"
                    ComboBox1.ValueMember = "BRAND"
                End Using
            End Using
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally
        End Try
    End Sub
    Private Sub clearfiltercombobox()
        Try
            ComboBox1.ResetText()
            TextBox1.Text = ""
            Dim query = "select ITM,ART,QTY FROM EXAMPLE WHERE QTY > 0"
            Using con As OleDbConnection = New OleDbConnection(cn)
                Using cmd As OleDbCommand = New OleDbCommand(query, con)
                    Using adapter As New OleDbDataAdapter(cmd)
                        Dim dt As DataTable = New DataTable()
                        adapter.Fill(dt)
                        Me.DataGridView1.DataSource = dt
                    End Using
                End Using
            End Using
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally
        End Try
    End Sub
    Private Sub Refreshonly_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Refreshonly.Click
        Me.clearfiltercombobox()
        DataGridView1.Refresh()
    End Sub
    Private Sub clearall_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Clearall.Click
        Me.clearfiltercombobox()
    End Sub
End Class
0 Answers
Related