Results 1 to 4 of 4

Thread: Download mysql data and loop

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2016
    Posts
    4

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Download mysql data and loop

    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.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2016
    Posts
    4

    Re: Download mysql data and loop

    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
    Object reference not set to an instance of an object

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Download mysql data and loop

    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:
    1. Private adapter As New SqlDataAdapter("SELECT * FROM Messages WHERE LastUpdatedTime > @LastUpdatedTime", "connection string here")
    2. Private parameter As SqlParameter = adapter.SelectCommand.Parameters.Add("@LastUpdatedTime", SqlDbType.DateTime2)
    3. Private table As New DataTable
    4. Private maxUpdatedTime As Date = Date.MinValue
    5.  
    6. Private Sub TabControl1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TabControl1.SelectedIndexChanged
    7.     Timer1.Enabled = (TabControl1.SelectedTab Is TabPage_chat)
    8. End Sub
    9.  
    10. Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    11.     parameter.Value = maxUpdatedTime
    12.     adapter.Fill(table)
    13.  
    14.     BindingSource1.DataSource = table
    15.     DataGridView_chat.DataSource = BindingSource1
    16.  
    17.     maxUpdatedTime = CDate(table.Compute("MAX(LastUpdatedTime)", String.Empty))
    18. 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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width