Google Chart APIs: Populate Google Chart from database on DropDownList change using jQuery AJAX in ASP.Net

Last Reply 9 days ago By dharmendr

Posted 10 days ago

https://www.aspsnippets.com/Articles/Using-HTML5-Canvas-charts-in-ASPNet.aspx

The above chart is there in HTML Canvas.

Can i do the same chart in google chart with parameter

I tried the below method .. It shows undefined on page load... Let me know what i did wrong ... Let me know how to do so ...

Once again thanks for quick reply ...

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <script src="jquery.min.js" type="text/javascript"></script>
    <script src="jsapi.js" type="text/javascript"></script>
    <asp:DropDownList ID="DrpMonth" runat="server" >
    <asp:ListItem Text = "January"  Value = "January" Selected="True"></asp:ListItem>
    <asp:ListItem Text = "March"  Value = "March"></asp:ListItem>
    <asp:ListItem Text = "April"  Value = "April"></asp:ListItem>
    <asp:ListItem Text = "June"  Value = "June"></asp:ListItem>
    <asp:ListItem Text = "November"  Value = "November"></asp:ListItem>
    </asp:DropDownList>
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var options = {
                title: 'Chart',
                is3D: false
            };
 
            var Month = $('#<%=DrpMonth.ClientID %>').val();
        
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetChartData",
                data: "{Month: '" + $("[id*=DrpMonth]").val() + "'}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (r) {
                    var data = google.visualization.arrayToDataTable(r.d);
                    var chart = new google.visualization.PieChart($("#chart")[0]);
                    chart.draw(data, options);
                },
                failure: function (r) {
                    alert(r.d);
                },
                error: function (r) {
                    alert(r.d);
                }
            });
        }
    </script>   
    <div id="chart" style="width: 900px; height: 500px;">
    </div> 
    </form>     
</body>
</html>

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
 
    [WebMethod]
    public static List<object> GetChartData(string Month)
    {
 
        string query = "SELECT DATENAME(MONTH,Date) as [MonthName], sum(isnull(Qty,0)) as OrderQty FROM [TestDB].[dbo].[Order] where DATENAME(MONTH,Date)=@Month ";
        query += " GROUP BY DATENAME(MONTH, Date)";
 
        string constr = ConfigurationManager.ConnectionStrings["Con"].ConnectionString;
 
 
        List<object> chartData = new List<object>();
        chartData.Add(new object[]
        {
            "MonthName", "OrderQty"
        });
 
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                con.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        chartData.Add(new object[]
                    {
                        sdr["MonthName"], sdr["OrderQty"]
                    });
                    }
                }
 
                con.Close();
                return chartData;
            }
        } 
    }  
}

 

Posted 9 days ago

Before

ashraft1 says:
con.Open();

 Add line

cmd.Parameters.AddWithValue("@Month", Month)

 


Posted 9 days ago

Check the example using the below article.

Google Chart APIs: Google (Pie / Doughnut) Chart example with database in ASP.Net

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
    google.load("visualization", "1", { packages: ["corechart"] });
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        var country = $("#DrpMonth option:selected").val();
        var options = { title: country + ' Distribution', is3D: false };
        $.ajax({
            type: "POST",
            url: "CS.aspx/GetChartData",
            data: "{country: '" + country + "'}",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {
                var data = google.visualization.arrayToDataTable(r.d);
                var chart = new google.visualization.PieChart($("#chart")[0]);
                chart.draw(data, options);
            },
            failure: function (r) {
                alert(r.d);
            },
            error: function (r) {
                alert(r.d);
            }
        });
    }
</script>
<center>
    <asp:DropDownList ID="DrpMonth" runat="server" AutoPostBack="true">
        <asp:ListItem Text="Finland" Value="Finland" Selected="True"></asp:ListItem>
        <asp:ListItem Text="Brazil" Value="Brazil"></asp:ListItem>
        <asp:ListItem Text="USA" Value="USA"></asp:ListItem>
        <asp:ListItem Text="Italy" Value="Italy"></asp:ListItem>
        <asp:ListItem Text="Germany" Value="Germany"></asp:ListItem>
    </asp:DropDownList>
</center>
<div id="chart" style="width: 900px; height: 500px;">
</div>

C#

[WebMethod]
public static List<object> GetChartData(string country)
{
    string query = "SELECT ShipCity, COUNT(orderid) TotalOrders";
    query += " FROM Orders WHERE ShipCountry = @Country GROUP BY ShipCity";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    List<object> chartData = new List<object>();
    chartData.Add(new object[]
    {
        "ShipCity", "TotalOrders"
    });
    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;
        }
    }
}

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html