VB.NET Creating a chart with multiple xseries based on column-value in SQL

darkgen · 27 Nov 2019 at 15:02

I'm at it again!

The idea is to dynamically create multiple x series based on column in sqlsource. Y is the count of tickets and x is the weekno.

A sql view which returns columns on a data-set covering a period of 12 months:

I then have the following code:

Code:

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If Not Me.IsPostBack Then
            'Fetch the data from database.
            Dim query As String = "SELECT TickC, CODE, Year, Week FROM SQLVIEW ORDER BY Year, Week"
            Dim dt As DataTable = GetData(query)

            'Get the DISTINCT subcodes.
            Dim codes As List(Of String) = (From p In dt.AsEnumerable()
                                            Select p.Field(Of String)("CODE")).Distinct().ToList()

            'Loop through the subcodes.
            For Each subcode As String In codes

                'Get the weekno for each subcode.
                Dim x As Integer() = (From p In dt.AsEnumerable()
                                      Where p.Field(Of String)("CODE") = subcode
                                      Order By p.Field(Of Integer)("Week")
                                      Select p.Field(Of Integer)("Week")).ToArray()

                'Get the Total of tickes for each subcode.
                Dim y As Integer() = (From p In dt.AsEnumerable()
                                      Where p.Field(Of String)("CODE") = subcode
                                      Order By p.Field(Of Integer)("Week")
                                      Select p.Field(Of Integer)("TickC")).ToArray()

                'Add Series to the Chart.
                Chart1.Series.Add(New Series(subcode))
                Chart1.Series(subcode).IsValueShownAsLabel = True
                Chart1.Series(subcode).BorderWidth = 2
                Chart1.Series(subcode).ChartType = SeriesChartType.Line
                Chart1.Series(subcode).Points.DataBindXY(x, y)
            Next

            Chart1.Legends(0).Enabled = True
        End If
    End Sub

    Private Shared Function GetData(ByVal query As String) As DataTable
        Dim constr As String = ConfigurationManager.ConnectionStrings("CONNECTION").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                Return dt
            End Using
        End Using
    End Function

However, I get the following ASP error when viewing the page
"Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"

Any pointers would be greatly appreciated.
TIA!

radderfire · 28 Nov 2019 at 00:56

Could be this:

http://www.kodyaz.com/t-sql/order-by-in-sql-view.aspx

darkgen · 28 Nov 2019 at 07:14

I don't think so, the error remains when the order by is removed from the select query. I do wonder at the "less than the size of the collection" part. I'm not defining a size but I wonder if there is a maximum and the data-set is breaching it.
Full error trace:

Code:

[ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index]
   System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource) +64
   System.Collections.Generic.List`1.get_Item(Int32 index) +12737704
   System.Collections.ObjectModel.Collection`1.get_Item(Int32 index) +45
   ctxapp2.IM_Group_RES.Page_Load(Object sender, EventArgs e) in C:\IM_Group_RES.aspx.vb:38
   System.Web.UI.Control.OnLoad(EventArgs e) +95
   System.Web.UI.Control.LoadRecursive() +59
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +678

eh fixed it.
Needed additional variables set in chart:

Code:

  <asp:Chart ID="Chart1" runat="server">
          <series>
              <asp:Series Name="Series1">
              </asp:Series>
          </series>
          <chartareas>
              <asp:ChartArea Name="ChartArea1">
              </asp:ChartArea>
          </chartareas>
      </asp:Chart>
>
<asp:Chart ID="Chart1" runat="server" Height="300px" Width="400px">
    <Titles>
        <asp:Title ShadowOffset="3" Name="Items" />
    </Titles>
    <Legends>
        <asp:Legend Alignment="Center" Docking="Bottom" IsTextAutoFit="False" Name="Default"
            LegendStyle="Row" />
    </Legends>
    <ChartAreas>
        <asp:ChartArea Name="ChartArea1" BorderWidth="0" />
    </ChartAreas>
</asp:Chart>

heh, 2 steps forward and 1 back. Chart now renders but there appears to be rogue x series created with seemingly random counts. I cannot see anything in the data which would generate the data.

darkgen · 28 Nov 2019 at 10:06

Hmm; so added a check to skip for loop if subcode value is null but continues to render empty space / values in the chart.

Code:

 'Loop through the subcodes.
            For Each subcode As String In codes
                If Not subcode Is DBNull.Value Then
                    'Get the weekno for each subcode.
                    Dim x As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("Week")).ToArray()

                    'Get the Total of tickes for each subcode.
                    Dim y As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("TickC")).ToArray()

                    'Add Series to the Chart.
                    Chart1.Series.Add(New Series(subcode))
                    Chart1.Series(subcode).IsValueShownAsLabel = False
                    Chart1.Series(subcode).BorderWidth = 2
                    Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn
                    Chart1.Series(subcode).Points.DataBindXY(x, y)
                    Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)"
                End If
            Next

Hmm.

darkgen · 28 Nov 2019 at 10:46

darkgen said:

Hmm; so added a check to skip for loop if subcode value is null but continues to render empty space / values in the chart.

Code:

 'Loop through the subcodes.
            For Each subcode As String In codes
                If Not subcode Is DBNull.Value Then
                    'Get the weekno for each subcode.
                    Dim x As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("Week")).ToArray()

                    'Get the Total of tickes for each subcode.
                    Dim y As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("TickC")).ToArray()

                    'Add Series to the Chart.
                    Chart1.Series.Add(New Series(subcode))
                    Chart1.Series(subcode).IsValueShownAsLabel = False
                    Chart1.Series(subcode).BorderWidth = 2
                    Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn
                    Chart1.Series(subcode).Points.DataBindXY(x, y)
                    Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)"
                End If
            Next

Hmm.

E: looks like Chart1.DataManipulator.InsertEmptyPoints is my friend but it does not appear to do anything. I'm placing the following inside the For each loop.
Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, subcode)

touch · 28 Nov 2019 at 12:56

I think your "out of range" error will be because your arrays of values (x and y) can be different sizes from your series (subcode) unless every different codes occurs every single week.

edit: so rather than checking if the code is null, you need to do it the other way around and check if there are zero values for that code and week then dont add them to the values list if they are.

darkgen · 28 Nov 2019 at 13:52

touch said:
I think your "out of range" error will be because your arrays of values (x and y) can be different sizes from your series (subcode) unless every different codes occurs every single week.

edit: so rather than checking if the code is null, you need to do it the other way around and check if there are zero values for that code and week then dont add them to the values list if they are.

I think the code to create dynamic series from dataset is good. The issue I'm now seeing is around gaps/overlap as outlined in this thread. I'm just struggling with the specific code and where it should sit.

e: on your second line, i think that's what "Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, subcode)" or similar should do, basically set that specific subcode to 0. hmm; I don't know.

darkgen · 9 Dec 2019 at 12:31

With a little help from another forum here is the code which creates a sets data-points as zero within each series.

Code:

For Each subcode As String In codes

                'Get the weekno for each subcode.
                Dim x1 As Integer() = (From p In dt.AsEnumerable()
                                       Where p.Field(Of String)("CODE") = subcode
                                       Order By p.Field(Of Integer)("Week")
                                       Select p.Field(Of Integer)("Week")).ToArray()

                Dim x As Integer() = (From p In dt.AsEnumerable() Select p.Field(Of Integer)("Week") Distinct).ToArray()

                Dim y As Integer() = (From p In dt.AsEnumerable()
                                      Where p.Field(Of String)("CODE") = subcode
                                      Order By p.Field(Of Integer)("Week")
                                      Select p.Field(Of Integer)("TickC")).ToArray()

                Dim a As Integer = 1
                For Each xi As Integer In x
                    If Not x1.Contains(xi) Then
                        Array.Resize(y, y.Length + 1)
                        For i = y.Length - 1 To a Step -1
                            y(i) = y(i - 1)
                        Next
                        y(a - 1) = 0
                        a += 1
                    Else
                        a += 1
                    End If
                Next
                'Add Series to the Chart.
                Chart4.Series.Add(New Series(subcode))
                Chart4.Series(subcode).IsValueShownAsLabel = False
                Chart4.Series(subcode).BorderWidth = 2
                Chart4.Series(subcode).ChartType = SeriesChartType.StackedColumn100
                Chart4.Series(subcode).Points.DataBindXY(x, y)
                Chart4.Series(subcode).ToolTip = "#SERIESNAME(#VALY)"
            Next