[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Chart Refresh Issues (vb express)

Hi,

I have an small problem refreshing charts (only in some cases) which i assume (as i am a novice) is connected to the datasets not being cleared correctly.

In the example, when i choose a selection from form (OEEGraph_DateRange) the period filter pick list (cboPeriodSelector) all the charts refresh and display ok for indexes 0-4.  On index 5 (user defined option), this also works fine independantly but when i then choose another option without closing the form, the data is not refreshing correctly - it is holding some of the data from the user defined option.

Its difficult to explain without showing an example.  I am using Vb Express 2010 and MS SQL 2012 Express, a one drive file is attached.  I have also enter the complete code below.  I will add a couple of screen shots too to try and explain.

Thanks

https://onedrive.live.com/redir?resid=C91A0203A9EBBE32!112&authkey=!AH6LDhMYpifzSdQ&ithint=file%2czip

Imports System.Data.SqlClient
Imports System.Windows.Forms.DataVisualization.Charting

Public Class OEEGraph_DateRange
    Dim SQL As New SQLControl

    Private Sub OEEGraph_DateRange_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        GetTimePeriod()
        ChartMA.Titles.Add("Machine Availability")
        ChartMA.Titles(0).Font = New Font("Arial", 12, FontStyle.Bold)
        ChartPR.Titles.Add("Performance v Standard")
        ChartPR.Titles(0).Font = New Font("Arial", 12, FontStyle.Bold)
        ChartQR.Titles.Add("Quality Rate")
        ChartQR.Titles(0).Font = New Font("Arial", 12, FontStyle.Bold)
        ChartOEE.Titles.Add("Overall Equipment Effectiveness")
        ChartOEE.Titles(0).Font = New Font("Arial", 12, FontStyle.Bold)
        datStart.CustomFormat = " "
        datStart.Format = DateTimePickerFormat.Custom
        datEnd.CustomFormat = " "
        datEnd.Format = DateTimePickerFormat.Custom
        datStart.Visible = False
        datEnd.Visible = False
        labDateFilter.Visible = False
        labTo.Visible = False
    End Sub

    Private Sub ChartPR_Customize(sender As System.Object, e As System.EventArgs) Handles ChartPR.Customize
        If ChartPR.ChartAreas(0).AxisX.CustomLabels.Count > 1 Then
            ChartPR.ChartAreas(0).AxisX.CustomLabels.RemoveAt(ChartPR.ChartAreas(0).AxisX.CustomLabels.Count - 1)
            ChartPR.ChartAreas(0).AxisX.CustomLabels.RemoveAt(0)
        End If
        If ChartPR.Series(0).Points.Count > 0 Then
            ChartPR.Series(0).Points.Remove(ChartPR.Series(0).Points.FindByValue(ChartPR.ChartAreas(0).AxisX.Maximum, "X"))
            ChartPR.Series(0).Points.Remove(ChartPR.Series(0).Points.FindByValue(ChartPR.ChartAreas(0).AxisX.Minimum, "X"))
        End If
    End Sub

    Private Sub ChartQR_Customize(sender As System.Object, e As System.EventArgs) Handles ChartQR.Customize
        If ChartQR.ChartAreas(0).AxisX.CustomLabels.Count > 1 Then
            ChartQR.ChartAreas(0).AxisX.CustomLabels.RemoveAt(ChartQR.ChartAreas(0).AxisX.CustomLabels.Count - 1)
            ChartQR.ChartAreas(0).AxisX.CustomLabels.RemoveAt(0)
        End If
        If ChartQR.Series(0).Points.Count > 0 Then
            ChartQR.Series(0).Points.Remove(ChartQR.Series(0).Points.FindByValue(ChartQR.ChartAreas(0).AxisX.Maximum, "X"))
            ChartQR.Series(0).Points.Remove(ChartQR.Series(0).Points.FindByValue(ChartQR.ChartAreas(0).AxisX.Minimum, "X"))
        End If
    End Sub

    Private Sub ChartMA_Customize(sender As System.Object, e As System.EventArgs) Handles ChartMA.Customize
        If ChartMA.ChartAreas(0).AxisX.CustomLabels.Count > 1 Then
            ChartMA.ChartAreas(0).AxisX.CustomLabels.RemoveAt(ChartMA.ChartAreas(0).AxisX.CustomLabels.Count - 1)
            ChartMA.ChartAreas(0).AxisX.CustomLabels.RemoveAt(0)
        End If
        If ChartMA.Series(0).Points.Count > 0 Then
            ChartMA.Series(0).Points.Remove(ChartMA.Series(0).Points.FindByValue(ChartMA.ChartAreas(0).AxisX.Maximum, "X"))
            ChartMA.Series(0).Points.Remove(ChartMA.Series(0).Points.FindByValue(ChartMA.ChartAreas(0).AxisX.Minimum, "X"))
        End If
    End Sub

    Private Sub ChartOEE_Customize(sender As System.Object, e As System.EventArgs) Handles ChartOEE.Customize
        If ChartOEE.ChartAreas(0).AxisX.CustomLabels.Count > 1 Then
            ChartOEE.ChartAreas(0).AxisX.CustomLabels.RemoveAt(ChartOEE.ChartAreas(0).AxisX.CustomLabels.Count - 1)
            ChartOEE.ChartAreas(0).AxisX.CustomLabels.RemoveAt(0)
        End If
        If ChartOEE.Series(0).Points.Count > 0 Then
            ChartOEE.Series(0).Points.Remove(ChartOEE.Series(0).Points.FindByValue(ChartOEE.ChartAreas(0).AxisX.Maximum, "X"))
            ChartOEE.Series(0).Points.Remove(ChartOEE.Series(0).Points.FindByValue(ChartOEE.ChartAreas(0).AxisX.Minimum, "X"))
        End If
    End Sub

    Private Sub GetTimePeriod()
        'Populate period selection combo box
        If SQL.SQLDataSet IsNot Nothing Then
            SQL.SQLDataSet.Clear()
        End If
        cboPeriodSelector.Items.Clear() 'empty combo box
        SQL.RunQuery("SELECT Period FROM tbl_PeriodSelector")
        'MsgBox(SQL.recordcount)
        If SQL.recordcount > 0 Then
            For Each r As DataRow In SQL.SQLDataSet.Tables(0).Rows
                cboPeriodSelector.Items.Add(r("Period")) 'period column
            Next
        ElseIf SQL.Exception <> "" Then
            MsgBox(SQL.Exception)
        End If
    End Sub

    Private Sub cboPeriodSelector_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cboPeriodSelector.SelectedIndexChanged

            SQL.SQLDataSet.Clear()
        If SQL.SQLDataSet.Tables(0).Rows.Count = 0 Then
            'Hourly data by date range
            If cboPeriodSelector.SelectedIndex = 0 Then
                datStart.Visible = False
                datEnd.Visible = False
                labDateFilter.Visible = False
                labTo.Visible = False
                'Hourly OEE
                SQL.RunQuery("SELECT OEE As OEE, Hour As Hour FROM qry_OEE_Hourly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartOEE.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Hourly")
                ChartOEE.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Hourly")
                Dim Series1 As Series = ChartOEE.Series(0)
                Series1.Name = "Hourly OEE"
                ChartOEE.Series(Series1.Name).XValueMember = "Hour"
                ChartOEE.Series(Series1.Name).YValueMembers = "OEE"
                ChartOEE.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartOEE.ChartAreas(0).AxisX.Title = "Hour"
                ChartOEE.ChartAreas(0).AxisY.Title = "% OEE"
                ChartOEE.ChartAreas(0).AxisX.Minimum = -1
                ChartOEE.ChartAreas(0).AxisX.Maximum = 24
                ChartOEE.ChartAreas(0).AxisY.Minimum = 0
                ChartOEE.ChartAreas(0).AxisY.Maximum = 120
                ChartOEE.ChartAreas(0).AxisX.Interval = 1
                ChartOEE.ChartAreas(0).AxisY.Interval = 20
                ChartOEE.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartOEE.Series(0).XValueType = ChartValueType.Int32
                'Hourly MA
                SQL.RunQuery("SELECT Productivity As Productivity, Hour As Hour FROM qry_OEE_Hourly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartMA.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Hourly")
                ChartMA.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Hourly")
                Dim Series2 As Series = ChartMA.Series(0)
                Series2.Name = "Hourly OEE"
                ChartMA.Series(Series2.Name).XValueMember = "Hour"
                ChartMA.Series(Series2.Name).YValueMembers = "Productivity"
                ChartMA.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartMA.ChartAreas(0).AxisX.Title = "Hour"
                ChartMA.ChartAreas(0).AxisY.Title = "% Machine Availability"
                ChartMA.ChartAreas(0).AxisX.Minimum = -1
                ChartMA.ChartAreas(0).AxisX.Maximum = 24
                ChartMA.ChartAreas(0).AxisY.Minimum = 0
                ChartMA.ChartAreas(0).AxisY.Maximum = 120
                ChartMA.ChartAreas(0).AxisX.Interval = 1
                ChartMA.ChartAreas(0).AxisY.Interval = 20
                ChartMA.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartMA.Series(0).XValueType = ChartValueType.Int32
                'Hourly PR
                SQL.RunQuery("SELECT Productivity As Productivity, Hour As Hour FROM qry_OEE_Hourly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartPR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Hourly")
                ChartPR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Hourly")
                Dim Series3 As Series = ChartPR.Series(0)
                Series2.Name = "Hourly OEE"
                ChartPR.Series(Series3.Name).XValueMember = "Hour"
                ChartPR.Series(Series3.Name).YValueMembers = "Productivity"
                ChartPR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartPR.ChartAreas(0).AxisX.Title = "Hour"
                ChartPR.ChartAreas(0).AxisY.Title = "% Performance v Standard"
                ChartPR.ChartAreas(0).AxisX.Minimum = -1
                ChartPR.ChartAreas(0).AxisX.Maximum = 24
                ChartPR.ChartAreas(0).AxisY.Minimum = 0
                ChartPR.ChartAreas(0).AxisY.Maximum = 120
                ChartPR.ChartAreas(0).AxisX.Interval = 1
                ChartPR.ChartAreas(0).AxisY.Interval = 20
                ChartPR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartPR.Series(0).XValueType = ChartValueType.Int32
                'Hourly QR
                SQL.RunQuery("SELECT QualityRate As QualityRate, Hour As Hour FROM qry_OEE_Hourly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartQR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Hourly")
                ChartQR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Hourly")
                Dim Series4 As Series = ChartQR.Series(0)
                Series2.Name = "Hourly OEE"
                ChartQR.Series(Series4.Name).XValueMember = "Hour"
                ChartQR.Series(Series4.Name).YValueMembers = "QualityRate"
                ChartQR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartQR.ChartAreas(0).AxisX.Title = "Hour"
                ChartQR.ChartAreas(0).AxisY.Title = "% Quality Rate"
                ChartQR.ChartAreas(0).AxisX.Minimum = -1
                ChartQR.ChartAreas(0).AxisX.Maximum = 24
                ChartQR.ChartAreas(0).AxisY.Minimum = 0
                ChartQR.ChartAreas(0).AxisY.Maximum = 120
                ChartQR.ChartAreas(0).AxisX.Interval = 1
                ChartQR.ChartAreas(0).AxisY.Interval = 20
                ChartQR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartQR.Series(0).XValueType = ChartValueType.Int32
                Exit Sub
                'Daily data by date range
            ElseIf cboPeriodSelector.SelectedIndex = 1 Then
                datStart.Visible = False
                datEnd.Visible = False
                labDateFilter.Visible = False
                labTo.Visible = False
                SQL.RunQuery("SELECT OEE As OEE, Day As Day FROM qry_OEE_Daily")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartOEE.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Daily")
                ChartOEE.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Daily")
                Dim Series1 As Series = ChartOEE.Series(0)
                Series1.Name = "Daily OEE"
                ChartOEE.Series(Series1.Name).XValueMember = "Day"
                ChartOEE.Series(Series1.Name).YValueMembers = "OEE"
                ChartOEE.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartOEE.ChartAreas(0).AxisX.Title = "Day"
                ChartOEE.ChartAreas(0).AxisY.Title = "% OEE"
                ChartOEE.ChartAreas(0).AxisX.Minimum = 0
                ChartOEE.ChartAreas(0).AxisX.Maximum = 8
                ChartOEE.ChartAreas(0).AxisY.Minimum = 0
                ChartOEE.ChartAreas(0).AxisY.Maximum = 120
                ChartOEE.ChartAreas(0).AxisX.Interval = 1
                ChartOEE.ChartAreas(0).AxisY.Interval = 20
                ChartOEE.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartOEE.Series(0).XValueType = ChartValueType.Int32
                'Daily MA
                SQL.RunQuery("SELECT Productivity As Productivity, Day As Day FROM qry_OEE_Daily")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartMA.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Daily")
                ChartMA.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Daily")
                Dim Series2 As Series = ChartMA.Series(0)
                Series2.Name = "Daily OEE"
                ChartMA.Series(Series2.Name).XValueMember = "Day"
                ChartMA.Series(Series2.Name).YValueMembers = "Productivity"
                ChartMA.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartMA.ChartAreas(0).AxisX.Title = "Day"
                ChartMA.ChartAreas(0).AxisY.Title = "% Machine Availability"
                ChartMA.ChartAreas(0).AxisX.Minimum = 0
                ChartMA.ChartAreas(0).AxisX.Maximum = 8
                ChartMA.ChartAreas(0).AxisY.Minimum = 0
                ChartMA.ChartAreas(0).AxisY.Maximum = 120
                ChartMA.ChartAreas(0).AxisX.Interval = 1
                ChartMA.ChartAreas(0).AxisY.Interval = 20
                ChartMA.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartMA.Series(0).XValueType = ChartValueType.Int32
                'Daily PR
                SQL.RunQuery("SELECT Productivity As Productivity, Day As Day FROM qry_OEE_Daily")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartPR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Daily")
                ChartPR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Daily")
                Dim Series3 As Series = ChartPR.Series(0)
                Series2.Name = "Daily OEE"
                ChartPR.Series(Series3.Name).XValueMember = "Day"
                ChartPR.Series(Series3.Name).YValueMembers = "Productivity"
                ChartPR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartPR.ChartAreas(0).AxisX.Title = "Day"
                ChartPR.ChartAreas(0).AxisY.Title = "% Performance v Standard"
                ChartPR.ChartAreas(0).AxisX.Minimum = 0
                ChartPR.ChartAreas(0).AxisX.Maximum = 8
                ChartPR.ChartAreas(0).AxisY.Minimum = 0
                ChartPR.ChartAreas(0).AxisY.Maximum = 120
                ChartPR.ChartAreas(0).AxisX.Interval = 1
                ChartPR.ChartAreas(0).AxisY.Interval = 20
                ChartPR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartPR.Series(0).XValueType = ChartValueType.Int32
                'Daily QR
                SQL.RunQuery("SELECT QualityRate As QualityRate, Day As Day FROM qry_OEE_Daily")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartQR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Daily")
                ChartQR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Daily")
                Dim Series4 As Series = ChartQR.Series(0)
                Series2.Name = "Daily OEE"
                ChartQR.Series(Series4.Name).XValueMember = "Day"
                ChartQR.Series(Series4.Name).YValueMembers = "QualityRate"
                ChartQR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartQR.ChartAreas(0).AxisX.Title = "Day"
                ChartQR.ChartAreas(0).AxisY.Title = "% Quality Rate"
                ChartQR.ChartAreas(0).AxisX.Minimum = 0
                ChartQR.ChartAreas(0).AxisX.Maximum = 8
                ChartQR.ChartAreas(0).AxisY.Minimum = 0
                ChartQR.ChartAreas(0).AxisY.Maximum = 120
                ChartQR.ChartAreas(0).AxisX.Interval = 1
                ChartQR.ChartAreas(0).AxisY.Interval = 20
                ChartQR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartQR.Series(0).XValueType = ChartValueType.Int32
                Exit Sub
                'Weekly data by date range
            ElseIf cboPeriodSelector.SelectedIndex = 2 Then
                datStart.Visible = False
                datEnd.Visible = False
                labDateFilter.Visible = False
                labTo.Visible = False
                'Weekly OEE
                SQL.RunQuery("SELECT OEE As OEE, Week As Week FROM qry_OEE_Weekly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartOEE.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Weekly")
                ChartOEE.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Weekly")
                Dim Series1 As Series = ChartOEE.Series(0)
                Series1.Name = "Weekly OEE"
                ChartOEE.Series(Series1.Name).XValueMember = "Week"
                ChartOEE.Series(Series1.Name).YValueMembers = "OEE"
                ChartOEE.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartOEE.ChartAreas(0).AxisX.Title = "Week"
                ChartOEE.ChartAreas(0).AxisY.Title = "% OEE"
                Dim sw As Integer
                Dim ew As Integer
                sw = DatePart(DateInterval.WeekOfYear, Date.Today, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays) + 1
                ew = sw - 6
                ChartOEE.ChartAreas(0).AxisX.Minimum = ew
                ChartOEE.ChartAreas(0).AxisX.Maximum = sw
                ChartOEE.ChartAreas(0).AxisY.Minimum = 0
                ChartOEE.ChartAreas(0).AxisY.Maximum = 120
                ChartOEE.ChartAreas(0).AxisX.Interval = 1
                ChartOEE.ChartAreas(0).AxisY.Interval = 20
                ChartOEE.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartOEE.Series(0).XValueType = ChartValueType.Int32
                'Weekly MA
                SQL.RunQuery("SELECT Productivity As Productivity , Week As Week FROM qry_OEE_Weekly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartMA.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Weekly")
                ChartMA.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Weekly")
                Dim Series2 As Series = ChartMA.Series(0)
                Series2.Name = "Productivity "
                ChartMA.Series(Series2.Name).XValueMember = "Week"
                ChartMA.Series(Series2.Name).YValueMembers = "Productivity"
                ChartMA.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartMA.ChartAreas(0).AxisX.Title = "Week"
                ChartMA.ChartAreas(0).AxisY.Title = "% Machine Availability"
                Dim sw2 As Integer
                Dim ew2 As Integer
                sw2 = DatePart(DateInterval.WeekOfYear, Date.Today, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays) + 1
                ew2 = sw2 - 6
                ChartMA.ChartAreas(0).AxisX.Minimum = ew2
                ChartMA.ChartAreas(0).AxisX.Maximum = sw2
                ChartMA.ChartAreas(0).AxisY.Minimum = 0
                ChartMA.ChartAreas(0).AxisY.Maximum = 120
                ChartMA.ChartAreas(0).AxisX.Interval = 1
                ChartMA.ChartAreas(0).AxisY.Interval = 20
                ChartMA.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartMA.Series(0).XValueType = ChartValueType.Int32
                'Weekly PR
                SQL.RunQuery("SELECT Productivity As Productivity , Week As Week FROM qry_OEE_Weekly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartPR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Weekly")
                ChartPR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Weekly")
                Dim Series3 As Series = ChartPR.Series(0)
                Series3.Name = "Productivity "
                ChartPR.Series(Series3.Name).XValueMember = "Week"
                ChartPR.Series(Series3.Name).YValueMembers = "Productivity"
                ChartPR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartPR.ChartAreas(0).AxisX.Title = "Week"
                ChartPR.ChartAreas(0).AxisY.Title = "% Productivity"
                Dim sw3 As Integer
                Dim ew3 As Integer
                sw3 = DatePart(DateInterval.WeekOfYear, Date.Today, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays) + 1
                ew3 = sw3 - 6
                ChartPR.ChartAreas(0).AxisX.Minimum = ew3
                ChartPR.ChartAreas(0).AxisX.Maximum = sw3
                ChartPR.ChartAreas(0).AxisY.Minimum = 0
                ChartPR.ChartAreas(0).AxisY.Maximum = 120
                ChartPR.ChartAreas(0).AxisX.Interval = 1
                ChartPR.ChartAreas(0).AxisY.Interval = 20
                ChartPR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartPR.Series(0).XValueType = ChartValueType.Int32
                'Weekly QR
                SQL.RunQuery("SELECT QualityRate As QualityRate , Week As Week FROM qry_OEE_Weekly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartQR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Weekly")
                ChartQR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Weekly")
                Dim Series4 As Series = ChartQR.Series(0)
                Series4.Name = "QualityRate "
                ChartQR.Series(Series4.Name).XValueMember = "Week"
                ChartQR.Series(Series4.Name).YValueMembers = "QualityRate"
                ChartQR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartQR.ChartAreas(0).AxisX.Title = "Week"
                ChartQR.ChartAreas(0).AxisY.Title = "% QualityRate"
                Dim sw4 As Integer
                Dim ew4 As Integer
                sw4 = DatePart(DateInterval.WeekOfYear, Date.Today, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFourDays) + 1
                ew4 = sw4 - 6
                ChartQR.ChartAreas(0).AxisX.Minimum = ew4
                ChartQR.ChartAreas(0).AxisX.Maximum = sw4
                ChartQR.ChartAreas(0).AxisY.Minimum = 0
                ChartQR.ChartAreas(0).AxisY.Maximum = 120
                ChartQR.ChartAreas(0).AxisX.Interval = 1
                ChartQR.ChartAreas(0).AxisY.Interval = 20
                ChartQR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartQR.Series(0).XValueType = ChartValueType.Int32
                Exit Sub
                'Monthly data by date range
            ElseIf cboPeriodSelector.SelectedIndex = 3 Then
                datStart.Visible = False
                datEnd.Visible = False
                labDateFilter.Visible = False
                labTo.Visible = False
                'Monthly OEE
                SQL.RunQuery("SELECT OEE As OEE, Month As Month FROM qry_OEE_Monthly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartOEE.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Monthly")
                ChartOEE.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Monthly")
                Dim Series1 As Series = ChartOEE.Series(0)
                Series1.Name = "Monthly OEE"
                ChartOEE.Series(Series1.Name).XValueMember = "Month"
                ChartOEE.Series(Series1.Name).YValueMembers = "OEE"
                ChartOEE.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartOEE.ChartAreas(0).AxisX.Title = "Month"
                ChartOEE.ChartAreas(0).AxisY.Title = "% OEE"
                ChartOEE.ChartAreas(0).AxisX.Minimum = 0
                ChartOEE.ChartAreas(0).AxisX.Maximum = 13
                ChartOEE.ChartAreas(0).AxisY.Minimum = 0
                ChartOEE.ChartAreas(0).AxisY.Maximum = 120
                ChartOEE.ChartAreas(0).AxisX.Interval = 1
                ChartOEE.ChartAreas(0).AxisY.Interval = 20
                ChartOEE.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartOEE.Series(0).XValueType = ChartValueType.Int32
                'Monthly MA
                SQL.RunQuery("SELECT Productivity As Productivity, Month As Month FROM qry_OEE_Monthly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartMA.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Monthly")
                ChartMA.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Monthly")
                Dim Series2 As Series = ChartMA.Series(0)
                Series2.Name = "Monthly OEE"
                ChartMA.Series(Series2.Name).XValueMember = "Month"
                ChartMA.Series(Series2.Name).YValueMembers = "Productivity"
                ChartMA.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartMA.ChartAreas(0).AxisX.Title = "Month"
                ChartMA.ChartAreas(0).AxisY.Title = "% Machine Availability"
                ChartMA.ChartAreas(0).AxisX.Minimum = 0
                ChartMA.ChartAreas(0).AxisX.Maximum = 13
                ChartMA.ChartAreas(0).AxisY.Minimum = 0
                ChartMA.ChartAreas(0).AxisY.Maximum = 120
                ChartMA.ChartAreas(0).AxisX.Interval = 1
                ChartMA.ChartAreas(0).AxisY.Interval = 20
                ChartMA.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartMA.Series(0).XValueType = ChartValueType.Int32
                'Monthly PR
                SQL.RunQuery("SELECT Productivity As Productivity, Month As Month FROM qry_OEE_Monthly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartPR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Monthly")
                ChartPR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Monthly")
                Dim Series3 As Series = ChartPR.Series(0)
                Series2.Name = "Monthly OEE"
                ChartPR.Series(Series3.Name).XValueMember = "Month"
                ChartPR.Series(Series3.Name).YValueMembers = "Productivity"
                ChartPR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartPR.ChartAreas(0).AxisX.Title = "Month"
                ChartPR.ChartAreas(0).AxisY.Title = "% Performance v Standard"
                ChartPR.ChartAreas(0).AxisX.Minimum = 0
                ChartPR.ChartAreas(0).AxisX.Maximum = 13
                ChartPR.ChartAreas(0).AxisY.Minimum = 0
                ChartPR.ChartAreas(0).AxisY.Maximum = 120
                ChartPR.ChartAreas(0).AxisX.Interval = 1
                ChartPR.ChartAreas(0).AxisY.Interval = 20
                ChartPR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartPR.Series(0).XValueType = ChartValueType.Int32
                'Monthly QR
                SQL.RunQuery("SELECT QualityRate As QualityRate, Month As Month FROM qry_OEE_Monthly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartQR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Monthly")
                ChartQR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Monthly")
                Dim Series4 As Series = ChartQR.Series(0)
                Series2.Name = "Monthly OEE"
                ChartQR.Series(Series4.Name).XValueMember = "Month"
                ChartQR.Series(Series4.Name).YValueMembers = "QualityRate"
                ChartQR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartQR.ChartAreas(0).AxisX.Title = "Month"
                ChartQR.ChartAreas(0).AxisY.Title = "% Quality Rate"
                ChartQR.ChartAreas(0).AxisX.Minimum = 0
                ChartQR.ChartAreas(0).AxisX.Maximum = 13
                ChartQR.ChartAreas(0).AxisY.Minimum = 0
                ChartQR.ChartAreas(0).AxisY.Maximum = 120
                ChartQR.ChartAreas(0).AxisX.Interval = 1
                ChartQR.ChartAreas(0).AxisY.Interval = 20
                ChartQR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartQR.Series(0).XValueType = ChartValueType.Int32
                Exit Sub
                'Yearly data by date range
            ElseIf cboPeriodSelector.SelectedIndex = 4 Then
                datStart.Visible = False
                datEnd.Visible = False
                labDateFilter.Visible = False
                labTo.Visible = False
                'Yearly OEE
                Dim sy As Integer
                Dim ey As Integer
                sy = Date.Today.Year
                ey = sy - 10
                SQL.RunQuery("SELECT OEE As OEE, Year As Year FROM qry_OEE_Yearly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartOEE.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Yearly")
                ChartOEE.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Yearly")
                Dim Series1 As Series = ChartOEE.Series(0)
                Series1.Name = "Yearly OEE"
                ChartOEE.Series(Series1.Name).XValueMember = "Year"
                ChartOEE.Series(Series1.Name).YValueMembers = "OEE"
                ChartOEE.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartOEE.ChartAreas(0).AxisX.Title = "Year"
                ChartOEE.ChartAreas(0).AxisY.Title = "% OEE"
                ChartOEE.ChartAreas(0).AxisX.Maximum = sy + 1
                ChartOEE.ChartAreas(0).AxisX.Minimum = ey - 1
                ChartOEE.ChartAreas(0).AxisY.Minimum = 0
                ChartOEE.ChartAreas(0).AxisY.Maximum = 120
                ChartOEE.ChartAreas(0).AxisX.Interval = 1
                ChartOEE.ChartAreas(0).AxisY.Interval = 20
                ChartOEE.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartOEE.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartOEE.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartOEE.Series(0).XValueType = ChartValueType.Int32
                'Yearly MA
                Dim sy2 As Integer
                Dim ey2 As Integer
                sy2 = Date.Today.Year
                ey2 = sy2 - 10
                SQL.RunQuery("SELECT MachineAvailability As MachineAvailability, Year As Year FROM qry_OEE_Yearly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartMA.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Yearly")
                ChartMA.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Yearly")
                Dim Series2 As Series = ChartMA.Series(0)
                Series2.Name = "MachineAvailability"
                ChartMA.Series(Series2.Name).XValueMember = "Year"
                ChartMA.Series(Series2.Name).YValueMembers = "MachineAvailability"
                ChartMA.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartMA.ChartAreas(0).AxisX.Title = "Year"
                ChartMA.ChartAreas(0).AxisY.Title = "% Machine Availability"
                ChartMA.ChartAreas(0).AxisX.Maximum = sy2 + 1
                ChartMA.ChartAreas(0).AxisX.Minimum = ey2 - 1
                ChartMA.ChartAreas(0).AxisY.Minimum = 0
                ChartMA.ChartAreas(0).AxisY.Maximum = 120
                ChartMA.ChartAreas(0).AxisX.Interval = 1
                ChartMA.ChartAreas(0).AxisY.Interval = 20
                ChartMA.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartMA.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartMA.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartMA.Series(0).XValueType = ChartValueType.Int32
                'Yearly PR
                Dim sy3 As Integer
                Dim ey3 As Integer
                sy3 = Date.Today.Year
                ey3 = sy3 - 10
                SQL.RunQuery("SELECT Productivity As Productivity, Year As Year FROM qry_OEE_Yearly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartPR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Yearly")
                ChartPR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Yearly")
                Dim Series3 As Series = ChartPR.Series(0)
                Series3.Name = "Productivity"
                ChartPR.Series(Series3.Name).XValueMember = "Year"
                ChartPR.Series(Series3.Name).YValueMembers = "Productivity"
                ChartPR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartPR.ChartAreas(0).AxisX.Title = "Year"
                ChartPR.ChartAreas(0).AxisY.Title = "% Productivity"
                ChartPR.ChartAreas(0).AxisX.Maximum = sy3 + 1
                ChartPR.ChartAreas(0).AxisX.Minimum = ey3 - 1
                ChartPR.ChartAreas(0).AxisY.Minimum = 0
                ChartPR.ChartAreas(0).AxisY.Maximum = 120
                ChartPR.ChartAreas(0).AxisX.Interval = 1
                ChartPR.ChartAreas(0).AxisY.Interval = 20
                ChartPR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartPR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartPR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartPR.Series(0).XValueType = ChartValueType.Int32
                'Yearly QR
                Dim sy4 As Integer
                Dim ey4 As Integer
                sy4 = Date.Today.Year
                ey4 = sy4 - 10
                SQL.RunQuery("SELECT QualityRate As QualityRate, Year As Year FROM qry_OEE_Yearly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    ChartQR.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Yearly")
                ChartQR.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Yearly")
                Dim Series4 As Series = ChartQR.Series(0)
                Series4.Name = "QualityRate"
                ChartQR.Series(Series4.Name).XValueMember = "Year"
                ChartQR.Series(Series4.Name).YValueMembers = "QualityRate"
                ChartQR.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                ChartQR.ChartAreas(0).AxisX.Title = "Year"
                ChartQR.ChartAreas(0).AxisY.Title = "% Quality Rate"
                ChartQR.ChartAreas(0).AxisX.Maximum = sy4 + 1
                ChartQR.ChartAreas(0).AxisX.Minimum = ey4 - 1
                ChartQR.ChartAreas(0).AxisY.Minimum = 0
                ChartQR.ChartAreas(0).AxisY.Maximum = 120
                ChartQR.ChartAreas(0).AxisX.Interval = 1
                ChartQR.ChartAreas(0).AxisY.Interval = 20
                ChartQR.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
                ChartQR.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                ChartQR.ChartAreas(0).AxisX.LabelStyle.Angle = 0
                ChartQR.Series(0).XValueType = ChartValueType.Int32
                Exit Sub
                'Specific data by date range
            ElseIf cboPeriodSelector.SelectedIndex = 5 Then
                datStart.CustomFormat = " "
                datStart.Format = DateTimePickerFormat.Custom
                datEnd.CustomFormat = " "
                datEnd.Format = DateTimePickerFormat.Custom
                datStart.Visible = True
                datEnd.Visible = False
                labDateFilter.Visible = True
                labTo.Visible = True
                datStart.Focus()
            End If
        End If
    End Sub

    Private Sub dateStart_DropDown(ByVal sender As Object, ByVal e As EventArgs) Handles datStart.DropDown
        RemoveHandler datStart.ValueChanged, AddressOf datStart_ValueChanged
    End Sub

    Private Sub datStart_CloseUp(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles datStart.CloseUp
        AddHandler datStart.ValueChanged, AddressOf datStart_ValueChanged
        Call datStart_ValueChanged(sender, EventArgs.Empty)
    End Sub

    Private Sub dateEnd_DropDown(ByVal sender As Object, ByVal e As EventArgs) Handles datEnd.DropDown
        RemoveHandler datEnd.ValueChanged, AddressOf datEnd_ValueChanged
    End Sub

    Private Sub datEnd_CloseUp(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles datEnd.CloseUp
        AddHandler datEnd.ValueChanged, AddressOf datEnd_ValueChanged
        Call datEnd_ValueChanged(sender, EventArgs.Empty)
    End Sub

    Private Sub datEnd_ValueChanged(sender As System.Object, e As System.EventArgs) Handles datEnd.ValueChanged
        'Date Range OEE
        datEnd.Format = DateTimePickerFormat.Long
        Me.Text = datEnd.Value.ToString
        Dim sd As DateTime = datStart.Value.ToString("yyyy/MM/dd")
        Dim ed As DateTime = datEnd.Value.ToString("yyyy/MM/dd")
        Dim Result As DialogResult
        Result = MsgBox("Are you sure the dates are correct?", vbQuestion + vbYesNo, "Message Alert")
        If Result = System.Windows.Forms.DialogResult.Yes Then
            Dim mind As DateTime
            Dim maxd As DateTime
            SQL.RunQuery("SELECT Max(Date) As MaxDate, Min(Date) As MinDate  FROM qry_OEE_DateRange")
            For Each i As Object In SQL.SQLDataSet.Tables(0).Rows
                maxd = i.Item("MaxDate")
                mind = i.Item("MinDate")
            Next
            Dim pmind As DateTime
            Dim pmaxd As DateTime
            pmind = sd
            pmaxd = ed
            If ed > maxd Then
                pmaxd = maxd
            Else
                pmaxd = ed
            End If
            If sd < mind Then
                pmind = mind
            Else
                pmind = sd
            End If
            Dim dd As String
            Dim xint As String
            dd = DateDiff(DateInterval.Day, pmaxd, pmind)
            xint = Math.Round(-dd / 5)
            'OEE by specific date range
            SQL.RunQuery("SELECT OEE As OEE, Date As Date FROM qry_OEE_DateRange WHERE Date >= '" & pmind.ToString("yyyy/MM/dd") & "' And Date <= '" & pmaxd.ToString("yyyy/MM/dd") & "' ")
            If SQL.recordcount = 0 Then
                MsgBox("No records available for this selection, please reselect", vbExclamation + vbOKOnly, "Message Alert")
                ChartOEE.Series(0).Points.Clear()
                Exit Sub
            End If
            SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_DateRange")
            ChartOEE.DataSource = SQL.SQLDataSet.Tables("qry_OEE_DateRange")
            Dim Series1 As Series = ChartOEE.Series(0)
            Series1.Name = "Date Range OEE"
            ChartOEE.Series(Series1.Name).XValueMember = "Date"
            ChartOEE.Series(Series1.Name).YValueMembers = "OEE"
            ChartOEE.Series(0).ChartType = SeriesChartType.Line
            ChartOEE.Series(0).BorderWidth = 3 ' line graph
            'ChartOEE.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
            ChartOEE.ChartAreas(0).AxisX.Title = "Date"
            ChartOEE.ChartAreas(0).AxisY.Title = "% OEE"
            ChartOEE.ChartAreas(0).AxisX.Maximum = pmaxd.ToOADate + 1
            ChartOEE.ChartAreas(0).AxisX.Minimum = pmind.ToOADate - 1
            ChartOEE.ChartAreas(0).AxisY.Minimum = 0
            ChartOEE.ChartAreas(0).AxisY.Maximum = 120
            ChartOEE.ChartAreas(0).AxisX.Interval = xint
            ChartOEE.ChartAreas(0).AxisY.Interval = 20
            ChartOEE.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
            ChartOEE.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
            ChartOEE.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 6)
            ChartOEE.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 6)
            ChartOEE.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
            ChartOEE.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
            ChartOEE.ChartAreas(0).AxisX.LabelStyle.Angle = 0
            ChartOEE.Series(0).XValueType = ChartValueType.DateTime
            Exit Sub
        Else
            datStart.Visible = False
            datEnd.Visible = False
            labDateFilter.Visible = False
            labTo.Visible = False
            SQL.SQLDataSet.Clear()
            ChartOEE.Series(0).Points.Clear()
            cboPeriodSelector.SelectedIndex = -1
            cboPeriodSelector.Focus()
            Exit Sub
        End If
    End Sub

    Private Sub datStart_ValueChanged(sender As Object, e As System.EventArgs) Handles datStart.ValueChanged
        Me.Text = datStart.Value.ToString
        datStart.Format = DateTimePickerFormat.Long
        datEnd.CustomFormat = " "
        datEnd.Format = DateTimePickerFormat.Custom
        datEnd.Visible = True
    End Sub
End Class

Open in new window

vbexample.doc
0
SweetingA
Asked:
SweetingA
  • 3
  • 2
1 Solution
 
CodeCruiserCommented:
Try changing

SQL.SQLDataSet.Clear()

to

SQL.SQLDataSet = New Dataset
0
 
Robert SchuttSoftware EngineerCommented:
I've noticed in the past that some data bindings can 'stick' and the best way to get rid of that is to set the .DataSource of the chart(s) to Nothing before setting to a new DataSet.
0
 
SweetingAAuthor Commented:
Tried both ideas, neither made a difference?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
SweetingAAuthor Commented:
I have worked out what it is doing and how to avoid it but why it doing it.

The difference in the user defined option is that i change the graph type from bar to line and somehow this is locking the data in the dataset.

When i change it bar to a bar graph everything refreshes fine.

Any ideas how to avoid this?
0
 
Robert SchuttSoftware EngineerCommented:
I'm trying to run the project but can't attach the database (too new). So I've just created some test data but no luck recreating the problem yet.

Note that I see in the code:
                Dim Series3 As Series = ChartPR.Series(0)
                Series2.Name = "Hourly OEE"

Open in new window

Same for Series4. So it seems to me the copy/pasted code has some errors. Could easily be that the problem stems from that. (well not from this but from a similar inconsistency)

Also, just to make sure: did you search your code and before every assignment to any chart.DataSource add a line before it: "chart.DataSource = Nothing" ?
0
 
SweetingAAuthor Commented:
I found a couple of mistypes in the copying and pasting thanks but the real issue is changing one chart t a line type, for some reason it just won't reset afterwards, it is hooked onto that.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now