Search code examples
vb.netchartsdatasetseriesgraphing

Chart display issue in dataset to chart with multiple series

Noob to charts, this is my first attempt at one, so here goes. I've built my code based on the answer given in Separating Chart Series

The chart I'm using has only been modified to add series 2-7 and also to change the series type from bar to spline for all series given. Said changes made via the GUI.

Upon execution, the dataset is retrieved properly, the series names in the key area of the chart update and display properly, but the chart itself does not display, remains a blank box other than the color key at right.

Datatypes: DateTime = DateTime, MCEC & AECC = Decimal, all others = integers

EDITS:

  • Day 2: Tried (multiple) different code attempts, latest being one which wipes and reestablishes the series each time the chart is updated (by combobox on the screen), no change.
  • Day 3:
    • Further alternative code attempts, latest iteration shown below
    • Tried deleting and adding new chart (completely unmodified)
  • Day 14:
    • Have tried about 30 different variants since my last edit, still no success.
    • Have enlisted the assistance of a developer I know, tried his code variant (below), and both of us are stymied. The variant code gets a pair of charts to display, but there's no series displayed on them. At this point, i'm off in search of a plugin to hopefully make this work.

Day 14 Code:

Private Sub T3UpdateDisplay()
    '/////////////////////////////////
    'Query database and display results in the chart
    '/////////////////////////////////
    'build sql for processing
    sql = "SELECT DateTime, AECCRate1 AS AECC, MCECRate1 AS MCEC, Manila, Gosnell, Lepanto, Reginold, Promised_Land " _
     & "FROM pt_demand WHERE DateTime BETWEEN '" & ReportStart.ToString & "' AND '" & ReportEnd.ToString & "' " _
     & "ORDER BY DateTime ASC;"

    SetUpChart()
    GetAllDemands()

End Sub

Private Sub SetUpChart()

    Chart1.Series.Clear()
    Chart1.ChartAreas.Clear()

    Chart1.Series.Add("AECC")
    Chart1.ChartAreas.Add("AD")
    Chart1.Series("AECC").ChartArea = "AD"

    Chart1.Series.Add("MCEC")
    Chart1.ChartAreas.Add("MD")
    Chart1.Series("MCEC").ChartArea = "MD"

    Chart1.Series.Add("Manila")
    Chart1.ChartAreas.Add("M")
    Chart1.Series("Manila").ChartArea = "M"

    Chart1.Series.Add("Gosnell")
    Chart1.ChartAreas.Add("G")
    Chart1.Series("Gosnell").ChartArea = "G"

    Chart1.Series.Add("Lepanto")
    Chart1.ChartAreas.Add("L")
    Chart1.Series("Lepanto").ChartArea = "L"

    Chart1.Series.Add("Reginold")
    Chart1.ChartAreas.Add("R")
    Chart1.Series("Reginold").ChartArea = "R"

    Chart1.Series.Add("PromisedLand")
    Chart1.ChartAreas.Add("P")
    Chart1.Series("PromisedLand").ChartArea = "P"

    Chart1.Series("AECC").Color = Color.Green
    Chart1.Series("MCEC").Color = Color.Blue
    Chart1.Series("Manila").Color = Color.Red
    Chart1.Series("Gosnell").Color = Color.Gray
    Chart1.Series("Lepanto").Color = Color.Purple
    Chart1.Series("Reginold").Color = Color.Orange
    Chart1.Series("PromisedLand").Color = Color.Yellow

    Chart1.Series("AECC").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
    Chart1.Series("MCEC").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
    Chart1.Series("Manila").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
    Chart1.Series("Gosnell").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
    Chart1.Series("Lepanto").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
    Chart1.Series("Reginold").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
    Chart1.Series("PromisedLand").ChartType = DataVisualization.Charting.SeriesChartType.FastLine

    Chart1.Series("AECC").XValueType = DataVisualization.Charting.ChartValueType.Time
    Chart1.Series("MCEC").XValueType = DataVisualization.Charting.ChartValueType.Time
    Chart1.Series("Manila").XValueType = DataVisualization.Charting.ChartValueType.Time
    Chart1.Series("Gosnell").XValueType = DataVisualization.Charting.ChartValueType.Time
    Chart1.Series("Lepanto").XValueType = DataVisualization.Charting.ChartValueType.Time
    Chart1.Series("Reginold").XValueType = DataVisualization.Charting.ChartValueType.Time
    Chart1.Series("PromisedLand").XValueType = DataVisualization.Charting.ChartValueType.Time

End Sub

Private Sub GetAllDemands()
    'Connect to database do SQL for Dates Between
    On Error GoTo Err_Control
    Dim myDBCS As String = Main.DBCS    'create local copy of database connection string
    Dim ThisRecKey As Guid
    Dim sqlConnectionString As String = myDBCS

    Dim strGetDemandsSQL As String = sql
    Using sConnection As New SqlConnection(sqlConnectionString)
        sConnection.Open()
        Dim GetRecKeySQLCommand As New SqlCommand(strGetDemandsSQL, sConnection)
        'Get the Rec_Key
        Dim reader As SqlDataReader = GetRecKeySQLCommand.ExecuteReader()
        Dim ThisDateTime As Date

        While reader.Read()
            'Add Points
            Debug.Print(reader(0) & "    " & reader(1) & "    " & reader(2) & "    " & reader(3) & "    " & reader(4) & "    " & reader(5) & "    " & reader(6) & "    " & reader(7))
            ThisDateTime = reader(0)
            Chart1.Series("AECC").Points.AddXY(ThisDateTime.ToOADate(), CDbl(reader(1)))
            Chart1.Series("MCEC").Points.AddXY(ThisDateTime.ToOADate(), CDbl(reader(2)))
            Chart1.Series("M").Points.AddXY(ThisDateTime.ToOADate(), CInt(reader(3)))
            Chart1.Series("G").Points.AddXY(ThisDateTime.ToOADate(), CInt(reader(4)))
            Chart1.Series("L").Points.AddXY(ThisDateTime.ToOADate(), CInt(reader(5)))
            Chart1.Series("R").Points.AddXY(ThisDateTime.ToOADate(), CInt(reader(6)))
            Chart1.Series("P").Points.AddXY(ThisDateTime.ToOADate(), CInt(reader(7)))

        End While
        reader.Close()
        sConnection.Close()
    End Using

    Dim dblMin, dblMax As Double
    SetMinMax(dblMin, dblMax, "AECCRate1")
    Chart1.ChartAreas("AD").AxisY.Minimum = dblMin
    Chart1.ChartAreas("AD").AxisY.Maximum = dblMax
    Chart1.ChartAreas("AD").AxisX.Title = "AECC: " & Format(ReportStart, "yyyy/MM/dd HH:mm") & " to " & Format(ReportEnd, "yyyy/MM/dd HH:mm")

    SetMinMax(dblMin, dblMax, "MCECRate1")
    Chart1.ChartAreas("MD").AxisY.Minimum = dblMin
    Chart1.ChartAreas("MD").AxisY.Maximum = dblMax
    Chart1.ChartAreas("MD").AxisX.Title = "MCEC: " & Format(ReportStart, "yyyy/MM/dd HH:mm") & " to " & Format(ReportEnd, "yyyy/MM/dd HH:mm")

    SetMinMax(dblMin, dblMax, "Manila")
    Chart1.ChartAreas("M").AxisY.Minimum = dblMin
    Chart1.ChartAreas("M").AxisY.Maximum = dblMax
    Chart1.ChartAreas("M").AxisX.Title = "Manila: " & Format(ReportStart, "yyyy/MM/dd HH:mm") & " to " & Format(ReportEnd, "yyyy/MM/dd HH:mm")

    SetMinMax(dblMin, dblMax, "Gosnell")
    Chart1.ChartAreas("G").AxisY.Minimum = dblMin
    Chart1.ChartAreas("G").AxisY.Maximum = dblMax
    Chart1.ChartAreas("G").AxisX.Title = "Gosnell: " & Format(ReportStart, "yyyy/MM/dd HH:mm") & " to " & Format(ReportEnd, "yyyy/MM/dd HH:mm")

    SetMinMax(dblMin, dblMax, "Lepanto")
    Chart1.ChartAreas("L").AxisY.Minimum = dblMin
    Chart1.ChartAreas("L").AxisY.Maximum = dblMax
    Chart1.ChartAreas("L").AxisX.Title = "Lepanto: " & Format(ReportStart, "yyyy/MM/dd HH:mm") & " to " & Format(ReportEnd, "yyyy/MM/dd HH:mm")

    SetMinMax(dblMin, dblMax, "Reginold")
    Chart1.ChartAreas("R").AxisY.Minimum = dblMin
    Chart1.ChartAreas("R").AxisY.Maximum = dblMax
    Chart1.ChartAreas("R").AxisX.Title = "Reginold: " & Format(ReportStart, "yyyy/MM/dd HH:mm") & " to " & Format(ReportEnd, "yyyy/MM/dd HH:mm")

    SetMinMax(dblMin, dblMax, "Promised_Land")
    Chart1.ChartAreas("P").AxisY.Minimum = dblMin
    Chart1.ChartAreas("P").AxisY.Maximum = dblMax
    Chart1.ChartAreas("P").AxisX.Title = "Promised Land: " & Format(ReportStart, "yyyy/MM/dd HH:mm") & " to " & Format(ReportEnd, "yyyy/MM/dd HH:mm")

Exit_Here:

    Exit Sub
Err_Control:
    Select Case Err.Number
        Case Else
            Resume Exit_Here
    End Select
End Sub

Private Sub SetMinMax(ByRef Min As Double, ByRef Max As Double, FieldName As String)
    On Error GoTo Err_Control
    Dim myDBCS As String = Main.DBCS    'create local copy of database connection string
    Dim sqlConnectionString As String = myDBCS
    Dim strGetMaxSQL As String = "Select MAX(" & FieldName & ") FROM pt_demand WHERE DateTime Between '" & ReportStart.ToString & "' AND '" & ReportEnd.ToString & "'"
    Dim strGetMinSQL As String = "Select MIN(" & FieldName & ") FROM pt_demand WHERE DateTime Between '" & ReportStart.ToString & "' AND '" & ReportEnd.ToString & "'"
    Using sConnection As New SqlConnection(sqlConnectionString)
        sConnection.Open()
        'MAX
        Dim GetMaxSQLCommand As New SqlCommand(strGetMaxSQL, sConnection)
        Dim readerMax As SqlDataReader = GetMaxSQLCommand.ExecuteReader()

        While readerMax.Read()
            Max = CDbl(CInt(readerMax(0) + (0.01 * readerMax(0))))
        End While
        readerMax.Close()
        'MIN
        Dim GetMinSQLCommand As New SqlCommand(strGetMinSQL, sConnection)
        'Get the Rec_Key
        Dim readerMin As SqlDataReader = GetMinSQLCommand.ExecuteReader()

        While readerMin.Read()
            Min = CDbl(CInt(readerMin(0) - (0.01 * readerMin(0))))
        End While
        readerMin.Close()

        sConnection.Close()
    End Using

Exit_Here:

    Exit Sub
Err_Control:
    Select Case Err.Number
        Case Else
            Resume Exit_Here
    End Select
End Sub

Day 3 code here:

Private Sub T3UpdateDisplay()
            '/////////////////////////////////
    'Query database and display results in the chart
    '  borrowed from https://stackoverflow.com/questions/22638639/separating-chart-series
    '/////////////////////////////////
    'build sql for processing
    sql = "SELECT DateTime, MCECRate1 AS MCEC, AECCRate1 AS AECC, Manila, Gosnell, Lepanto, Reginold, Promised_Land " _
     & "FROM pt_demand WHERE DateTime BETWEEN '" & ReportStart.ToString & "' AND '" & ReportEnd.ToString & "' " _
     & "ORDER BY DateTime ASC;"

    '---- REMOVE FROM PRODUCTION ----
    'Main.Msg2User(sql, "query")

    Using ds = dbSelect_DataSet(sql)
        Chart1.Series.Clear()       'dump all series and recreate
        Chart1.Titles.Clear()       'dump title for recreation later

        Chart1.Titles.Add("System Demands: " & Format(ReportStart, "yyyy/MM/dd HH:mm") & " to " & Format(ReportEnd, "yyyy/MM/dd HH:mm"))

        Dim mcec As New Series()
        mcec.ChartArea = "ChartArea1"
        mcec.Name = "MCEC"
        Chart1.Series.Add(mcec)
        Chart1.Series(mcec.Name).Color = Color.Green
        Chart1.Series(mcec.Name).ChartType = SeriesChartType.Spline
        Chart1.Series(mcec.Name).XValueMember = "DateTime"
        Chart1.Series(mcec.Name).XValueType = ChartValueType.DateTime
        Chart1.Series(mcec.Name).YValueMembers = "MCEC"
        Chart1.Series(mcec.Name).YValueType = ChartValueType.Double

        Dim aecc As New Series()
        aecc.ChartArea = "ChartArea1"
        aecc.Name = "AECC"
        Chart1.Series.Add(aecc)
        Chart1.Series(aecc.Name).Color = Color.DarkGray
        Chart1.Series(aecc.Name).ChartType = SeriesChartType.Spline
        Chart1.Series(aecc.Name).XValueMember = "DateTime"
        Chart1.Series(aecc.Name).XValueType = ChartValueType.DateTime
        Chart1.Series(aecc.Name).YValueMembers = "AECC"
        Chart1.Series(aecc.Name).YValueType = ChartValueType.Double

        Dim m As New Series()
        m.ChartArea = "ChartArea1"
        m.Name = "Manila"
        Chart1.Series.Add(m)
        Chart1.Series(m.Name).Color = Color.Blue
        Chart1.Series(m.Name).ChartType = SeriesChartType.Spline
        Chart1.Series(m.Name).XValueMember = "DateTime"
        Chart1.Series(m.Name).XValueType = ChartValueType.DateTime
        Chart1.Series(m.Name).YValueMembers = "Manila"
        Chart1.Series(m.Name).YValueType = ChartValueType.Int32

        Dim g As New Series()
        g.ChartArea = "ChartArea1"
        g.Name = "Gosnell"
        Chart1.Series.Add(g)
        Chart1.Series(g.Name).Color = Color.Gold
        Chart1.Series(g.Name).ChartType = SeriesChartType.Spline
        Chart1.Series(g.Name).XValueMember = "DateTime"
        Chart1.Series(g.Name).XValueType = ChartValueType.DateTime
        Chart1.Series(g.Name).YValueMembers = "Gosnell"
        Chart1.Series(g.Name).YValueType = ChartValueType.Int32

        Dim l As New Series()
        l.ChartArea = "ChartArea1"
        l.Name = "Lepanto"
        Chart1.Series.Add(l)
        Chart1.Series(l.Name).Color = Color.Red
        Chart1.Series(l.Name).ChartType = SeriesChartType.Spline
        Chart1.Series(l.Name).XValueMember = "DateTime"
        Chart1.Series(l.Name).XValueType = ChartValueType.DateTime
        Chart1.Series(l.Name).YValueMembers = "Lepanto"
        Chart1.Series(l.Name).YValueType = ChartValueType.Int32

        Dim r As New Series()
        r.ChartArea = "ChartArea1"
        r.Name = "Reginold"
        Chart1.Series.Add(r)
        Chart1.Series(r.Name).Color = Color.Orange
        Chart1.Series(r.Name).ChartType = SeriesChartType.Spline
        Chart1.Series(r.Name).XValueMember = "DateTime"
        Chart1.Series(r.Name).XValueType = ChartValueType.DateTime
        Chart1.Series(r.Name).YValueMembers = "Reginold"
        Chart1.Series(r.Name).YValueType = ChartValueType.Int32

        Dim p As New Series()
        p.ChartArea = "ChartArea1"
        p.Name = "Promised Land"
        Chart1.Series.Add(p)
        Chart1.Series(p.Name).Color = Color.Purple
        Chart1.Series(p.Name).ChartType = SeriesChartType.Spline
        Chart1.Series(p.Name).XValueMember = "DateTime"
        Chart1.Series(p.Name).XValueType = ChartValueType.DateTime
        Chart1.Series(p.Name).YValueMembers = "Promised_Land"
        Chart1.Series(p.Name).YValueType = ChartValueType.Int32

        Chart1.Size = New System.Drawing.Size(973, 467)
        'Chart1.BackColor = Color.WhiteSmoke
        Chart1.ChartAreas(0).AxisX.LabelStyle.Format = "MM/dd HH:mm"

        Chart1.DataSource = ds.Tables("pt_demand")
        Chart1.DataBind()
        Chart1.Visible = True

    End Using

End Sub
Solution

  • I'm still not completely sure why it wouldn't work the first several times I tried, but I suspect a possible issue between the minor data type differences (first two series are decimal and the remainder are integers).

    The final solution (abridged code is below) was to generate two different charts for the two different types of data, save chart to png's in the user profile directory, bang out an html file to handle final formatting, display, and printing needs, then open the file programmatically.

    The following sample code only includes:

    1. the generation of Chart2, as it is the more complicated of the two charts
    2. the procedure to set the chart's y axis as appropriate, and

    3. the procedure to save the chart to disk.

      Imports System.Windows.Forms.DataVisualization.Charting
Imports System.IO

... ... ...

Dim myDBCS As String = Main.DBCS    'create local copy of database connection string
Dim myAPPFOLDER As String = Main.APPFOLDER  'local copy of scratchpad folder
Dim ReportStart, ReportEnd As DateTime  'reporting dates, persistent
Dim dblMin, dblMax As Double    'minimum and maximum values for y axis settings
Dim sql As String   'holds sql for construction & execute
Dim Chart1 As New Chart 'rate1 chart
Dim Chart2 As New Chart 'subs chart

... ... ...

Public Sub PTRSubs()
'/////////////////////////////////
'Build & execute SQL Query, Parse results, build chart
'/////////////////////////////////

'build sql
Dim cols As String = ""
sql = "SELECT DateTime, Gosnell, Lepanto, Manila, Reginold " _
  & "FROM pt_demand WHERE DateTime BETWEEN '" & ReportStart.ToString & "' AND '" & ReportEnd.ToString _
  & "' ORDER BY DateTime ASC;"

'/////////////////////////////////
'Setup Chart2
'if we're talking more than 1 day's worth of data, change the x axis type to datetime, otherwise, leave as time
Dim x As Boolean
If DateDiff(DateInterval.Day, ReportStart, ReportEnd) > 1 Then
    x = True
Else
    x = False
End If

Chart2.Size = New System.Drawing.Size(1280, 575)
Chart2.Series.Clear()
Chart2.ChartAreas.Clear()

Chart2.ChartAreas.Add("Subs")

'/////////////////////////////////
'define series parameters
Chart2.Series.Add("Gosnell")
Chart2.Series("Gosnell").ChartArea = "Subs"
Chart2.Series("Gosnell").Color = Color.Gray
Chart2.Series("Gosnell").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
Chart2.Series("Gosnell").XValueType = DataVisualization.Charting.ChartValueType.Time
If x = True Then
    Chart2.Series("Gosnell").XValueType = DataVisualization.Charting.ChartValueType.DateTime
Else
    Chart2.Series("Gosnell").XValueType = DataVisualization.Charting.ChartValueType.Time
End If

Chart2.Series.Add("Lepanto")
Chart2.Series("Lepanto").ChartArea = "Subs"
Chart2.Series("Lepanto").Color = Color.Purple
Chart2.Series("Lepanto").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
Chart2.Series("Lepanto").XValueType = DataVisualization.Charting.ChartValueType.Time
If x = True Then
    Chart2.Series("Lepanto").XValueType = DataVisualization.Charting.ChartValueType.DateTime
Else
    Chart2.Series("Lepanto").XValueType = DataVisualization.Charting.ChartValueType.Time
End If

Chart2.Series.Add("Manila")
Chart2.Series("Manila").ChartArea = "Subs"
Chart2.Series("Manila").Color = Color.Red
Chart2.Series("Manila").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
Chart2.Series("Manila").XValueType = DataVisualization.Charting.ChartValueType.Time
If x = True Then
    Chart2.Series("Manila").XValueType = DataVisualization.Charting.ChartValueType.DateTime
Else
    Chart2.Series("Manila").XValueType = DataVisualization.Charting.ChartValueType.Time
End If

Chart2.Series.Add("Reginold")
Chart2.Series("Reginold").ChartArea = "Subs"
Chart2.Series("Reginold").Color = Color.Orange
Chart2.Series("Reginold").ChartType = DataVisualization.Charting.SeriesChartType.FastLine
Chart2.Series("Reginold").XValueType = DataVisualization.Charting.ChartValueType.Time
If x = True Then
    Chart2.Series("Reginold").XValueType = DataVisualization.Charting.ChartValueType.DateTime
Else
    Chart2.Series("Reginold").XValueType = DataVisualization.Charting.ChartValueType.Time
End If

'/////////////////////////////////
'Connect to database, execute query
On Error GoTo Err_Control
Dim ThisRecKey As Guid
Dim strGetDemandsSQL As String = sql
Using sConnection As New SqlConnection(myDBCS)
    sConnection.Open()
    Dim GetRecKeySQLCommand As New SqlCommand(strGetDemandsSQL, sConnection)
    'Get the Rec_Key
    Dim reader As SqlDataReader = GetRecKeySQLCommand.ExecuteReader()
    Dim ThisDateTime As Date

    '/////////////////////////////////
    'build series datapoints
    While reader.Read()
        'Add Points
        Debug.Print(reader(0) & "    " & reader(1) & "    " & reader(2) & "    " & reader(3) & "    " & reader(4))
        ThisDateTime = reader(0)

        Chart2.Series("Gosnell").Points.AddXY(ThisDateTime.ToOADate(), CInt(reader(1)))
        Chart2.Series("Lepanto").Points.AddXY(ThisDateTime.ToOADate(), CInt(reader(2)))
        Chart2.Series("Manila").Points.AddXY(ThisDateTime.ToOADate(), CInt(reader(3)))
        Chart2.Series("Reginold").Points.AddXY(ThisDateTime.ToOADate(), CInt(reader(4)))

    End While
    reader.Close()
    sConnection.Close()
End Using

dblMin = 0
dblMax = 0

SetMinMax(dblMin, dblMax, "Gosnell")
If dblMax = 0 Then          'if axis min/max is not already set, then set both min and max based on these values them based on these values
    Chart2.ChartAreas("Subs").AxisY.Minimum = dblMin
    Chart2.ChartAreas("Subs").AxisY.Maximum = dblMax

Else
    If dblMin < Chart2.ChartAreas("Subs").AxisY.Minimum Then Chart2.ChartAreas("Subs").AxisY.Minimum = dblMin 'only reset if value is lower than current setting
    If dblMax > Chart2.ChartAreas("Subs").AxisY.Maximum Then Chart2.ChartAreas("Subs").AxisY.Maximum = dblMax 'only reset if value is higher than current setting

End If

SetMinMax(dblMin, dblMax, "Lepanto")
If dblMax = 0 Then          'if axis min/max is not already set, then set both min and max based on these values them based on these values
    Chart2.ChartAreas("Subs").AxisY.Minimum = dblMin
    Chart2.ChartAreas("Subs").AxisY.Maximum = dblMax

Else
    If dblMin < Chart2.ChartAreas("Subs").AxisY.Minimum Then Chart2.ChartAreas("Subs").AxisY.Minimum = dblMin 'only reset if value is lower than current setting
    If dblMax > Chart2.ChartAreas("Subs").AxisY.Maximum Then Chart2.ChartAreas("Subs").AxisY.Maximum = dblMax 'only reset if value is higher than current setting

End If

SetMinMax(dblMin, dblMax, "Manila")
If dblMax = 0 Then          'if axis min/max is not already set, then set both min and max based on these values them based on these values
    Chart2.ChartAreas("Subs").AxisY.Minimum = dblMin
    Chart2.ChartAreas("Subs").AxisY.Maximum = dblMax

Else
    If dblMin < Chart2.ChartAreas("Subs").AxisY.Minimum Then Chart2.ChartAreas("Subs").AxisY.Minimum = dblMin 'only reset if value is lower than current setting
    If dblMax > Chart2.ChartAreas("Subs").AxisY.Maximum Then Chart2.ChartAreas("Subs").AxisY.Maximum = dblMax 'only reset if value is higher than current setting

End If

SetMinMax(dblMin, dblMax, "Reginold")
If dblMax = 0 Then          'if axis min/max is not already set, then set both min and max based on these values them based on these values
    Chart2.ChartAreas("Subs").AxisY.Minimum = dblMin
    Chart2.ChartAreas("Subs").AxisY.Maximum = dblMax

Else
    If dblMin < Chart2.ChartAreas("Subs").AxisY.Minimum Then Chart2.ChartAreas("Subs").AxisY.Minimum = dblMin 'only reset if value is lower than current setting
    If dblMax > Chart2.ChartAreas("Subs").AxisY.Maximum Then Chart2.ChartAreas("Subs").AxisY.Maximum = dblMax 'only reset if value is higher than current setting

End If

'/////////////////////////////////
'set axis title
Chart2.ChartAreas("Subs").AxisX.Title = "Report window: " & Format(ReportStart, "yyyy/MM/dd HH:mm") & " to " & Format(ReportEnd, "yyyy/MM/dd HH:mm")

DumpChartToImage(Chart2, "Chart2")

Exit_Here:
Exit Sub

Err_Control:
Select Case Err.Number
    Case Else
        MsgBox("PTRSubs" & vbCrLf _
         & "Error Number: " & Err.Number.ToString & vbCrLf _
         & "Description:  " & Err.Description.ToString & vbCrLf _
         & "Source:  " & Err.Source, MsgBoxStyle.OkOnly, "errcontrol")
        Resume Exit_Here
End Select
End Sub

Private Sub SetMinMax(ByRef Min As Double, ByRef Max As Double, FieldName As String)
'/////////////////////////////////
'Get min and Max values of data from selected set
'/////////////////////////////////

On Error GoTo Err_Control

Dim strGetMaxSQL As String = "Select MAX(" & FieldName & ") FROM pt_demand WHERE DateTime Between '" & ReportStart.ToString & "' AND '" & ReportEnd.ToString & "'"
Dim strGetMinSQL As String = "Select MIN(" & FieldName & ") FROM pt_demand WHERE DateTime Between '" & ReportStart.ToString & "' AND '" & ReportEnd.ToString & "'"
Using sConnection As New SqlConnection(myDBCS)
    sConnection.Open()
    'MAX
    Dim GetMaxSQLCommand As New SqlCommand(strGetMaxSQL, sConnection)
    Dim readerMax As SqlDataReader = GetMaxSQLCommand.ExecuteReader()

    While readerMax.Read()
        Max = CDbl(CInt(readerMax(0) + (0.01 * readerMax(0))))
    End While
    readerMax.Close()
    'MIN
    Dim GetMinSQLCommand As New SqlCommand(strGetMinSQL, sConnection)
    'Get the Rec_Key
    Dim readerMin As SqlDataReader = GetMinSQLCommand.ExecuteReader()

    While readerMin.Read()
        Min = CDbl(CInt(readerMin(0) - (0.01 * readerMin(0))))
    End While
    readerMin.Close()

    sConnection.Close()
End Using

Exit_Here:
Exit Sub

Err_Control:
Select Case Err.Number
    Case Else
        Resume Exit_Here
End Select
End Sub

Private Sub DumpChartToImage(ByRef mychart As Chart, ByRef file As String)
'//////////////////////
'Save chart to file on disk, first wipe old copy if already exists
'//////////////////////
Dim filename As String = myAPPFOLDER & file & ".png"
If System.IO.File.Exists(filename) Then
    'The file exists, delete it first
    System.IO.File.Delete(filename)
    'recreate the file
    mychart.SaveImage(filename, System.Drawing.Imaging.ImageFormat.Png)

Else
    'the file doesn't exist, create it
    mychart.SaveImage(filename, System.Drawing.Imaging.ImageFormat.Png)

End If

End Sub