DataGridView and BindingSource timer issue

Question

I have a DataGridview that displays the data and a TextBox that allows me to filter the BindingSource with an SQL query to display the data based on the input string. This is all working fine apart from once I have filtered the DataGridView the timer function I have is resetting it back so all the data is being displayed again. The timer is set on a 1000ms basis, so it will show the filtered result for a second, then revert back.

Heres my code:

Imports System.Data.OleDb

Public Class Form1

    Dim duraGadgetDB As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = C:\Users\Dave\Documents\duraGadget.mdb;"
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim sql As String = "SELECT * FROM duragadget"
        Dim connection As New OleDbConnection(duraGadgetDB)
        Dim dataadapter As New OleDbDataAdapter(sql, connection)
        Dim ds As New DataSet()
        connection.Open()
        dataadapter.Fill(ds, "dura")
        connection.Close()
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "dura"
        DataGridView1.Columns(5).Width = 300 
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        insert.Show()
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Dim currentRowID As Integer
        Dim scrollPosition As Integer = DataGridView1.FirstDisplayedScrollingRowIndex
        Try
            If DataGridView1.CurrentRow IsNot Nothing Then

                currentRowID = DataGridView1.CurrentRow.Index

                Dim sql As String = "SELECT * FROM duragadget"
                Dim connection As New OleDbConnection(duraGadgetDB)
                Dim dataadapter As New OleDbDataAdapter(sql, connection)
                Dim ds As New DataSet()
                connection.Open()
                dataadapter.Fill(ds, "dura")
                connection.Close()
                DataGridView1.DataSource = ds
                DataGridView1.DataMember = "dura"
                DataGridView1.CurrentCell = DataGridView1.Item(1, currentRowID)
            End If
        Catch ex As Exception

        End Try
        DataGridView1.FirstDisplayedScrollingRowIndex = scrollPosition
    End Sub

    Private Sub txtSearchOnSku_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearchOnSku.TextChanged
        Dim currentRowID As Integer
        Dim scrollPosition As Integer = DataGridView1.FirstDisplayedScrollingRowIndex
        Try
            If DataGridView1.CurrentRow IsNot Nothing Then
                currentRowID = DataGridView1.CurrentRow.Index
                Dim sql As String = "SELECT * FROM duragadget"
                Dim connection As New OleDbConnection(duraGadgetDB)
                Dim dataadapter As New OleDbDataAdapter(sql, connection)
                Dim ds As New DataSet()
                Dim dsView As New DataView
                Dim bs As New BindingSource()
                connection.Open()
                dataadapter.Fill(ds, "dura")
                connection.Close()
                dsView = ds.Tables(0).DefaultView
                bs.DataSource = dsView
                bs.Filter = "skuNo LIKE'" & txtSearchOnSku.Text & "*'"
                DataGridView1.DataSource = bs
                DataGridView1.CurrentCell = DataGridView1.Item(1, currentRowID)
            End If
        Catch ex As Exception
        End Try
        DataGridView1.FirstDisplayedScrollingRowIndex = scrollPosition
    End Sub
End Class

Can anyone tell me how to stop this happening?

Answer 1

Assuming you mean that, if you have entered a value in your text box to filter your results, that you then don't want you timer to fire and unfilter them...

You can either, check the contents of the TextBox in the Timer1_Tick routine;

If txtSearchOnSku.Text <> "" Then Exit Sub

Or you can disable your timer in the txtSearchOnSku_TextChanged routine;

If txtSearchOnSku.Text <> "" Then 
   Timer1.Stop
Else
   Timer1.Start
End If

Or, if you want to update your results once a second, based on you search, you can include the Filtering code in your Timer1_Tick routine.

As a quick point, you have alot of repeated code there. It may be worthwhile refactoring the repeated code out into another sub.