How to Create Multi-Series Chart
I would like to made Multi-Series Chart with user selection Department, Period and Rate of Case or Number of case.The data stored in MS SQL server. I completed the part of data gathering, and the related data were gathered from ms SQL Server and stored into two gridview. However, It cannot made the chart.
Here is my code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
gvRate.Visible = False
gvInt.Visible = False
gvRate.DataBind()
gvInt.DataBind()
btn_Print.Visible = False
Label52.Visible = True
Label51.Visible = True
Label51.Text = "Please select Type of Case/Department/Period before you pressed the 'GENERATE CHART BUTTON'."
RateChart.Visible = False
End Sub
Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged
Label20.Text = DropDownList1.SelectedValue
Label38.Text = DropDownList1.SelectedItem.Text
btn_Rate.Visible = False
btn_Int.Visible = False
If DropDownList1.SelectedIndex = -1 Then
btn_Rate.Visible = False
btn_Int.Visible = False
ElseIf DropDownList1.SelectedIndex = 0 Then
btn_Rate.Visible = False
btn_Int.Visible = False
ElseIf DropDownList1.SelectedIndex = 1 Then
Label001.Visible = True
Label001.Text = "Medication Administration Incidents only available for Generate Chart by Number Incidents"
btn_Rate.Visible = False
btn_Int.Visible = True
Else
Label001.Visible = False
btn_Rate.Visible = True
btn_Int.Visible = True
End If
End Sub
Protected Sub DropDownList2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList2.SelectedIndexChanged
Label21.Text = DropDownList2.SelectedValue
Label40.Text = DropDownList2.SelectedItem.Text
If DropDownList2.SelectedIndex < 1 Then
Label002.Visible = True
Label002.Text = "Please select Department from 1st Department Dropdown List First"
DropDownList13.SelectedIndex = -1
Else
Label002.Visible = False
End If
End Sub
Protected Sub DropDownList3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList3.SelectedIndexChanged
DropDownList7.SelectedIndex = -1
DropDownList9.SelectedIndex = -1
DropDownList8.SelectedIndex = -1
DropDownList10.SelectedIndex = -1
DropDownList11.SelectedIndex = -1
DropDownList12.SelectedIndex = -1
Label23.Text = DropDownList3.SelectedValue
Label32.Text = Label23.Text & Label24.Text
Label43.Text = DropDownList3.SelectedItem.Text
Label42.Text = Label44.Text & " " & Label43.Text
End Sub
Protected Sub DropDownList4_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList4.SelectedIndexChanged
DropDownList7.SelectedIndex = -1
DropDownList9.SelectedIndex = -1
DropDownList8.SelectedIndex = -1
DropDownList10.SelectedIndex = -1
DropDownList11.SelectedIndex = -1
DropDownList12.SelectedIndex = -1
Label24.Text = DropDownList4.SelectedValue
Label44.Text = DropDownList4.SelectedItem.Text
End Sub
Protected Sub DropDownList5_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList5.SelectedIndexChanged
DropDownList7.SelectedIndex = -1
DropDownList9.SelectedIndex = -1
DropDownList8.SelectedIndex = -1
DropDownList10.SelectedIndex = -1
DropDownList11.SelectedIndex = -1
DropDownList12.SelectedIndex = -1
Label25.Text = DropDownList5.SelectedValue
Label33.Text = Label25.Text & Label26.Text
Label46.Text = DropDownList5.SelectedItem.Text
Label45.Text = Label47.Text & " " & Label46.Text
End Sub
Protected Sub DropDownList6_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList6.SelectedIndexChanged
DropDownList7.SelectedIndex = -1
DropDownList9.SelectedIndex = -1
DropDownList8.SelectedIndex = -1
DropDownList10.SelectedIndex = -1
DropDownList11.SelectedIndex = -1
DropDownList12.SelectedIndex = -1
Label26.Text = DropDownList6.SelectedValue
Label47.Text = DropDownList6.SelectedItem.Text
End Sub
Protected Sub DropDownList7_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList7.SelectedIndexChanged
DropDownList3.SelectedIndex = -1
DropDownList4.SelectedIndex = -1
DropDownList5.SelectedIndex = -1
DropDownList6.SelectedIndex = -1
DropDownList11.SelectedIndex = -1
DropDownList12.SelectedIndex = -1
Label27.Text = DropDownList7.SelectedValue
Label32.Text = "_" & Label27.Text & Label28.Text
Label43.Text = DropDownList7.SelectedItem.Text
Label42.Text = Label44.Text & " " & Label43.Text
End Sub
Protected Sub DropDownList8_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList8.SelectedIndexChanged
DropDownList3.SelectedIndex = -1
DropDownList4.SelectedIndex = -1
DropDownList5.SelectedIndex = -1
DropDownList6.SelectedIndex = -1
DropDownList11.SelectedIndex = -1
DropDownList12.SelectedIndex = -1
Label28.Text = DropDownList8.SelectedValue
Label44.Text = DropDownList8.SelectedItem.Text
End Sub
Protected Sub DropDownList9_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList9.SelectedIndexChanged
DropDownList3.SelectedIndex = -1
DropDownList4.SelectedIndex = -1
DropDownList5.SelectedIndex = -1
DropDownList6.SelectedIndex = -1
DropDownList11.SelectedIndex = -1
DropDownList12.SelectedIndex = -1
Label29.Text = DropDownList9.SelectedValue
Label33.Text = "_" & Label29.Text & Label30.Text
Label46.Text = DropDownList9.SelectedItem.Text
Label45.Text = Label47.Text & " " & Label46.Text
End Sub
Protected Sub DropDownList10_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList10.SelectedIndexChanged
DropDownList3.SelectedIndex = -1
DropDownList4.SelectedIndex = -1
DropDownList5.SelectedIndex = -1
DropDownList6.SelectedIndex = -1
DropDownList11.SelectedIndex = -1
DropDownList12.SelectedIndex = -1
Label30.Text = DropDownList10.SelectedValue
Label47.Text = DropDownList10.SelectedItem.Text
End Sub
Protected Sub DropDownList11_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList11.SelectedIndexChanged
DropDownList3.SelectedIndex = -1
DropDownList4.SelectedIndex = -1
DropDownList5.SelectedIndex = -1
DropDownList6.SelectedIndex = -1
DropDownList7.SelectedIndex = -1
DropDownList8.SelectedIndex = -1
DropDownList9.SelectedIndex = -1
DropDownList10.SelectedIndex = -1
Label35.Text = DropDownList11.SelectedValue
Label42.Text = DropDownList11.SelectedItem.Text
End Sub
Protected Sub DropDownList12_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList12.SelectedIndexChanged
DropDownList3.SelectedIndex = -1
DropDownList4.SelectedIndex = -1
DropDownList5.SelectedIndex = -1
DropDownList6.SelectedIndex = -1
DropDownList7.SelectedIndex = -1
DropDownList8.SelectedIndex = -1
DropDownList9.SelectedIndex = -1
DropDownList10.SelectedIndex = -1
Label36.Text = DropDownList12.SelectedValue
Label32.Text = Label35.Text
Label33.Text = Label36.Text
Label45.Text = DropDownList12.SelectedItem.Text
If Label45.Text.Trim() = String.Empty Then
Label001.Visible = True
Label001.Text = "Please select the Period"
Else
Label001.Visible = False
End If
End Sub
Protected Sub btn_Reset_Click(sender As Object, e As EventArgs) Handles btn_Reset.Click
DropDownList1.SelectedIndex = -1
DropDownList2.SelectedIndex = -1
DropDownList3.SelectedIndex = -1
DropDownList4.SelectedIndex = -1
DropDownList5.SelectedIndex = -1
DropDownList6.SelectedIndex = -1
DropDownList7.SelectedIndex = -1
DropDownList9.SelectedIndex = -1
DropDownList8.SelectedIndex = -1
DropDownList10.SelectedIndex = -1
DropDownList11.SelectedIndex = -1
DropDownList12.SelectedIndex = -1
DropDownList13.SelectedIndex = -1
Label21.Text = ""
Label22.Text = ""
Label23.Text = ""
Label24.Text = ""
Label25.Text = ""
Label26.Text = ""
Label27.Text = ""
Label28.Text = ""
Label29.Text = ""
Label30.Text = ""
Label32.Text = ""
Label33.Text = ""
Label35.Text = ""
Label36.Text = ""
Label37.Text = ""
Label38.Text = ""
Label39.Text = ""
Label40.Text = ""
Label41.Text = ""
Label42.Text = ""
Label42.Text = ""
Label45.Text = ""
Label48.Text = ""
End Sub
Private Sub btn_Rate_Click(sender As Object, e As EventArgs) Handles btn_Rate.Click
Label37.Visible = True
Label39.Visible = True
Label48.Visible = True
Label49.Visible = False
Label51.Visible = False
Label52.Visible = False
Label37.Text = "The Chart of " & Label38.Text & " by Rate of Incidents "
Label48.Text = "From" & " " & Label42.Text & " " & "to" & " " & Label45.Text
If DropDownList13.SelectedIndex = -1 Then
Label39.Text = Label40.Text
Else
Label39.Text = Label40.Text & Label41.Text
End If
btn_Print.Visible = True
RateChart.Visible = True
End Sub
Protected Sub btn_Int_Click(sender As Object, e As EventArgs) Handles btn_Int.Click
Label001.Visible = False
Label37.Visible = True
Label39.Visible = True
Label48.Visible = True
Label51.Visible = False
Label52.Visible = False
Label37.Text = "The Chart of " & Label38.Text & " by Number of Incidents "
Label48.Text = "From" & " " & Label42.Text & " " & "to" & " " & Label45.Text
If DropDownList13.SelectedIndex = -1 Then
Label39.Text = Label40.Text
Else
Label39.Text = Label40.Text & Label41.Text
End If
btn_Print.Visible = True
RateChart.Visible = True
End Sub
Protected Sub btn_Print_Click(sender As Object, e As EventArgs) Handles btn_Print.Click
End Sub
Protected Sub DropDownList13_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList13.SelectedIndexChanged
Label22.Text = DropDownList13.SelectedValue
If DropDownList2.SelectedIndex > 0 Then
Label41.Text = " vs " & DropDownList13.SelectedItem.Text
Else
Label002.Visible = True
Label002.Text = "Please Select Department from 1st Department Dropdown List First"
Label31.Text = ""
Label41.Text = DropDownList13.SelectedItem.Text
End If
End Sub
Protected Sub gvRate_RowDataBound(sender As Object, e As EventArgs) Handles gvRate.RowDataBound
If Not Me.IsPostBack Then
Dim dtRate As DataTable = New DataTable("dtRate")
For Each cell As TableCell In gvRate.HeaderRow.Cells
dtRate.Columns.Add(cell.Text.Trim())
Next
For Each row As GridViewRow In gvRate.Rows
dtRate.Rows.Add()
For i As Integer = 0 To row.Cells.Count - 1
dtRate.Rows(row.RowIndex)(i) = row.Cells(i).Text.Trim()
Next
Next
Dim departments As List(Of String) = (From p In dtRate.AsEnumerable() Select p.Field(Of String)("Department")).Distinct().ToList()
If RateChart.Series.Count() = 1 Then RateChart.Series.Remove(RateChart.Series(0))
For Each Userdepartment As String In departments
Dim x As Integer() = (From p In dtRate.AsEnumerable()
Where p.Field(Of String)("Department") = Userdepartment
Order By p.Field(Of String)("Period_Shown")
Select Convert.ToInt32(p.Field(Of String)("Period_Shown"))).ToArray()
Dim y As Decimal() = (From p In dtRate.AsEnumerable()
Where p.Field(Of String)("Department") = Userdepartment
Order By p.Field(Of String)("Period_Shown")
Select Convert.ToDecimal(p.Field(Of String)("Rate_Int"))).ToArray()
RateChart.Series.Add(New Series(Userdepartment))
RateChart.Series(Userdepartment).IsValueShownAsLabel = True
RateChart.Series(Userdepartment).BorderWidth = 3
RateChart.Series(Userdepartment).ChartType = SeriesChartType.Line
RateChart.Series(Userdepartment).Points.DataBindXY(x, y)
Next
RateChart.Legends(0).Enabled = True
End If
End Sub
Protected Sub gvInt_RowDataBound(sender As Object, e As EventArgs) Handles gvInt.RowDataBound
If Not Me.IsPostBack Then
Dim dtRate As DataTable = New DataTable("dtRate")
For Each cell As TableCell In gvInt.HeaderRow.Cells
dtRate.Columns.Add(cell.Text.Trim())
Next
For Each row As GridViewRow In gvInt.Rows
dtRate.Rows.Add()
For i As Integer = 0 To row.Cells.Count - 1
dtRate.Rows(row.RowIndex)(i) = row.Cells(i).Text.Trim()
Next
Next
Dim departments As List(Of String) = (From p In dtRate.AsEnumerable() Select p.Field(Of String)("Department")).Distinct().ToList()
If RateChart.Series.Count() = 1 Then RateChart.Series.Remove(RateChart.Series(0))
For Each Userdepartment As String In departments
Dim x As Integer() = (From p In dtRate.AsEnumerable()
Where p.Field(Of String)("Department") = Userdepartment
Order By p.Field(Of String)("Period_Shown")
Select Convert.ToInt32(p.Field(Of String)("Period_Shown"))).ToArray()
Dim y As Decimal() = (From p In dtRate.AsEnumerable()
Where p.Field(Of String)("Department") = Userdepartment
Order By p.Field(Of String)("Period_Shown")
Select Convert.ToDecimal(p.Field(Of String)("Rate_Int"))).ToArray()
RateChart.Series.Add(New Series(Userdepartment))
RateChart.Series(Userdepartment).IsValueShownAsLabel = True
RateChart.Series(Userdepartment).BorderWidth = 3
RateChart.Series(Userdepartment).ChartType = SeriesChartType.Line
RateChart.Series(Userdepartment).Points.DataBindXY(x, y)
Next
RateChart.Legends(0).Enabled = True
End If
End Sub
Protected Sub RateChart_Load(sender As Object, e As EventArgs) Handles RateChart.Load
RateChart.Visible = False
End Sub
When I complied it, the first error on it is "Using the iteration variable in a query expression may have unexpected results. Instead, create a local variable within the loop and assign it the value of the iteration variable." The error variable is Userdepartment in "Protected Sub gvInt_RowDataBound" line of "Where p.Field(Of String)("Department") = Userdepartment"
How can I fix it and run the program smoothly? Thank you