[RESOLVED] VB.Net Multiple DataSources for Single Line Type Chart

I am having a single chart control on a form that works fine during runtime. However, I need to use the same chart control to show data from different datasources (rather different SQL statements). The number of datasources can range upto 25 or even more. With each datasource, the title of the graph needs to change. The X-Axis and Y-Axis values are of the same type.

How can I use a timer to display graph for one datasource for a specified period of time and then it changes to another datasource?

Any tips would be appreciated.

Regards,

you can create Chart objects via code. this is some snippet from existing code i have that queries the data, sets up the Chart and exports it as png:
(hope you dont feel offended by the german comments...)

Code:

---

  Private Sub WriteChart(ByVal CenterCode As String, ByVal DataType As String, ByVal Title As String, ByVal FileName As String)
        'Charting
        Dim oChart As New System.Windows.Forms.DataVisualization.Charting.Chart
        oChart.ChartAreas.Add("dummy")
        'Die maximale Balkenhöhe/Punktwert ist auf 80 Minuten gekappt!
        Dim oChartCmd As New OleDb.OleDbCommand("SELECT ChartTime" &
            ",IIF(ISNULL(T1.avgMinutes),0,IIF(T1.avgMinutes>80,80,T1.avgMinutes)) AS avgMin" &
            ",IIF(ISNULL(minMinutes),0,IIF(minMinutes>80,80,minMinutes)) AS minMin" &
            ",IIF(ISNULL(maxMinutes),0,IIF(maxMinutes>80,80,maxMinutes)) AS maxMin" &
            ",IIF(ISNULL(CountRows),0,CountRows) AS ct " &
            ",IIF(ISNULL(T2.avgMinutes),0,IIF(T2.avgMinutes>80,80,T2.avgMinutes)) AS avgMinDay " &
            ",IIF(ISNULL(T1.avgMinutes),0,IIF(T1.avgMinutes>80,80,T1.avgMinutes)) AS avgMinLabel " &
            "FROM (tblChartTimes " &
            "LEFT JOIN (SELECT Format(dtRetrieved,'HH:MM') as dt,CountRows,avgMinutes,minMinutes,maxMinutes FROM tblCookedData WHERE CenterCode=@CenterCode" &
            "        AND idDataType=(SELECT TOP 1 id FROM tblDataTypes WHERE Code=@DataType1)) " &
            "        T1 ON tblChartTimes.ChartTime=T1.dt) " &
            "LEFT JOIN (SELECT Format(dtRetrieved,'HH:MM') as dt,avgMinutes FROM tblCookedData WHERE CenterCode=@CenterCode " &
            "        AND idDataType=(SELECT TOP 1 id FROM tblDataTypes WHERE Code=@DataType2)) " &
            "        T2 ON tblChartTimes.ChartTime=T2.dt " &
            "ORDER BY ChartTime", cnLDI)
        oChartCmd.Parameters.AddWithValue("@DataType1", "DLZ15m_" & DataType)
        oChartCmd.Parameters.AddWithValue("@CenterCode", CenterCode)
        oChartCmd.Parameters.AddWithValue("@DataType2", "DLZ1d_" & DataType)
        Dim dr As OleDb.OleDbDataReader = oChartCmd.ExecuteReader
        oChart.Series.Clear()
        oChart.DataBindTable(dr, "ChartTime")

        With oChart
            .Width = 1200
            .Height = 500
            .ChartAreas(0).AxisX.Interval = 1
            .ChartAreas(0).AxisX.MajorGrid.Enabled = False
            .Legends.Add("Legende")
            .Legends(0).Docking = DataVisualization.Charting.Docking.Bottom
            .Titles.Add(Title)
            .Titles(0).Font = New Font("Arial", 15)
        End With

        With oChart.Series(0)  'der Balken der Intervall DLZ ohne Labels
            .Name = "Ø DLZ"
        End With

        With oChart.Series(1)  'der grüne Punkt für den Intervall Minimum DLZ Wert
            .Name = "min DLZ"
            .ChartType = DataVisualization.Charting.SeriesChartType.Point
            .Color = Color.DarkGreen
        End With

        With oChart.Series(2)  'der rote Punkt für den Intervall Maximum DLZ Wert
            .Name = "max DLZ"
            .ChartType = DataVisualization.Charting.SeriesChartType.Point
            .Color = Color.Red
        End With

        With oChart.Series(3)  'die blaue Linie mit Labels für die Anzahl der Spender im Intervall
            .Name = "# Spender"
            .ChartType = DataVisualization.Charting.SeriesChartType.Line
            .Color = Color.Blue
            .IsValueShownAsLabel = True
            .LabelForeColor = Color.Blue
        End With

        With oChart.Series(4)  'die braune Linie für die Tages DLZ ohne Labels
            .Name = "Tages DLZ"
            .ChartType = DataVisualization.Charting.SeriesChartType.Line
            .Color = Color.Chocolate
            .BorderWidth = 2
        End With

        With oChart.Series(5)  'nur die Labels für die Intervall DLZ ganz am Schluss damit die nicht verdeckt werden
            .Name = ""
            .ChartType = DataVisualization.Charting.SeriesChartType.Point
            .Color = Color.Transparent
            .IsValueShownAsLabel = True
            .LabelFormat = "0.0"
            .SmartLabelStyle.Enabled = False
            .LabelAngle = -90
        End With

        oChart.SaveImage(FileName, System.Drawing.Imaging.ImageFormat.Png)
        dr.Close()

    End Sub

---

Hi Shaman,

Thanks for the code snippet. I already have sub routines to create different graphs. I am trying to figure out how do i cycle through them. If i display the first graph, i want the display to wait for a set time interval and then display another graph (based on a different datasource). The again wait for a set time interval and display the 3rd graph. Its slightly different than showing images so I cannot use that logic. There are about 25 or so graphs. I tried to put a Thread.Sleep(15000) at the end of each sub routine but it does not help.

you would use a timer for that. set it to whatever interval you want one graph to be displayed. then in the tick Event call your function to create the Chart object. design the function so that it Returns the Chart object. then back in the tick Event take what the function Returns, remove any previous Chart object that is in the forms control collection with Name "myChart" (check if there is one first) then add what the function returned to your forms control collection with the Name "myChart". thats all in the tick Event. the new Chart should then be visible on the form until the timer elapsed again and the tick Event Switches to the next Chart.

you could also use the same Chart object over and over again and just Change the datasource.

Below is the sub to create and display graphs for one set of information

Code:

---

   
Private Sub ShowGraph_1()
        Dim MysqlConn = New MySqlConnection
        MysqlConn.ConnectionString = "server=localhost;" _
            & "user id=user;" _
            & "password=pwd;" _
            & "database=databasename"
        Dim rdrTemp As MySqlDataReader
        Try
            MysqlConn.Open()
            Dim Query As String
            Query = "SELECT CONCAT (tblname.ColumnB,' ',tblname.ColumnC) as DateAndTime, tblname.ColumnK FROM tblname " _
                & "WHERE tblname.MetNo = 'Met1' ORDER BY (tblname.ColumnB + tblname.ColumnC) DESC LIMIT 24"
            Dim Command = New MySqlCommand(Query, MysqlConn)
            rdrTemp = Command.ExecuteReader
            While rdrTemp.Read
                graMeter_BarGraph.Series("Series1").Points.AddXY(rdrTemp.GetString("DateAndTime"), rdrTemp.GetInt32("ColumnK"))
            End While
            Dim strTitle As Title = graMeter_BarGraph.Titles.Add("Location Name - Met No. 1")
            With strTitle
                .ForeColor = Color.Black
                .BackColor = Color.Honeydew
                .Font = New System.Drawing.Font("Verdana", 11.0F, FontStyle.Bold)
            End With
            graMeter_BarGraph.Series("Series1").Font = New System.Drawing.Font("Tahoma", 9.0F, FontStyle.Regular)
            graMeter_BarGraph.Series("Series1").IsValueShownAsLabel = True
            graMeter_BarGraph.Series("Series1").LabelForeColor = Color.Red
            graMeter_BarGraph.Series("Series1").BorderWidth = 2
            MysqlConn.Close()
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Finally
            MysqlConn.Dispose()
        End Try
    End Sub

---

Likewise I have multiple graphs to display with corresponding Title Name etc. where the tblname.MetNo value will change for each graph and can be passed as an argument. However, if I return a graph from a function, how will I change the datasource for the next graph alongwith the Title Name etc.

How to change the function argument in the timer event?

so you got 25 functions ShowGraph_1 to ShowGraph_25 like the one you posted, and you got a Chart control graMeter_BarGraph on your form which you want to use for displaying the data. each function ShowGraph_1 to ShowGraph_25 works nicely for itself but you got a Problem when calling ShowGraph_1 first and then e.g. ShowGraph_2

so far correct?

to make it work that you call ShowGraph_1 then ShowGraph_2 i think you Need to clear at least the Chart.series before you set the new data to the Chart. go through the Chart and Series Methods, there should be some 'clear' methods. when this works, then i'd do something like that (pseudo code) in the timer tick event

Code:

---

public sub Timer_Tick () Handles Timer_Tick
        Static iDiagram as int32=0
       
        iDiagram += 1
        select case iDiagram
        case 1
                ShowGraph_1
        case 2
                ShowGraph_2
        case 3
        ....
       
        case 25
                ShowGraph_25
                iDiagram=0
        end select
end sub

---

if you are asking how you can make your ShowGraph_1 function to work for different tblname.MetNo Values then the solution would look something like that:

Code:

---

Private Sub ShowGraph(MetNo as string,ChartTitle as string)
        Dim MysqlConn = New MySqlConnection
        MysqlConn.ConnectionString = "server=localhost;" _
            & "user id=user;" _
            & "password=pwd;" _
            & "database=databasename"
        Dim rdrTemp As MySqlDataReader
        Try
            MysqlConn.Open()
            Dim Query As String
            Query = "SELECT CONCAT (tblname.ColumnB,' ',tblname.ColumnC) as DateAndTime, tblname.ColumnK FROM tblname " _
                & "WHERE tblname.MetNo = @MetNo ORDER BY (tblname.ColumnB + tblname.ColumnC) DESC LIMIT 24"        '<-- !
            Dim Command = New MySqlCommand(Query, MysqlConn)
            Command.Parameters.AddwithValue("@MetNo",MetNo) '<-- !
            rdrTemp = Command.ExecuteReader
            graMeter_BarGraph.Series.Clear '<-- ! or whatever the correct command is
            While rdrTemp.Read
                graMeter_BarGraph.Series("Series1").Points.AddXY(rdrTemp.GetString("DateAndTime"), rdrTemp.GetInt32("ColumnK"))
            End While
            Dim strTitle As Title = graMeter_BarGraph.Titles.Add(ChartTitle)'<-- !
            With strTitle
                .ForeColor = Color.Black
                .BackColor = Color.Honeydew
                .Font = New System.Drawing.Font("Verdana", 11.0F, FontStyle.Bold)
            End With
            graMeter_BarGraph.Series("Series1").Font = New System.Drawing.Font("Tahoma", 9.0F, FontStyle.Regular)
            graMeter_BarGraph.Series("Series1").IsValueShownAsLabel = True
            graMeter_BarGraph.Series("Series1").LabelForeColor = Color.Red
            graMeter_BarGraph.Series("Series1").BorderWidth = 2
            MysqlConn.Close()
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Finally
            MysqlConn.Dispose()
        End Try
    End Sub

---

and your tick event:

Code:

---

public sub Timer_Tick () Handles Timer_Tick
        Static iDiagram as int32=0
       
        iDiagram += 1
        select case iDiagram
        case 1
                ShowGraph("Met1","Location Name - Met No. 1")
        case 2
                ShowGraph("Met2","Location Name - Met No. 2")
        case 3
        ....
       
        case 25
                ShowGraph("Met25","Location Name - Met No. 25")
                iDiagram=0
        end select
end sub

---

although i somehow have the gut feeling i did not really understand your requirements, i still hope that helps.

Hi Shaman,

It works perfectly.

Danke!!!!! :)