Answered by:
Dynamically created series using StackedColumn - Overlap / gaps between series.

Question
-
User705223197 posted
I've read various threads around this issue which is caused when there are no data-points between series but I can't quite work out how to plug it in when dynamically creating the series for a StackedColumn chart. I'm almost there ...
Code behind:
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 DISTINCT TickC, CODE, Week FROM someview order by code desc, 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 ' 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 tickets 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 = 0 Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn Chart1.Series(subcode).Points.DataBindXY(x, y) Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)" 'Chart1.AlignDataPointsByAxisLabel() 'Chart1.DataManipulator.Sort(PointSortOrder.Ascending, "X", subcode); 'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, subcode); Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 1, IntervalType.Number, subcode) ' End If Next 'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, Series As codes) Chart1.Legends(0).Enabled = True End If
As you can see I've clumsily tried various things with DataManupulator but with zero luck. I'm struggling how I plug this in, if indeed this is the right method, in to the code.
Any pointers, greatly appreciated.
TIA, Paul.
Friday, November 29, 2019 7:04 AM
Answers
-
User-719153870 posted
Hi darkgen,
darkgen
So nothing to do with maximums; the error occurs with the following dataset.Yes and sorry you met the problem because i took a shortcut in the code i provided.
In the early sample datatable you provided, the Week value was 1,2 and 3 thus i took it as the counter which you can see it as xi in the code.
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 xi Step -1 y(i) = y(i - 1) Next y(xi - 1) = 0 End If Next
In the newest datatable, the week could be 45,46 and so on, so the counter xi would break.
To solve this problem, all you need to do is to add a fresh counter in the code bove. Please refer to below demo:
aspx:
<%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Chart ID="Chart1" runat="server" Height="594px" Width="1000px" BackColor="Silver" BorderlineColor="LightGray"> <Titles> <asp:Title Name="Items" Text="Created IM by subcode" /> </Titles> <Legends> <asp:Legend Alignment="Center" Docking="Right" IsTextAutoFit="True" Name="Default" LegendStyle="column" /> </Legends> <ChartAreas> <asp:ChartArea Name="ChartArea1" BorderWidth="1" /> </ChartAreas> </asp:Chart> </div> </form> </body> </html>
cs:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then 'Fetch the data from database. 'Dim query As String = "SELECT distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, week" Dim dt As DataTable = New DataTable() dt.Columns.AddRange(New DataColumn(2) {New DataColumn("CODE"), New DataColumn("Week", Type.GetType("System.Int32")), New DataColumn("TickC", Type.GetType("System.Int32"))}) dt.Rows.Add("ACCESS ISSUE", 44, 2) dt.Rows.Add("APPLICATION ISSUE", 44, 6) dt.Rows.Add("PERFORMANCE", 44, 2) dt.Rows.Add("T4-PERFORMANCE", 44, 1) dt.Rows.Add("ACCESS ISSUE", 45, 9) dt.Rows.Add("APPLICATION ERROR", 45, 1) dt.Rows.Add("APPLICATION ISSUE", 45, 27) dt.Rows.Add("CONNECTIVITY", 45, 8) dt.Rows.Add("PERFORMANCE", 45, 9) dt.Rows.Add("T4 - ACCESS ISSUE", 45, 5) dt.Rows.Add("T4 - CONNECTIVITY", 45, 4) dt.Rows.Add("T4 - PERFORMANCE", 45, 1) dt.Rows.Add("ACCESS ISSUE", 46, 13) dt.Rows.Add("APPLICATION ISSUE", 46, 23) dt.Rows.Add("CONFIGURATION", 46, 1) dt.Rows.Add("CONNECTIVITY", 46, 1) dt.Rows.Add("CONSULTANCY", 46, 1) dt.Rows.Add("PERFORMANCE", 46, 6) dt.Rows.Add("T4 - ACCESS ISSUE", 46, 6) dt.Rows.Add("T4 - CONFIGURATION", 46, 2) dt.Rows.Add("T4 - CONNECTIVITY", 46, 4) dt.Rows.Add("T4 - PERFORMANCE", 46, 2) dt.Rows.Add("ACCESS ISSUE", 47, 12) dt.Rows.Add("APPLICATION ISSUE", 47, 25) dt.Rows.Add("CONFIGURATION", 47, 5) dt.Rows.Add("CONNECTIVITY", 47, 4) dt.Rows.Add("CONSULTANCY", 47, 5) dt.Rows.Add("PERFORMANCE", 47, 6) dt.Rows.Add("T4 - ACCESS ISSUE", 47, 2) dt.Rows.Add("T4 - CONFIGURATION", 47, 3) dt.Rows.Add("T4 - CONNECTIVITY", 47, 5) dt.Rows.Add("ACCESS ISSUE", 48, 15) dt.Rows.Add("APPLICATION ISSUE", 48, 20) dt.Rows.Add("LICENSE", 48, 1) dt.Rows.Add("PERFORMANCE", 48, 3) dt.Rows.Add("PROFILE", 48, 1) dt.Rows.Add("T4 - ACCESS ISSUE", 48, 1) dt.Rows.Add("T4 - CONFIGURATION", 48, 1) dt.Rows.Add("T4 - CONNECTIVITY", 48, 2) dt.Rows.Add("T4 - PERFORMANCE", 48, 1) dt.Rows.Add("ACCESS ISSUE", 49, 1) dt.Rows.Add("APPLICATION ISSUE", 49, 7) dt.Rows.Add("PERFORMANCE", 49, 1) dt.Rows.Add("T4 - ACCESS ISSUE", 49, 1) dt.Rows.Add("T4 - CONFIGURATION", 49, 1) dt.Rows.Add("T4 - PERFORMANCE", 49, 1) '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 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 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)" 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
Below is the result of above demo:
Hope this could help.
Best Regard,
Yang Shen
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, December 4, 2019 5:34 AM
All replies
-
User-719153870 posted
Hi darkgen,
I built a demo and reproduced your problem then got a chart like below:
As you said, this problem 'is caused when there are no data-points between series'. When there's a field value was there in other columns but not in the current one, the chart will keep a white space for this field value.
To solve this problem, i suggest you should update your query string to make sure there's a 0 value for those fileds who has null as its value in your database.
Since we don't have the complete code of your program, i recommend you modify your query string like below:
Dim query As String = "SELECT distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, week"
You can refer to below demo for more information:
aspx:
<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Chart ID="Chart1" runat="server"> <Series> <asp:Series Name="Series1"></asp:Series> </Series> <ChartAreas> <asp:ChartArea Name="ChartArea1"></asp:ChartArea> </ChartAreas> </asp:Chart> </div> </form> </body> </html>
vb:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then 'Fetch the data from database. 'Dim query As String = "SELECT DISTINCT TickC, CODE, Week FROM someview order by code desc, week" Dim dt As DataTable = New DataTable() dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Name"), New DataColumn("ID", Type.GetType("System.Int32")), New DataColumn("Qty", Type.GetType("System.Int32"))}) dt.Rows.Add("AA", 1, 3) dt.Rows.Add("BB", 1, 0) dt.Rows.Add("CC", 1, 1) dt.Rows.Add("DD", 1, 0) dt.Rows.Add("EE", 1, 24) dt.Rows.Add("FF", 1, 6) dt.Rows.Add("GG", 1, 44) dt.Rows.Add("AA", 2, 4) dt.Rows.Add("BB", 2, 43) dt.Rows.Add("CC", 2, 0) dt.Rows.Add("DD", 2, 54) dt.Rows.Add("EE", 2, 0) dt.Rows.Add("FF", 2, 14) dt.Rows.Add("GG", 2, 22) dt.Rows.Add("AA", 3, 32) dt.Rows.Add("BB", 3, 5) dt.Rows.Add("CC", 3, 9) dt.Rows.Add("DD", 3, 0) dt.Rows.Add("EE", 3, 34) dt.Rows.Add("FF", 3, 0) dt.Rows.Add("GG", 3, 22) 'Get the DISTINCT subcodes. Dim codes As List(Of String) = (From p In dt.AsEnumerable() Select p.Field(Of String)("Name")).Distinct().ToList() '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)("Name") = subcode Order By p.Field(Of Integer)("ID") Select p.Field(Of Integer)("ID")).ToArray() 'Get the Total of tickets for each subcode. Dim y As Integer() = (From p In dt.AsEnumerable() Where p.Field(Of String)("Name") = subcode Order By p.Field(Of Integer)("ID") Select p.Field(Of Integer)("Qty")).ToArray() 'Add Series to the Chart. Chart1.Series.Add(New Series(subcode)) Chart1.Series(subcode).IsValueShownAsLabel = False Chart1.Series(subcode).BorderWidth = 0 Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn Chart1.Series(subcode).Points.DataBindXY(x, y) Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)" 'Chart1.AlignDataPointsByAxisLabel() 'Chart1.DataManipulator.Sort(PointSortOrder.Ascending, "X", subcode); 'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, subcode); Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 1, IntervalType.Number, subcode) ' End If Next 'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, Series As codes) 'Chart1.Legends(0).Enabled = True End If End Sub
Below is the result of this demo:
Best Regard,
Yang Shen
Monday, December 2, 2019 6:05 AM -
User705223197 posted
Many thanks for your reply but adding a isnull to the query doesn't seem to make a difference.
For completeness:
Here is the chart section:
<asp:Chart ID="Chart1" runat="server" Height="400px" Width="1000px" BackColor="Silver" BorderlineColor="LightGray"> <Titles> <asp:Title Name="Items" Text="Created IM by subcode" /> </Titles> <Legends> <asp:Legend Alignment="Center" Docking="Right" IsTextAutoFit="True" Name="Default" LegendStyle="column" /> </Legends> <ChartAreas> <asp:ChartArea Name="ChartArea1" BorderWidth="1" /> </ChartAreas> </asp:Chart>
Here is the full code behind:
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 distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, 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 = 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)" 'Chart1.AlignDataPointsByAxisLabel() 'Chart1.AlignDataPointsByAxisLabel() 'Chart1.DataManipulator.Sort(PointSortOrder.Ascending, "X", subcode) 'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, subcode) 'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, subcode) Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 1, IntervalType.Number, subcode) Next 'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, Series As codes) Chart1.Legends(0).Enabled = True 'For Each s As Series In Chart1.Series 'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, s) 'Next 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
This is an example of the query data:
Count Code Week
1 ACCESS ISSUE 1 6 APPLICATION ISSUE 1 3 ACCESS ISSUE 2 6 APPLICATION ISSUE 2 1 CONFIGURATION 2 1 T123 - CONNECTIVITY 2 1 T123 - PERFORMANCE 2 2 ACCESS ISSUE 3 6 APPLICATION ISSUE 3 1 CONFIGURATION 3 1 CONNECTIVITY 3 1 PERFORMANCE 3 1 T123 - ACCESS ISSUE 3 2 T123 - CONNECTIVITY 3As you can see; it's not just a simple case of isnulling to 0 codes that have zero. In the sql table; there isn't an entry at all for codes with no ticket counts.
Monday, December 2, 2019 8:31 AM -
User-719153870 posted
Hi darkgen,
darkgen
it's not just a simple case of isnulling to 0 codes that have zero. In the sql table; there isn't an entry at all for codes with no ticket counts.Yes you are right and actually i'm right too, let me explain.
First of all, the reason that caused the weird performance of your chart. It is not entirely caused by your code but also the asp.net Chart control. Press F12 and you will see all those data are provided but some of them just disappear in the chart, like below:
This is wierd and sorry that i'm not sure the reason why.
However, the most important thing is how you can fix it. As i mentioned before, the solution is to set the null value to 0 and if you don't even have the CODE for specific Week, create it dynamically and set it to 0. You can refert to below code (it's not database related but with the sample datatable you provided so i assume you should get the point):
aspx:
<%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Chart ID="Chart1" runat="server" Height="594px" Width="1000px" BackColor="Silver" BorderlineColor="LightGray"> <Titles> <asp:Title Name="Items" Text="Created IM by subcode" /> </Titles> <Legends> <asp:Legend Alignment="Center" Docking="Right" IsTextAutoFit="True" Name="Default" LegendStyle="column" /> </Legends> <ChartAreas> <asp:ChartArea Name="ChartArea1" BorderWidth="1" /> </ChartAreas> </asp:Chart> </div> </form> </body> </html>
vb:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then 'Fetch the data from database. 'Dim query As String = "SELECT distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, week" Dim dt As DataTable = New DataTable() dt.Columns.AddRange(New DataColumn(2) {New DataColumn("CODE"), New DataColumn("Week", Type.GetType("System.Int32")), New DataColumn("TickC", Type.GetType("System.Int32"))}) dt.Rows.Add("ACCESS ISSUE", 1, 1) dt.Rows.Add("APPLICATION ISSUE", 1, 6) '---------------------- 'dt.Rows.Add("CONFIGURATION", 1, 0) 'dt.Rows.Add("CONNECTIVITY", 1, 0) 'dt.Rows.Add("PERFORMANCE", 1, 0) 'dt.Rows.Add("T123 - ACCESS ISSUE", 1, 0) 'dt.Rows.Add("T123 - CONNECTIVITY", 1, 0) 'dt.Rows.Add("T123 - PERFORMANCE", 1, 0) dt.Rows.Add("ACCESS ISSUE", 2, 3) dt.Rows.Add("APPLICATION ISSUE", 2, 6) dt.Rows.Add("CONFIGURATION", 2, 1) '-- dt.Rows.Add("T123 - CONNECTIVITY", 2, 1) '-- dt.Rows.Add("T123 - PERFORMANCE", 2, 1) '---------------------- 'dt.Rows.Add("CONNECTIVITY", 2, 0) 'dt.Rows.Add("PERFORMANCE", 2, 0) 'dt.Rows.Add("T123 - ACCESS ISSUE", 2, 0) dt.Rows.Add("ACCESS ISSUE", 3, 2) dt.Rows.Add("APPLICATION ISSUE", 3, 6) dt.Rows.Add("CONFIGURATION", 3, 1) dt.Rows.Add("CONNECTIVITY", 3, 1) '-- dt.Rows.Add("PERFORMANCE", 3, 1) '-- dt.Rows.Add("T123 - ACCESS ISSUE", 3, 1) dt.Rows.Add("T123 - CONNECTIVITY", 3, 2) '---------------------- 'dt.Rows.Add("T123 - PERFORMANCE", 3, 0) '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 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() 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 xi Step -1 y(i) = y(i - 1) Next y(xi - 1) = 0 End If Next 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)" 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
And below is the result:
Hope this could help.
Best Regard,
Yang Shen
Tuesday, December 3, 2019 8:28 AM -
User705223197 posted
I've think I've got it with that additional code enumeration. Sample code works as expected so I'm almost there.
Dim x As Integer() = (From p In dt.AsEnumerable() Select p.Field(Of Integer)("Week") Distinct).ToArray() 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 xi Step -1 y(i) = y(i - 1) Next y(xi - 1) = 0 End If Next
[IndexOutOfRangeException: Index was outside the bounds of the array.] ctxapp2.IM_Group_RES.Page_Load(Object sender, EventArgs e) in path2code.aspx.vb:12 System.Web.UI.Control.OnLoad(EventArgs e) +95 System.Web.UI.Control.LoadRecursive() +59 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +678
Unfortunately when I pull data from the sql query it throws the above error! I'll continue to play and hopefully get there! Really appreciate your support and patience with this.
e; interestingly, if i group the data by month; it renders without an error.
e2: originally I had grouped the data by party before then subgrouping between week or month. Using that grouped data, it returns 150 records from the sql table which renders without issue. If I remove the grouping by party but keep it grouped by month, that returns 255 records and the "Index was outside the bounds of the array" occurs. Are there maximums at play here when trying to do this dynamically?
Tuesday, December 3, 2019 9:04 AM -
User705223197 posted
Many thanks; I finally understand and I'm almost there.
'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() '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() 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 xi Step -1 y(i) = y(i - 1) Next y(xi - 1) = 0 End If Next
I get the additional code which enumerates where there is no series, creates one and sets it to 0. However; I'm now up against another behavior which I think is based on maximums. With the query as is, which is based a grouping by party. It is currently set to group by week, which returns 395 rows of data. This throws the following error:
[IndexOutOfRangeException: Index was outside the bounds of the array.] ctxapp2.IM_Group_RES.Page_Load(Object sender, EventArgs e) in path2\IM_Group_RES.aspx.vb:12 System.Web.UI.Control.OnLoad(EventArgs e) +95 System.Web.UI.Control.LoadRecursive() +59 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +678
If I change the grouping to month; this then returns 150 rows of data and the chart renders correctly. Whether based on week or month; there are no null or duplicated rows so I'm at a loss. Could it be due to maximums; is this method limited to x number of enumerations?
Really appreciate your patience and support with this!
Many thanks!
E: So nothing to do with maximums; the error occurs with the following dataset.
TickC Code Week
2 ACCESS ISSUE 44 6 APPLICATION ISSUE 44 2 PERFORMANCE 44 1 T4 - PERFORMANCE 44 9 ACCESS ISSUE 45 1 APPLICATION ERROR 45 27 APPLICATION ISSUE 45 8 CONNECTIVITY 45 9 PERFORMANCE 45 5 T4 - ACCESS ISSUE 45 4 T4 - CONNECTIVITY 45 1 T4 - PERFORMANCE 45 13 ACCESS ISSUE 46 23 APPLICATION ISSUE 46 1 CONFIGURATION 46 1 CONNECTIVITY 46 1 CONSULTANCY 46 6 PERFORMANCE 46 6 T4 - ACCESS ISSUE 46 2 T4 - CONFIGURATION 46 4 T4 - CONNECTIVITY 46 2 T4 - PERFORMANCE 46 12 ACCESS ISSUE 47 25 APPLICATION ISSUE 47 5 CONFIGURATION 47 4 CONNECTIVITY 47 5 CONSULTANCY 47 6 PERFORMANCE 47 2 T4 - ACCESS ISSUE 47 3 T4 - CONFIGURATION 47 5 T4 - CONNECTIVITY 47 15 ACCESS ISSUE 48 20 APPLICATION ISSUE 48 1 LICENSE 48 3 PERFORMANCE 48 1 PROFILE 48 1 T4 - ACCESS ISSUE 48 1 T4 - CONFIGURATION 48 2 T4 - CONNECTIVITY 48 1 T4 - PERFORMANCE 48 1 ACCESS ISSUE 49 7 APPLICATION ISSUE 49 1 PERFORMANCE 49 1 T4 - ACCESS ISSUE 49 1 T4 - CONFIGURATION 49 1 T4 - PERFORMANCE 49Tuesday, December 3, 2019 10:26 AM -
User-719153870 posted
Hi darkgen,
darkgen
So nothing to do with maximums; the error occurs with the following dataset.Yes and sorry you met the problem because i took a shortcut in the code i provided.
In the early sample datatable you provided, the Week value was 1,2 and 3 thus i took it as the counter which you can see it as xi in the code.
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 xi Step -1 y(i) = y(i - 1) Next y(xi - 1) = 0 End If Next
In the newest datatable, the week could be 45,46 and so on, so the counter xi would break.
To solve this problem, all you need to do is to add a fresh counter in the code bove. Please refer to below demo:
aspx:
<%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Chart ID="Chart1" runat="server" Height="594px" Width="1000px" BackColor="Silver" BorderlineColor="LightGray"> <Titles> <asp:Title Name="Items" Text="Created IM by subcode" /> </Titles> <Legends> <asp:Legend Alignment="Center" Docking="Right" IsTextAutoFit="True" Name="Default" LegendStyle="column" /> </Legends> <ChartAreas> <asp:ChartArea Name="ChartArea1" BorderWidth="1" /> </ChartAreas> </asp:Chart> </div> </form> </body> </html>
cs:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then 'Fetch the data from database. 'Dim query As String = "SELECT distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, week" Dim dt As DataTable = New DataTable() dt.Columns.AddRange(New DataColumn(2) {New DataColumn("CODE"), New DataColumn("Week", Type.GetType("System.Int32")), New DataColumn("TickC", Type.GetType("System.Int32"))}) dt.Rows.Add("ACCESS ISSUE", 44, 2) dt.Rows.Add("APPLICATION ISSUE", 44, 6) dt.Rows.Add("PERFORMANCE", 44, 2) dt.Rows.Add("T4-PERFORMANCE", 44, 1) dt.Rows.Add("ACCESS ISSUE", 45, 9) dt.Rows.Add("APPLICATION ERROR", 45, 1) dt.Rows.Add("APPLICATION ISSUE", 45, 27) dt.Rows.Add("CONNECTIVITY", 45, 8) dt.Rows.Add("PERFORMANCE", 45, 9) dt.Rows.Add("T4 - ACCESS ISSUE", 45, 5) dt.Rows.Add("T4 - CONNECTIVITY", 45, 4) dt.Rows.Add("T4 - PERFORMANCE", 45, 1) dt.Rows.Add("ACCESS ISSUE", 46, 13) dt.Rows.Add("APPLICATION ISSUE", 46, 23) dt.Rows.Add("CONFIGURATION", 46, 1) dt.Rows.Add("CONNECTIVITY", 46, 1) dt.Rows.Add("CONSULTANCY", 46, 1) dt.Rows.Add("PERFORMANCE", 46, 6) dt.Rows.Add("T4 - ACCESS ISSUE", 46, 6) dt.Rows.Add("T4 - CONFIGURATION", 46, 2) dt.Rows.Add("T4 - CONNECTIVITY", 46, 4) dt.Rows.Add("T4 - PERFORMANCE", 46, 2) dt.Rows.Add("ACCESS ISSUE", 47, 12) dt.Rows.Add("APPLICATION ISSUE", 47, 25) dt.Rows.Add("CONFIGURATION", 47, 5) dt.Rows.Add("CONNECTIVITY", 47, 4) dt.Rows.Add("CONSULTANCY", 47, 5) dt.Rows.Add("PERFORMANCE", 47, 6) dt.Rows.Add("T4 - ACCESS ISSUE", 47, 2) dt.Rows.Add("T4 - CONFIGURATION", 47, 3) dt.Rows.Add("T4 - CONNECTIVITY", 47, 5) dt.Rows.Add("ACCESS ISSUE", 48, 15) dt.Rows.Add("APPLICATION ISSUE", 48, 20) dt.Rows.Add("LICENSE", 48, 1) dt.Rows.Add("PERFORMANCE", 48, 3) dt.Rows.Add("PROFILE", 48, 1) dt.Rows.Add("T4 - ACCESS ISSUE", 48, 1) dt.Rows.Add("T4 - CONFIGURATION", 48, 1) dt.Rows.Add("T4 - CONNECTIVITY", 48, 2) dt.Rows.Add("T4 - PERFORMANCE", 48, 1) dt.Rows.Add("ACCESS ISSUE", 49, 1) dt.Rows.Add("APPLICATION ISSUE", 49, 7) dt.Rows.Add("PERFORMANCE", 49, 1) dt.Rows.Add("T4 - ACCESS ISSUE", 49, 1) dt.Rows.Add("T4 - CONFIGURATION", 49, 1) dt.Rows.Add("T4 - PERFORMANCE", 49, 1) '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 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 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)" 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
Below is the result of above demo:
Hope this could help.
Best Regard,
Yang Shen
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, December 4, 2019 5:34 AM -
User705223197 posted
Fantastic. much much appreciated. Your knowledge, responsiveness and patience has helped me enormously. I will spend many hours now going through this; my next objective will be to include grouping by year in order to show last twelves ordered correctly by year, week no. It's ordered nicely as is as we've happened to be in December! :) Another column "year" to add!
Thank you for this head-start.
Wednesday, December 4, 2019 7:45 AM