1 Attachment(s)
[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.
Attachment 160265
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
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.
1 Attachment(s)
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.
Attachment 160303
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.
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()
Re: How to auto refresh data grid view every 5 minutes .
Quote:
Originally Posted by
BONITO
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.
