ASP.NET: can not filter data in google chart based on data from database

Question

I'm trying to filter data in different google chart based on college branches. I have created a dropdown list to filter so it will show the data based on the selected branch. but it didn't work. any idea what is the possible reason?

this is my code to fetch data into the dropdownlist from the database:

 Dim dt As DataTable = New DataTable()  
            Dim constr As String = ConfigurationManager.ConnectionStrings("dbconn").ConnectionString  
            Using con As SqlConnection = New SqlConnection(constr)  
                Using cmd As SqlCommand = New SqlCommand("SELECT * FROM tblUniversityBranches", con)  
                    cmd.CommandType = CommandType.Text  
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)  
                    da.Fill(dt)  
                End Using  
            End Using  
  
            ddlBranch.Items.Clear()  
            ddlBranch.Items.Insert(0, New ListItem("--Select--", "0"))  
            ddlBranch.AppendDataBoundItems = True  
            ddlBranch.DataTextField = "UniBranchNameEn"  
            ddlBranch.DataValueField = "UniBranchID"  
            ddlBranch.DataSource = dt  
            ddlBranch.DataBind()  



    <WebMethod()>  
    Public Shared Function GetChartDataByDept() As List(Of Object)  
        Dim query As String = "SELECT tblDepartments.DeptNameEn, COUNT(EmpID) Total"  
        query += " FROM tblEmployee INNER JOIN tblDepartments ON tblEmployee.DeptID = tblDepartments.DeptID "  
        query += " GROUP BY tblDepartments.DeptNameEn"  
        Dim constr As String = ConfigurationManager.ConnectionStrings("ddconn").ConnectionString  
        Dim chartData As New List(Of Object)()  
        chartData.Add(New Object() {"DeptNameEn", "Total"})  
        Using con As New SqlConnection(constr)  
            Using cmd As New SqlCommand(query)  
                cmd.CommandType = CommandType.Text  
                cmd.Connection = con  
                con.Open()  
                Using sdr As SqlDataReader = cmd.ExecuteReader()  
                    While sdr.Read()  
                        chartData.Add(New Object() {sdr("DeptNameEn"), sdr("Total")})  
                    End While  
                End Using  
                con.Close()  
                Return chartData  
            End Using  
        End Using  
    End Function  
    <WebMethod()>  
    Public Shared Function GetChartDataByNationality() As List(Of Object)  
        Dim query As String = "SELECT tblCountriesNationalities.NationalityNameEn, COUNT(EmpID) Total"  
        query += " FROM tblEmployee INNER JOIN tblCountriesNationalities ON tblEmployee.NationalityID = tblCountriesNationalities.CountryNationalityID "  
        query += " GROUP BY tblCountriesNationalities.NationalityNameEn"  
        Dim constr As String = ConfigurationManager.ConnectionStrings("dbConn").ConnectionString  
        Dim chartData As New List(Of Object)()  
        chartData.Add(New Object() {"NationalityNameEn", "Total"})  
        Using con As New SqlConnection(constr)  
            Using cmd As New SqlCommand(query)  
                cmd.CommandType = CommandType.Text  
                cmd.Connection = con  
                con.Open()  
                Using sdr As SqlDataReader = cmd.ExecuteReader()  
                    While sdr.Read()  
                        chartData.Add(New Object() {sdr("NationalityNameEn"), sdr("Total")})  
                    End While  
                End Using  
                con.Close()  
                Return chartData  
            End Using  
        End Using  
    End Function  
    <WebMethod()>  
    Public Shared Function GetChartDataByEmployer() As List(Of Object)  
        Dim query As String = "SELECT tblEmployers.EmployerNameEn, COUNT(EmpID) Total"  
        query += " FROM tblEmployee INNER JOIN tblEmployers ON tblEmployee.employerid = tblEmployers.EmployerID "  
        query += " GROUP BY tblEmployers.EmployerNameEn"  
        Dim constr As String = ConfigurationManager.ConnectionStrings("dbConn").ConnectionString  
        Dim chartData As New List(Of Object)()  
        chartData.Add(New Object() {"EmployerNameEn", "Total"})  
        Using con As New SqlConnection(constr)  
            Using cmd As New SqlCommand(query)  
                cmd.CommandType = CommandType.Text  
                cmd.Connection = con  
                con.Open()  
                Using sdr As SqlDataReader = cmd.ExecuteReader()  
                    While sdr.Read()  
                        chartData.Add(New Object() {sdr("EmployerNameEn"), sdr("Total")})  
                    End While  
                End Using  
                con.Close()  
                Return chartData  
            End Using  
        End Using  
    End Function  
    <WebMethod()>  
    Public Shared Function GetChartDataByGender() As List(Of Object)  
        Dim query As String = "SELECT tblGender.GenderNameEn, COUNT(EmpID) Total"  
        query += " FROM tblEmployee INNER JOIN tblGender ON tblEmployee.GenderID = tblGender.GenderID "  
        query += " GROUP BY tblGender.GenderNameEn"  
        Dim constr As String = ConfigurationManager.ConnectionStrings("dbConn").ConnectionString  
        Dim chartData As New List(Of Object)()  
        chartData.Add(New Object() {"GenderNameEn", "Total"})  
        Using con As New SqlConnection(constr)  
            Using cmd As New SqlCommand(query)  
                cmd.CommandType = CommandType.Text  
                cmd.Connection = con  
                con.Open()  
                Using sdr As SqlDataReader = cmd.ExecuteReader()  
                    While sdr.Read()  
                        chartData.Add(New Object() {sdr("GenderNameEn"), sdr("Total")})  
                    End While  
                End Using  
                con.Close()  
                Return chartData  
            End Using  
        End Using  
    End Function  
    <WebMethod()>  
    Public Shared Function GetChartDataByDesignation() As List(Of Object)  
        Dim query As String = "SELECT tblDesignations.DesigNameEn, COUNT(EmpID) Total"  
        query += " FROM tblEmployee INNER JOIN tblDesignations ON tblEmployee.DesigID = tblDesignations.DesigID "  
        query += " GROUP BY tblDesignations.DesigNameEn"  
        Dim constr As String = ConfigurationManager.ConnectionStrings("dbConn").ConnectionString  
        Dim chartData As New List(Of Object)()  
        chartData.Add(New Object() {"DesigNameEn", "Total"})  
        Using con As New SqlConnection(constr)  
            Using cmd As New SqlCommand(query)  
                cmd.CommandType = CommandType.Text  
                cmd.Connection = con  
                con.Open()  
                Using sdr As SqlDataReader = cmd.ExecuteReader()  
                    While sdr.Read()  
                        chartData.Add(New Object() {sdr("DesigNameEn"), sdr("Total")})  
                    End While  
                End Using  
                con.Close()  
                Return chartData  
            End Using  
        End Using  
    End Function  
    <WebMethod()>  
    Public Shared Function GetChartData() As List(Of Object)  
        Dim query As String = "SELECT tblEmployeeStatus.EmployeeStatusNameEn, COUNT(EmpID) Total"  
        query += " FROM tblEmployee INNER JOIN tblEmployeeStatus ON tblEmployee.Status = tblEmployeeStatus.Status "  
        query += " GROUP BY tblEmployeeStatus.EmployeeStatusNameEn"  
        Dim constr As String = ConfigurationManager.ConnectionStrings("dbConn").ConnectionString  
        Dim chartData As New List(Of Object)()  
        chartData.Add(New Object() {"EmployeeStatusNameEn", "Total"})  
        Using con As New SqlConnection(constr)  
            Using cmd As New SqlCommand(query)  
                cmd.CommandType = CommandType.Text  
                cmd.Connection = con  
                con.Open()  
                Using sdr As SqlDataReader = cmd.ExecuteReader()  
                    While sdr.Read()  
                        chartData.Add(New Object() {sdr("EmployeeStatusNameEn"), sdr("Total")})  
                    End While  
                End Using  
                con.Close()  
                Return chartData  
            End Using  
        End Using  
    End Function  

default.aspx:

<%@ Page Title="" Language="VB" MasterPageFile="~/En/MasterPage.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>  
  
  
  
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
        <link rel="stylesheet" href="bootstrap/css/bootstrap.min.css" />  
     <link rel="stylesheet" href="plugins/DataTablesNew/DataTables-1.10.16/css/dataTables.bootstrap.min.css"/>  
     <link rel="stylesheet" href="plugins/DataTablesNew/Responsive-2.2.1/css/responsive.bootstrap.min.css" />  
    <link rel="stylesheet" href="dist/css/StyleSheet.css" />  
    <link rel="stylesheet" href="dist/css/waitMe.css"/>  
    <link rel="stylesheet" href="bootstrap/css/bootstrap-select.css"/>  
    <link rel="Stylesheet" href="plugins/jQueryUI/jquery-ui.css" />  
  
    <script src="plugins/jQuery/jQuery-2.1.4.min.js"></script>  
     <script src="plugins/DataTablesNew/DataTables-1.10.16/js/jquery.dataTables.min.js"></script>  
     <script src="plugins/DataTablesNew/DataTables-1.10.16/js/dataTables.bootstrap.min.js"></script>  
    <script src="plugins/DataTablesNew/Responsive-2.2.1/js/dataTables.responsive.min.js"></script>  
    <script src="plugins/DataTablesNew/Responsive-2.2.1/js/responsive.bootstrap.min.js"></script>  
    <script src="bootstrap/js/bootstrap-filestyle.min.js"></script>  
    <script src="dist/js/waitMe.js"></script>  
    <script src="bootstrap/js/bootstrap-select.js"></script>  
    <script src="plugins/jQueryUI/jquery-ui_1.0.8.js"></script>  
     <script src="plugins/chartjs/Chart.min.js"></script>  
  
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js"></script>  
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>  
    <script type="text/javascript">  
  
  
        google.charts.load('current', { 'packages': ['corechart'] });  
        google.charts.setOnLoadCallback(drawChart);  
        google.charts.setOnLoadCallback(drawChart1);  
        google.charts.setOnLoadCallback(drawChart2);  
        google.charts.setOnLoadCallback(drawBarChart);  
        google.setOnLoadCallback(drawChartt);  
        google.setOnLoadCallback(drawCharttt);  
         
         
      
         
         
        function drawChart() {  
            var options = {  
                title: 'Staff by Departments',  
                is3D: true,  
            };  
            $.ajax({  
                type: "POST",  
                url: "Default.aspx/GetChartDataByDept",  
                data: '{}',  
                contentType: "application/json; charset=utf-8",  
                dataType: "json",  
                success: function (r) {  
                    var data = google.visualization.arrayToDataTable(r.d);  
                    var chart = new google.visualization.PieChart(document.getElementById('piechart'));  
                    chart.draw(data, options);  
                }  
            });  
  
            //var chart = new google.visualization.PieChart(document.getElementById('piechart_3d'));  
            //chart.draw(data, options);  
        }  
        function drawChart1() {  
            var options = {  
                title: 'Staff by Nationality',  
                is3D: true,  
            };  
            $.ajax({  
                type: "POST",  
                url: "Default.aspx/GetChartDataByNationality",  
                data: '{}',  
                contentType: "application/json; charset=utf-8",  
                dataType: "json",  
                success: function (r) {  
                    var data = google.visualization.arrayToDataTable(r.d);  
                    //var chart = new google.visualization.ColumnChart($("#chart")[0]);  
                    var chart = new google.visualization.PieChart(document.getElementById('piechart1'));  
                    chart.draw(data, options);  
                }  
            });  
            //var chart = new google.visualization.PieChart(document.getElementById('piechart_3d'));  
            //chart.draw(data, options);  
        }  
          
        function drawChart2() {  
            var options = {  
                title: 'Staff by Employer',  
                is3D: true,  
            };  
            $.ajax({  
                type: "POST",  
                url: "Default.aspx/GetChartDataByEmployer",  
                data: '{}',  
                contentType: "application/json; charset=utf-8",  
                dataType: "json",  
                success: function (r) {  
                    var data = google.visualization.arrayToDataTable(r.d);  
                    //var chart = new google.visualization.ColumnChart($("#chart")[0]);  
                    var chart = new google.visualization.PieChart(document.getElementById('piechart2'));  
                    chart.draw(data, options);  
                }  
            });  
            //var chart = new google.visualization.PieChart(document.getElementById('piechart_3d'));  
            //chart.draw(data, options);  
        }  
  
  
        function drawBarChart() {  
            var options = {  
                title: 'Staff by Designation',  
                is3D: true,  
            };  
            $.ajax({  
                type: "POST",  
                url: "Default.aspx/GetChartDataByDesignation",  
                data: '{}',  
                contentType: "application/json; charset=utf-8",  
                dataType: "json",  
                success: function (r) {  
                    var data = google.visualization.arrayToDataTable(r.d);  
                    //var chart = new google.visualization.PieChart(document.getElementById('piechart_3d'));  
                    var chart = new google.visualization.ColumnChart(document.getElementById('AreaChart'));  
                    chart.draw(data, options);  
                },  
                failure: function (r) {  
                    alert(r.d);  
                },  
                error: function (r) {  
                    alert(r.d);  
                }  
            });  
        }  
        function drawChartt() {  
            var options = {  
                title: 'Staff by Status',  
                pieHole: 0.5  
            };  
            $.ajax({  
                type: "POST",  
                url: "Default.aspx/GetChartData",  
                data: '{}',  
                contentType: "application/json; charset=utf-8",  
                dataType: "json",  
                success: function (r) {  
                    var data = google.visualization.arrayToDataTable(r.d);  
                    var chart = new google.visualization.PieChart($("#chartt")[0]);  
                    chart.draw(data, options);  
                },  
                failure: function (r) {  
                    alert(r.d);  
                },  
                error: function (r) {  
                    alert(r.d);  
                }  
            });  
        }  
        function drawCharttt() {  
            var options = {  
                title: 'Staff by Gender',  
               
                bar: { groupWidth: "95%" },  
                legend: { position: "none" },  
                isStacked: true  
            };  
            $.ajax({  
                type: "POST",  
                url: "Default.aspx/GetChartDataByGender",  
                data: '{}',  
                contentType: "application/json; charset=utf-8",  
                dataType: "json",  
                success: function (r) {  
                    var data = google.visualization.arrayToDataTable(r.d);  
                    var chart = new google.visualization.BarChart($("#charttt")[0]);  
                    chart.draw(data, options);  
                },  
                failure: function (r) {  
                    alert(r.d);  
                },  
                error: function (r) {  
                    alert(r.d);  
                }  
            });  
        }  
        //function drawChart3() {  
        //    var options = {  
        //        title: 'Staff by Gender',  
        //        is3D: true,  
        //    };  
        //    $.ajax({  
        //        type: "POST",  
        //        url: "Default.aspx/GetChartDataByGender",  
        //        data: '{}',  
        //        contentType: "application/json; charset=utf-8",  
        //        dataType: "json",  
        //        success: function (r) {  
        //            var data = google.visualization.arrayToDataTable(r.d);  
        //            //var chart = new google.visualization.ColumnChart($("#chart")[0]);  
        //            var chart = new google.visualization.PieChart(document.getElementById('piechart3'));  
        //            chart.draw(data, options);  
        //        }  
        //    });  
        //    //var chart = new google.visualization.PieChart(document.getElementById('piechart_3d'));  
        //    //chart.draw(data, options);  
        //}  
         
  
  
    </script>  
  
    <style>  
        .divtop{  
            margin-top:50px;  
        }  
        @media (max-width:960) {  
            .divtop {  
                margin-top:200px;  
                background-color:antiquewhite;  
            }  
        }  
    </style>  
      
     
</asp:Content>  
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
       <!-- Content Header (Page header) -->  
    <%--<div class="divtop"></div>--%>  
          
          
       <div id="conte" runat="server">  
           <section class="content">  
           <div class="row">  
            <div class="col-md-12">  
              <!-- Box Comment -->  
              <div class="box box-widget">  
                <div class="box-header with-border">  
                                  
                </div><!-- /.box-header -->  
                <div class="box-body">  
                 <div class="row">  
            <div class="col-lg-3 col-xs-6">  
              <!-- small box -->  
                <a href="#" style="text-decoration:none">  
              <div class="small-box bg-aqua-gradient">  
                <div class="inner">  
                  <h3>  
                      <asp:Label ID="lblBranchesCount" runat="server" Text=""></asp:Label></h3>  
                  <p>Branches</p>  
                </div>  
                <div class="icon">  
                  <i class="fa fa-university"></i>  
                </div>  
                <div class="small-box-footer">  
                     
                </div>  
              </div>  
                    </a>  
            </div><!-- ./col -->  
            <div class="col-lg-3 col-xs-6">  
              <!-- small box -->  
                <a href="#" style="text-decoration:none">  
              <div class="small-box bg-yellow-gradient">  
                <div class="inner">  
                  <h3><asp:Label ID="lblStaffCount" runat="server" Text=""></asp:Label></h3>  
                  <p>Staff</p>  
                </div>  
                <div class="icon">  
                  <i class="fa fa-group"></i>  
                </div>  
                <div class="small-box-footer">  
                     
                </div>  
              </div>  
                    </a>  
            </div>  
            <div class="col-lg-3 col-xs-6">  
              <!-- small box -->  
                <a href="#" style="text-decoration:none">  
              <div class="small-box bg-blue-gradient">  
                <div class="inner">  
                  <h3><asp:Label ID="lblDepartment" runat="server" Text="0"></asp:Label></h3>  
                  <p>Departments</p>  
                </div>  
                <div class="icon">  
                  <i class="fa fa-group fa-university"></i>  
                </div>  
                <div class="small-box-footer">  
                     
                </div>  
              </div>  
                    </a>  
            </div>  
  
            <div class="col-lg-3 col-xs-6">  
              <!-- small box -->  
                <a href="#" style="text-decoration:none">  
              <div class="small-box bg-blue-gradient">  
                <div class="inner">  
                  <h3><asp:Label ID="lblDesignations" runat="server" Text="0"></asp:Label></h3>  
                  <p>Designations</p>  
                </div>  
                <div class="icon">  
                  <i class="fa fa-group fa-group"></i>  
                </div>  
                <div class="small-box-footer">  
                     
                </div>  
              </div>  
                    </a>  
            </div>  
  
                
          </div>  
                    <div class="row hidden">  
                        <div class="col-lg-5">  
                            <div class="box box-default">                 
                             <div class="box-header">  
                                  <h3 class="box-title">Employee by Nationality</h3>  
                                   <div class="box-tools pull-right">  
                      
                  </div>  
                </div>                  
<div class="box-body">                 <canvas id="myChart" style="width:100%;max-width:600px"></canvas>  
                  
</div><!-- /.box-body -->                
</div>  
                        </div>  
                    </div>  
  
                      
                 
                          
               </div>  
                  
                 
              </div><!-- /.box -->  
            </div><!-- /.col -->  
              
                 
            
</div>  
                 
            <div class="row">  
            <div class="col-md-12">  
              <!-- Box Comment -->  
              <div class="box box-widget">  
                <div class="box-header with-border">  
                                  
                </div><!-- /.box-header -->  
                <div class="box-body">  
              
             
  
                          <div class="row">  
                              <div>  
                                  <strong>  
                                  <asp:Label ID="Label1" runat="server" Text="Choose Branch:"></asp:Label>   </strong>   <asp:DropDownList ID="ddlBranch" runat="server"></asp:DropDownList>  
                              </div>  
                        <div class="col-lg-4">  
                                       
                               
                    
                                 
                <div id="piechart"></div>  
                  
      
  
                        </div>  
                                  <div class="col-lg-4">  
                                       
                               
                    
                                 
                <div id="piechart1"></div>  
                  
      
  
                        </div>  
                                        <div class="col-lg-4">  
                                       
                               
                    
                                 
                <div id="piechart2"></div>  
                  
      
  
                        </div>  
                                 <div class="col-lg-4">  
                                       
                               
                  <div class="reaChart"></div>  
                                 
                <div id="chartt"></div>  
                  
      
  
                        </div>  
                                 
                              <div class="col-lg-4">  
                                       
                               
                    
                                 
                <div id="AreaChart"></div>  
                  
      
  
                        </div>  
                                 <div class="col-lg-4">  
                            <div id="charttt">  
</div>      </div>    
                    </div>  
                 
                          
               </div>  
                  
                 
              </div><!-- /.box -->  
            </div><!-- /.col -->  
              
                 
            
</div>  
            </section>  
       </div>  
      

this is a screenshot of my aspx:

Answer 1

Hi @mush ,
I checked your code and found that you didn't set the SelectedIndex property of DropDownList .
The DropDownList.SelectedIndex property gets or sets the index of the selected item in the DropDownList control.
https://learn.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.dropdownlist.selectedindex?view=netframework-4.8
You can refer to the following example:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load  
        If Not IsPostBack Then  
            GetChartData()  
            GetChartTypes()  
        End If  
    End Sub  
    Private Sub GetChartTypes()  
        For Each chartType As Integer In [Enum].GetValues(GetType(SeriesChartType))  
            Dim li As ListItem = New ListItem([Enum].GetName(GetType(SeriesChartType), chartType), chartType.ToString())  
            ddlChart.Items.Add(li)  
        Next  
    End Sub  
    Private Sub GetChartData()  
        Dim CS As String = ConfigurationManager.ConnectionStrings("DBCS").ConnectionString  
  
        Using con As SqlConnection = New SqlConnection(CS)  
            Dim cmd As SqlCommand = New SqlCommand("spGetStudentMarks", con)  
            cmd.CommandType = CommandType.StoredProcedure  
            con.Open()  
            Dim rdr As SqlDataReader = cmd.ExecuteReader()  
            'Retrieve the Series to which we want to add DataPoints   
            Dim series As Series = Chart1.Series("Series1")  
            'Loop thru each Student record    
            While rdr.Read()  
                'Add X And Y values using AddXY() method    
                series.Points.AddXY(rdr("StudentName").ToString(), rdr("TotalMarks"))  
            End While  
        End Using  
    End Sub  
    Protected Sub ddlChart_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)  
        'Call Get ChartData() method when the user select a different chart type   
        GetChartData()  
        Chart1.Series("Series1").ChartType = CType([Enum].Parse(GetType(SeriesChartType), ddlChart.SelectedValue), SeriesChartType)  
    End Sub  

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.