Google Chart API: Load Google Chart on DropDownList Change in ASP.Net using C# and VB.Net

Last Reply 12 days ago By dharmendr

Posted 14 days ago

I would like to load the google chart based on the dropdown list selected value. Below is the code I bind with a database. 

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Page.IsPostBack == false)
            {
                studentDetailsDAO sdDAO = new studentDetailsDAO();
                List<studentDetails> pemClass = sdDAO.displayClass();
                ddlClass.Items.Clear();
                ddlClass.Items.Insert(0, new ListItem("--Select--", "0"));
                ddlClass.AppendDataBoundItems = true;
                ddlClass.DataTextField = "pemGroup";
                ddlClass.DataValueField = "pemGroup";
                ddlClass.DataSource = pemClass;
                ddlClass.DataBind();
            }

        }

Below is the code to draw Chart in aspx.cs

        [WebMethod]
        public static List<object> GetChartDataClass(string pemGroup)
        {
            string query = "SELECT countryName, Count(formID) NumberOfStudents FROM TripFormStudent inner join Trip on TripFormStudent.tripID = Trip.tripID inner join Country on Trip.countryCode = Country.countryCode inner join TbStudentPro on TripFormStudent.adminNo = TbStudentPro.adminNO where TripFormStudent.status = 'accepted' AND paymentStatus = 'accepted' AND pemGroup  = @pemGroup Group by countryName";
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            List<object> chartData = new List<object>();
            chartData.Add(new object[] { "countryName", "NumberOfStudents" });
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@pemGroup", pemGroup);
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            chartData.Add(new object[] { sdr["countryName"], sdr["NumberOfStudents"] });
                        }
                    }
                    con.Close();
                    return chartData;
                }
            }
        }

Now, I'm stuck. I don't know how to put the value in. 

I hope you can solve. Thank you.

Posted 12 days ago

Hi kitty,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        google.charts.load('current', { 'packages': ['corechart'] });
        //google.charts.setOnLoadCallback(drawChart);

        $('#ddlCountries').on('change', function () {
            drawChart($(this).find("option:selected").val());
        });
        function drawChart(country) {
            $.ajax({
                url: "Default.aspx/GetChartData",
                data: '{country:"' + country + '"}',
                type: "POST",
                contentType: "application/json; charset=utf-8",
                dataType: "json"
            }).done(function (data) {
                var info = new google.visualization.DataTable();
                info.addColumn('string', 'ShipCity');
                info.addColumn('number', 'TotalOrders');
                var json = data.d;
                json.forEach(function (row) {
                    info.addRow([row[0], row[1]]);
                });
                var options = {
                    'title': $('#ddlCountries').find("option:selected").val() + ' City Distribution',
                    'width': 400,
                    'height': 300
                };
                var chart = new google.visualization.PieChart(document.getElementById('divChart'));
                chart.draw(info, options);
            }).fail(function (err) {
                debugger;
                console.log(err);
            });
        }
    });
</script>
<asp:DropDownList ID="ddlCountries" runat="server">
</asp:DropDownList>
<hr />
<div id="divChart" style="width: 900px; height: 500px;">
</div>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT ShipCountry FROM Orders", con))
            {
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
        }
        ddlCountries.Items.Clear();
        ddlCountries.Items.Insert(0, new ListItem("--Select--", "0"));
        ddlCountries.AppendDataBoundItems = true;
        ddlCountries.DataTextField = "ShipCountry";
        ddlCountries.DataValueField = "ShipCountry";
        ddlCountries.DataSource = dt;
        ddlCountries.DataBind();
    }
}

[WebMethod]
public static List<object> GetChartData(string country)
{
    List<object> chartData = new List<object>();
    string query = "SELECT TOP 5 ShipCity, COUNT(orderid) TotalOrders FROM Orders WHERE ShipCountry = @Country GROUP BY ShipCity";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@Country", country);
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    chartData.Add(new object[] { sdr["ShipCity"], sdr["TotalOrders"] });
                }
            }
            con.Close();
            return chartData;
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = New DataTable()
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand("SELECT DISTINCT ShipCountry FROM Orders", con)
                cmd.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                da.Fill(dt)
            End Using
        End Using

        ddlCountries.Items.Clear()
        ddlCountries.Items.Insert(0, New ListItem("--Select--", "0"))
        ddlCountries.AppendDataBoundItems = True
        ddlCountries.DataTextField = "ShipCountry"
        ddlCountries.DataValueField = "ShipCountry"
        ddlCountries.DataSource = dt
        ddlCountries.DataBind()
    End If
End Sub

<WebMethod()>
Public Shared Function GetChartData(ByVal country As String) As List(Of Object)
    Dim chartData As List(Of Object) = New List(Of Object)()
    Dim query As String = "SELECT TOP 10 ShipCity, COUNT(orderid) TotalOrders FROM Orders WHERE ShipCountry = @Country GROUP BY ShipCity"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@Country", country)
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    chartData.Add(New Object() {sdr("ShipCity"), sdr("TotalOrders")})
                End While
            End Using
            con.Close()
            Return chartData
        End Using
    End Using
End Function

Screenshot