Download mysql data and loop

Hi, I create very simple mysql message system, when you enter specific tab page it opens mysql connection, downloads data to DataGridView, and that works great, what I am trying to do now, is when user clicks on that tab, program will request mysql data every 5 seconds or so, something like: Do, Loop I just don't know which place to put it.

Code:

---

Private Sub TabPage_chat_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabPage_chat.Enter
        Dim SDA As New MySqlDataAdapter
        Dim dbDataSet As New DataTable
        Dim bSource As New BindingSource
        Dim Command As New MySqlCommand
        Try

            con.Open()
            Dim Query As String
            Query = "select * from messages ORDER BY time DESC"
            Command = New MySqlCommand(Query, con)
            SDA.SelectCommand = Command
            SDA.Fill(dbDataSet)
            bSource.DataSource = dbDataSet
            DataGridView_czat.DataSource = bSource
            SDA.Update(dbDataSet)
            con.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()
        End Try
    End Sub

---

No loop. Use a Timer. Set the Interval, which is measured in milliseconds, to 5000. In the Tick event handler, retrieve the data. Also, use the SelectedIndexChanged event of the TabControl to Start and Stop the Timer.

Also, don't create a new DataTable each time. Just keep using the same DataTable. You can Clear its Rows and repopulate it or, if you do things right, you can retrieve only those records that have changed since the last retrieval. To achieve that you would need a column in the database table to store the last update time of the record. You can then retrieve only records that have a last update time that is later than the latest value already in the DataTable.

Can you help me with the code? After reading your suggestion this is the best I've come up with

Code:

---

Private Sub TheTimer()

        AddHandler Timer1.Elapsed, AddressOf Timer1_Tick
        Timer1.Interval = 5000
        Timer1.Start()
    End Sub


    Private Sub Timer1_Tick(ByVal obj As Object, ByVal e As EventArgs)

        Dim SDA As New MySqlDataAdapter
        Dim dbDataSet As New DataTable
        Dim bSource As New BindingSource
        Dim Command As New MySqlCommand
        Try
            con.Open()
            Dim Query As String
            Query = "select * from messages ORDER BY time DESC"
            Command = New MySqlCommand(Query, con)
            SDA.SelectCommand = Command
            SDA.Fill(dbDataSet)
            bSource.DataSource = dbDataSet
            DataGridView_chat.DataSource = bSource
            SDA.Update(dbDataSet)
            con.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()
        End Try

    End Sub


    Private Sub TabPage_chat_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabPage_czat.Enter
        TheTimer()
    End Sub

    Private Sub TabPage_chat_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabPage_czat.Leave

        Timer1.Stop()

    End Sub

---

ofcourse it's not working, I get the debug error

Quote:

---

Object reference not set to an instance of an object

---

Firstly, you're using a different Timer to the one I was suggesting. I said to handle the Tick event, which is a member of the Windows.Forms.Timer. You are using a Timers.Timer, which is evidenced by the fact that you're handling an Elapsed event.

That said, it may not actually matter, but then again it might. How did you add this Timer? Did you do it in code or in the designer? If you did it in code then your Elapsed event handler is being executed on a secondary thread, which is going to be an issue if you're trying to update the UI. What I would suggest is that you get rid of that Timer and the code you have. Add a Timer to your form in the designer, which will be a Windows.Forms.Timer. Set the Interval in the Properties window and create the Tick event handler in the usual way. Your code should look something like this:

vb.net Code:

Private adapter As New SqlDataAdapter("SELECT * FROM Messages WHERE LastUpdatedTime > @LastUpdatedTime", "connection string here")
Private parameter As SqlParameter = adapter.SelectCommand.Parameters.Add("@LastUpdatedTime", SqlDbType.DateTime2)
Private table As New DataTable
Private maxUpdatedTime As Date = Date.MinValue
 
Private Sub TabControl1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TabControl1.SelectedIndexChanged
    Timer1.Enabled = (TabControl1.SelectedTab Is TabPage_chat)
End Sub
 
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    parameter.Value = maxUpdatedTime
    adapter.Fill(table)
 
    BindingSource1.DataSource = table
    DataGridView_chat.DataSource = BindingSource1
 
    maxUpdatedTime = CDate(table.Compute("MAX(LastUpdatedTime)", String.Empty))
End Sub

That assumes that your database table has a column LastUpdatedTime that gets set to the current time whenever a record is updated. Without such a column, there's no way to tell which records are newer than the ones you already have.