Results 1 to 6 of 6

Thread: [RESOLVED] How to auto refresh data grid view every 5 minutes .

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    90

    Resolved [RESOLVED] How to auto refresh data grid view every 5 minutes .

    Hi All,

    I want my program to have function auto refresh every 5 minutes , and update data in my data grid view.
    here is my program look like.

    Name:  zzzzzzzzzzz.jpg
Views: 7723
Size:  22.2 KB

    here is my code.
    When i click btn_SHow it will auto load data grid view and automatic calculate formula below.
    I want every 5 minutes all will be refresh or update data.
    so it will look like a monitoring program.

    Code:
      Private Sub btn_Show_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Show_.Click
            Dim startDate = dtpFrom.Value.Date
            Dim endDate = dtpTo.Value.Date.AddDays(1)
            If txtTarget.Text <> "" Then
    
                connection.Open()
                sqlCmd = connection.CreateCommand
                sqlCmd.CommandText = ("WITH Src AS (SELECT station_no,CASE WHEN TestResult = 'ok' THEN 1 END AS ok,CASE WHEN TestResult = 'NG' " &
                 "THEN 1 END AS NG,CASE WHEN TestResult = 'RR' THEN 1 END AS RR FROM TEST_TABLE WHERE SQLDateTime >= @startDate AND SQLDateTime < @endDate and Station_No in (1,2,3,4,5,6))SELECT station_no, COUNT(ok) AS TotalOK,COUNT(NG) " &
                 "AS TotalNG,COUNT(RR) AS TotalRR,COUNT(ok) + COUNT(NG) + COUNT(RR) AS Total_Sum  FROM Src GROUP BY ROLLUP (Station_No)ORDER BY " &
                 "Station_No DESC")
                sqlCmd.Parameters.AddWithValue("@startDate", dtpFrom.Value.Date)
                sqlCmd.Parameters.AddWithValue("@endDate", dtpTo.Value.Date.AddDays(1))
    
                Dim dr As SqlDataReader = sqlCmd.ExecuteReader
                If dr.HasRows Then
                    dtRecords_C1.Load(dr)
                    dgvCell1_Muscatel_MPCA.DataSource = dtRecords_C1
                    dgvCell1_Muscatel_MPCA.Show()
                Else
                    MsgBox("NO DATA ", MsgBoxStyle.Critical, "ERROR")
                    dr.Close()
    
                End If
                connection.Close()
    
                'cell 2
                connection.Open()
                sqlCmd = connection.CreateCommand
                sqlCmd.CommandText = "WITH Src AS (SELECT station_no,CASE WHEN TestResult = 'ok' THEN 1 END AS ok,CASE WHEN TestResult = 'NG' " &
                 "THEN 1 END AS NG,CASE WHEN TestResult = 'RR' THEN 1 END AS RR FROM TEST_TABLE WHERE  SQLDateTime >= @startDate AND SQLDateTime < @endDate and Station_No in (7,8,9,10,11,12))SELECT station_no, COUNT(ok) AS TotalOK,COUNT(NG) " &
                 "AS TotalNG,COUNT(RR) AS TotalRR,COUNT(ok) + COUNT(NG) + COUNT(RR) AS Total_Sum FROM Src GROUP BY ROLLUP (Station_No)ORDER BY " &
                 "Station_No DESC"
                sqlCmd.Parameters.AddWithValue("@startDate", dtpFrom.Value.Date)
                sqlCmd.Parameters.AddWithValue("@endDate", dtpTo.Value.Date.AddDays(1))
                Dim dr_C2 As SqlDataReader = sqlCmd.ExecuteReader
                If dr_C2.HasRows Then
                    dtRecords_C2.Load(dr_C2)
                    dgvCell2_Muscaatel_MPCA.DataSource = dtRecords_C2
                Else
                    MsgBox("NO DATA ", MsgBoxStyle.Critical, "ERROR")
                    '  txtEfficiency.Text = Efficiency.ToString("p2")
                    dr_C2.Close()
    
                End If
                connection.Close()
    
    
    
                'CELL 1
                Dim rows = dtRecords_C1.Rows
                Dim lastRow = rows(rows.Count - 1)
                Dim totalOk = CInt(lastRow("TotalOK"))
                Dim total_Input = CInt(lastRow("Total_Sum"))
                Dim yield = totalOk / total_Input
    
                txtTotalOk_C1.Text = totalOk.ToString()
                txtTotalInput_C1.Text = total_Input.ToString()
                txtYieldRate_C1.Text = yield.ToString("p2")
    
                Dim Target = txtTarget.Text    'declare var Target assign value from txttarget * 6
                Dim Efficiency = total_Input / Target       'compute eff by dividing t_input /target
                txtEfficiency_C1.Text = Efficiency.ToString("p2")  'p2 * 100 and percent sign
                txtTarget.Text = Target
    
                'CELL2
                Dim rows_C2 = dtRecords_C2.Rows
                Dim lastRow_C2 = rows_C2(rows_C2.Count - 1)
                Dim totalOk_C2 = CInt(lastRow_C2("TotalOK"))
                Dim total_Input_C2 = CInt(lastRow_C2("Total_Sum"))
                Dim yield_C2 = totalOk_C2 / total_Input_C2
    
                txtTotalOk_C2.Text = totalOk_C2.ToString()
                txtTotalInput_C2.Text = total_Input_C2.ToString()
                txtYield_C2.Text = yield_C2.ToString("p2")
    
                '  Dim Target = txtTarget.Text * 6     'declare var Target assign value from txttarget * 6
                Dim Efficiency_C2 = total_Input_C2 / Target       'compute eff by dividing t_input /target
                txtEfficiency_C2.Text = Efficiency_C2.ToString("p2")  'p2 * 100 and percent sign
                txtTarget.Text = Target
            Else
                MsgBox("PLEASE KEY IN TARGET ", MsgBoxStyle.Critical, "ERROR")
    
            End If
        End Sub

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: How to auto refresh data grid view every 5 minutes .

    To run code at regular intervals, add a Timer to the form, set the Interval property, and put the code in the Tick event. To start/stop the timer, use the .Start and .Stop methods (or the .Enabled property).

    As it will be running regularly you probably don't want quite the same code (because it is likely show a "PLEASE KEY IN TARGET" message on a regular basis), so what I would recommend is to move all of the code (except the If/Else/EndIf and Msgbox lines) to a new Sub, then in the button you can have just this:
    Code:
            If txtTarget.Text <> "" Then
                SubName
            Else
                MsgBox("PLEASE KEY IN TARGET ", MsgBoxStyle.Critical, "ERROR")
            End If
    ...and in the timer event you can have this:
    Code:
            If txtTarget.Text <> "" Then
                SubName
            End If
    You may want to stop/start the timer in the button event, so that it only refreshes 5 minutes after a manual refresh.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    90

    Re: How to auto refresh data grid view every 5 minutes .

    hi Sir Si,

    is it possible to REFRESH only the data ? and show the latest data, not adding and showing again and again the data.

    below is my output now. it query new data again and again. i want it to be updated only. so must show 6 rows only.

    Name:  zzzzzzzzzzz.PNG
Views: 6751
Size:  19.7 KB

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

    Re: How to auto refresh data grid view every 5 minutes .

    Some people seem to forget all common sense when they start programming. Let's say that you had some information on a whiteboard and you wanted refresh that information. Would you just write the new information on the whiteboard and then be surprised that you had two sets of information? Of course you wouldn't, because it's bleedingly obvious that you need to clear off the old information first and then write the new information. Why would this be any different? Just clear out the old information first, then display the new information.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    90

    Re: How to auto refresh data grid view every 5 minutes .

    hi Sir Jm,

    thanks for that comment !

    I try 3 codes and the last one works !
    Code:
     '  dgvCell1_Muscatel_MPCA.Rows.Clear()
                'dgvCell1_Muscatel_MPCA.DataSource = Nothing
                dgvCell1_Muscatel_MPCA.DataSource.clear()

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

    Re: How to auto refresh data grid view every 5 minutes .

    Quote Originally Posted by BONITO View Post
    hi Sir Jm,

    thanks for that comment !

    I try 3 codes and the last one works !
    Code:
     '  dgvCell1_Muscatel_MPCA.Rows.Clear()
                'dgvCell1_Muscatel_MPCA.DataSource = Nothing
                dgvCell1_Muscatel_MPCA.DataSource.clear()
    So did you think about why each of those works or doesn't work? If the grid is bound then it displays the contents of its DataSource. The first one can't work because you can't add or remove rows directly in a bound grid. The second one doesn't help because you simply bind the same DataTable again after loading more rows into it so you never actually remove the old rows. The last one works because the DataSource of the grid is the DataTable that contains the rows so you actually are removing those old rows. It's still not really ideal though, given that it's relying on late-binding.

    Here's where you're actually populating the grid:
    Code:
                If dr_C2.HasRows Then
                    dtRecords_C2.Load(dr_C2)
                    dgvCell2_Muscaatel_MPCA.DataSource = dtRecords_C2
                Else
                    MsgBox("NO DATA ", MsgBoxStyle.Critical, "ERROR")
                    '  txtEfficiency.Text = Efficiency.ToString("p2")
                    dr_C2.Close()
    
                End If
    Why not simply add a line above that to Clear the Rows of the DataTable before you populate it?
    Code:
                dtRecords_C2.Rows.Clear()
    
                If dr_C2.HasRows Then
                    dtRecords_C2.Load(dr_C2)
                    dgvCell2_Muscaatel_MPCA.DataSource = dtRecords_C2
                Else
                    MsgBox("NO DATA ", MsgBoxStyle.Critical, "ERROR")
                    '  txtEfficiency.Text = Efficiency.ToString("p2")
                    dr_C2.Close()
    
                End If
    Simple. If you don't want to keep any old rows in the DataTable when adding new ones then just remove the old ones before you add the new ones.

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