Results 1 to 7 of 7

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

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    Resolved [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,

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: VB.Net Multiple DataSources for Single Line Type Chart

    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

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    Re: VB.Net Multiple DataSources for Single Line Type Chart

    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.
    Last edited by tomahawk; Sep 30th, 2015 at 12:55 AM.

  4. #4
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: VB.Net Multiple DataSources for Single Line Type Chart

    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.

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    Re: VB.Net Multiple DataSources for Single Line Type Chart

    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?
    Last edited by tomahawk; Sep 30th, 2015 at 05:20 AM.

  6. #6
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: VB.Net Multiple DataSources for Single Line Type Chart

    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.

  7. #7

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    Re: VB.Net Multiple DataSources for Single Line Type Chart

    Hi Shaman,

    It works perfectly.

    Danke!!!!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width