hopefully someone here might be able to help me with a huge problem I'm having. I've got a database table that gets update every few seconds and I want to be able to view it semi real time over a web interface so I create a gridview inside an ASP.NET update panel and set my timer.tick event as the trigger for the panel than in my code behind I databind the database table to the gridview on the timer.tick event, I know the timer is running but the update panel refuses to update. I know I could use a sqldatasource instead of databinding however I needed the extra flexibility of databinding compared to a data source because the table that the data is comign from changes depending on user input. Anyways the code is posted below its a fairly large web application so I've tried to only post the code behind that is actually being called and the entire .aspx page.
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="SelectJob.aspx.vb" Inherits="WebInterface.SelectJob"
EnableEventValidation="false" %>
<%@ Register Assembly="GMDatePicker" Namespace="GrayMatterSoft" TagPrefix="cc2" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<%@ Register Assembly="eWorld.UI, Version=2.0.6.2393, Culture=neutral, PublicKeyToken=24d65337282035f2"
Namespace="eWorld.UI" TagPrefix="ew" %>
WellDaq Data Systems - Select Job
CODE BEHIND
Imports system
Imports System.IO
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Web
Imports system.web.security
Imports system.web.ui
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HTMLControls
Imports System.Globalization
Partial Public Class SelectJob
Inherits System.Web.UI.Page
Dim JobType As String = "WellDaq"
Dim FromDate As Date
Dim ToDate As Date
Dim Interval As Single
Dim ExportInterval As Single
Dim Job As String
Dim Registers As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Check if the person attempting to access the page is logged in
If Session("loggedIn") <> True Then
Response.Redirect("LoggedOut.aspx")
End If
'set default views
mainpanelMultiView.SetActiveView(pageloadView)
rightbarMultiView.SetActiveView(pageloadmenuView)
'declare variables
Dim cmd As SqlCommand
Dim conn As SqlConnection
Dim reader As SqlDataReader
Dim reader2 As SqlDataReader
Dim adapter As SqlDataAdapter
Session("company") = Session("accessTable").Rows(0).Item("Auth0").ToString()
'check to see if you have new mail messages
Dim newMessages As New DataTable
conn = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=Internet;Integrated Security=True")
conn.Open()
adapter = New SqlDataAdapter("SELECT MessageID from messages WHERE [MessageTo] = '" & Session("username") & "' AND [MessageNew] = 'true'", conn)
adapter.Fill(newMessages)
conn.Close()
If newMessages.Rows.Count >= 1 Then
messageNotify.Font.Bold = True
messageNotify.Text = "You have " & newMessages.Rows.Count.ToString & " new messages!"
Else
messageNotify.Font.Bold = False
messageNotify.Text = "You have no new messages!"
End If
'Determine which jobs you can view based on company name
If Session("company") = "DKD" Then
conn = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=WellDat;Integrated Security=True")
cmd = New SqlCommand("Select * From [infotable]", conn)
conn.Open()
reader = cmd.ExecuteReader
WellDatJobs.DataSource = reader
WellDatJobs.DataBind()
reader.Close()
cmd.Dispose()
conn.Close()
conn = New SqlConnection("Data Source=(local)\WELLDAQ;Initial Catalog=EDRData;Integrated Security=True")
cmd = New SqlCommand("Select * From [tbljobs]", conn)
conn.Open()
reader2 = cmd.ExecuteReader
WellDaqJobs.DataSource = reader2
WellDaqJobs.DataBind()
reader2.Close()
cmd.Dispose()
conn.Close()
Else
conn = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=WellDat;Integrated Security=True")
cmd = New SqlCommand("Select * From [infotable] WHERE ([company] = '" & Session("company") & "')", conn)
conn.Open()
reader = cmd.ExecuteReader
WellDatJobs.DataSource = reader
WellDatJobs.DataBind()
reader.Close()
cmd.Dispose()
conn.Close()
conn = New SqlConnection("Data Source=(local)\WELLDAQ;Initial Catalog=EDRData;Integrated Security=True")
cmd = New SqlCommand("Select * From [tbljobs] Where JobData2 = '" & Session("company") & "'", conn)
conn.Open()
reader2 = cmd.ExecuteReader
WellDaqJobs.DataSource = reader2
WellDaqJobs.DataBind()
reader2.Close()
cmd.Dispose()
conn.Close()
End If
End Sub
Protected Sub WellDatJobs_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WellDatJobs.SelectedIndexChanged
'Clear our registers list
registerSelect.Items.Clear()
SelectedRegister.Items.Clear()
'set some graphical variables
loadRealtime.Visible = True
loadHistorical.Visible = True
jobtableLabel.Text = WellDatJobs.SelectedValue.ToString
'change multiview to Select Job info
mainpanelMultiView.SetActiveView(selectjobView)
rightbarMultiView.SetActiveView(loadjobsView)
'declare variables
Dim cmd As SqlCommand
Dim conn As SqlConnection
Dim reader As SqlDataReader
Dim adapter As SqlDataAdapter
Dim registersTable As New DataTable
'save what job were looking at
Job = WellDatJobs.SelectedValue
Session("Job") = Job
JobType = "WellDat"
'populate job info view based on input from job select view
conn = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=WellDat;Integrated Security=True")
cmd = New SqlCommand("Select [Description], [Value] From [" & Job & "info]", conn)
'open SQL connection to grab job data
conn.Open()
reader = cmd.ExecuteReader
jobData.DataSource = reader
jobData.DataBind()
reader.Close()
cmd.Dispose()
'populate registers select box
adapter = New SqlDataAdapter("Select column_name as registers From information_schema.columns WHERE table_name = '" & Job & "'", conn)
adapter.Fill(registersTable)
registerSelect.DataSource = registersTable
registerSelect.DataTextField = registersTable.Columns("registers").ColumnName.ToString()
registerSelect.DataValueField = registersTable.Columns("registers").ColumnName.ToString()
registerSelect.DataBind()
'Remove the registers that don't matter
registerSelect.Items.Remove("ID")
registerSelect.Items.Remove("TimeBased")
registerSelect.Items.Remove("TimeStamp")
registerSelect.Items.Remove("TimeStampSurvey")
registerSelect.Items.Remove("DepthBased")
registerSelect.Items.Remove("DepthStamp")
registerSelect.Items.Remove("DepthStampSurvey")
registerSelect.Items.Remove("WashID")
registerSelect.Items.Remove("Comment")
registerSelect.Items.Remove("AlarmName")
registerSelect.Items.Remove("AlarmDescription")
registerSelect.Items.Remove("AlarmCleared")
registerSelect.Items.Remove("(none)")
'close job info grabbing SQL connection
conn.Close()
'set our min and max time selection
Dim timetable As New DataTable
Dim culture As New CultureInfo("en-CA")
adapter = New SqlDataAdapter("SELECT MIN(TimeStamp) AS MinTime, MAX(TimeStamp) AS MaxTime FROM " & Job, conn)
adapter.Fill(timetable)
FromDatePicker.Culture = culture
ToDatePicker.Culture = culture
FromDatePicker.Date = timetable.Rows(0).Item("MinTime").ToString
ToDatePicker.Date = timetable.Rows(0).Item("MaxTime").ToString
conn.Close()
End Sub
Protected Sub loadRealtime_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles loadRealtime.Click
'Set views
mainpanelMultiView.SetActiveView(DatatableView)
'Set some variables
FromDate = FromDatePicker.Date
Interval = DropDownList1.SelectedValue.ToString
Job = Session("job")
'Setup our datatable timer
DataTableTimer.Enabled = True
'Dim TimerInterval As Integer
'TimerInterval = Interval
'If Interval < 1 Then TimerInterval = 1
'DataTableTimer.Interval = TimerInterval * 60000
DataTableTimer.Interval = 10000
' If JobType = "WellDat" Then
WelldatDataTable()
'End If
End Sub
Protected Sub grabWellDatRegisters()
Dim qString As String = "[ID], [TimeStamp], "
'generate a dynamic SQL list to be used in "Interface.aspx"
For Each item As ListItem In selectedRegister.Items
qString = qString & "[" & item.Value & "], "
Next
Dim qStringArray() As Char = {Chr(44)}
qString = qString.Remove(qString.Length - 2)
Registers = qString
Session("Registers") = Registers
End Sub
Private Function GetData(ByVal AllData As DataTable, ByVal ExportKey As Boolean, ByVal DataStart As Object, ByVal DataStop As Object, ByVal Interval As Single) As DataTable
'modified timing code from WellDat source code
'Alldata is the formatted data table that is displayed in the interface need to add handling for if timestamp or datestamp column are not accessible, possibly add it to this function later for now its seperate.
'ExportKey = true than time based, false than depth based
'Interval = timing interval
If Interval = 0 Then
Return AllData
Else
'Filter to only include the data for the requested interval
Dim FilterData As DataTable = AllData.Clone
If AllData.Rows.Count > 0 Then
If ExportKey = True Then
'TimeBased
Dim ThisDiff As TimeSpan
Dim LastDiff As TimeSpan = TimeSpan.MinValue
Dim DataIndex As Integer = 0
Dim RowTime As Date = DirectCast(DataStart, Date).Date.AddMinutes(Int(DirectCast(DataStart, Date).TimeOfDay.TotalMinutes / Interval) * Interval)
Do 'For each time step
Debug.WriteLine(RowTime)
Do 'For each data record
'Figure out the difference between the desired time and the current row
If DataIndex > 0 Then LastDiff = DirectCast(AllData.Rows(DataIndex - 1).Item("TimeStamp"), Date).Subtract(RowTime)
ThisDiff = DirectCast(AllData.Rows(DataIndex).Item("TimeStamp"), Date).Subtract(RowTime)
Debug.WriteLine(ThisDiff)
'Check if the current row is ahead of the desired time
If ThisDiff.CompareTo(TimeSpan.Zero) >= 0 OrElse DataIndex = AllData.Rows.Count - 1 Then
Debug.WriteLine("we made it here")
'Thisdiff is the difference between the desired time and the current row
'Lastdiff is the difference between the desired time and the last row
'Use whatever is less as the data for this row
Dim IndexToUse As Integer
If DataIndex = 0 OrElse ThisDiff.Duration.CompareTo(LastDiff.Duration) < 0 Then
IndexToUse = DataIndex
Else
IndexToUse = DataIndex - 1
End If
'Copy row into FilterData
Dim NewRow As DataRow = FilterData.NewRow
'NewRow.ItemArray = AllData.Rows(IndexToUse).ItemArray
NewRow.ItemArray = AllData.Rows(IndexToUse).ItemArray
'Set the timestamp to the desired time for this row
NewRow("TimeStamp") = RowTime
FilterData.Rows.Add(NewRow)
Exit Do
End If
DataIndex += 1
Loop
RowTime = RowTime.AddMinutes(Interval)
Loop Until RowTime.CompareTo(DataStop) > 0
Else
'Depth(Based)
Dim RowDepth As Decimal = Int(DataStart / Interval + 0.0001) * Interval
Dim DataIndex As Integer = 0
Dim ThisDiff As Single
Dim LastDiff As Single
Do ' For each Depth step
Do ' For each data record
'Figure out the differences for the two current records
If DataIndex > 0 Then LastDiff = AllData.Rows(DataIndex - 1).Item("DepthStamp") - RowDepth
ThisDiff = AllData.Rows(DataIndex).Item("DepthStamp") - RowDepth
'If the dataindex points to a row that has a depth greater then or equal to the row depth
If ThisDiff >= 0 Or DataIndex = AllData.Rows.Count - 1 Then
'Decide weather to use this row or the previous one
Dim IndexToUse As Integer
If DataIndex = 0 OrElse Math.Abs(ThisDiff) < Math.Abs(LastDiff) Then
'Use Current row
IndexToUse = DataIndex
Else
'Use previous row
IndexToUse = DataIndex - 1
End If
'Copy row into FilterData
Dim NewRow As DataRow = FilterData.NewRow
NewRow.ItemArray = AllData.Rows(IndexToUse).ItemArray
'Set the timestamp to the desired time for this row
NewRow("DepthStamp") = RowDepth
FilterData.Rows.Add(NewRow)
Exit Do
End If
DataIndex += 1
Loop
RowDepth += Interval
RowDepth = Math.Round(RowDepth, 2)
Loop Until RowDepth > DataStop
End If
End If
Return FilterData
End If
End Function
Private Sub DataTableTimer_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataTableTimer.Tick
Dim conn As New SqlConnection()
Dim dt1 As New DataTable
Dim dt2 As New DataTable
Job = Session("Job")
Registers = Session("registers")
conn.ConnectionString = "Data Source=(local)\SQLEXPRESS;Initial Catalog=WellDat;Integrated Security=True"
Dim adapter As New SqlDataAdapter("SELECT " & Registers & " FROM " & Job, conn)
adapter.Fill(dt1)
adapter.Dispose()
Dim EndTime As Date
Dim dt As New DataTable
Dim adapter2 As New SqlDataAdapter
adapter2.SelectCommand = New SqlCommand("Select Max(TimeStamp) From " & Job, conn)
adapter2.Fill(dt)
adapter2.Dispose()
EndTime = Convert.ToDateTime(dt.Rows(0).Item(0).ToString())
dt.Reset()
dt2 = GetData(dt1, True, FromDate, EndTime, Interval)
'DataTable.Dispose()
DataTable.DataSource = dt2
DataTable.DataBind()
DataTable.Sort("TimeStamp", SortDirection.Descending)
UpdatePanel1.Update()
End Sub
Protected Sub WelldatDataTable()
'grab selected registers
grabWellDatRegisters()
'First load of our gridview
Dim conn As New SqlConnection()
Dim dt1 As New DataTable
Dim dt2 As New DataTable
conn.ConnectionString = "Data Source=(local)\SQLEXPRESS;Initial Catalog=WellDat;Integrated Security=True"
Dim adapter As New SqlDataAdapter("SELECT " & Registers & " FROM " & Job, conn)
adapter.Fill(dt1)
adapter.Dispose()
Dim EndTime As Date
Dim dt As New DataTable
Dim adapter2 As New SqlDataAdapter
adapter2.SelectCommand = New SqlCommand("Select Max(TimeStamp) From " & Job, conn)
adapter2.Fill(dt)
adapter2.Dispose()
EndTime = Convert.ToDateTime(dt.Rows(0).Item(0).ToString())
dt.Reset()
dt2 = GetData(dt1, True, FromDate, EndTime, Interval)
DataTable.DataSource = dt2
DataTable.DataBind()
DataTable.Sort("TimeStamp", SortDirection.Descending)
End Sub