Hi gsmm,
Since you are using Bar chart you need some modification in your code.
Also there are lots of mistake in your code.
gsmm says:
$('btnDisplay').on('click', function () {
Here you jquery selector for button click event is wrong.
gsmm says:
drawChart();
In your function you are not passing any dropdown value.
gsmm says:
string query = "SELECT DATE_ID,KPI_Value,TARGET_YEAR1,BASELINE,WORST_VALUE from rr_result";
Your select query doesnot have where condition.
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
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.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'] });
$('#btnDisplay').on('click', function () {
var ddlCountry1 = $("#ddlCountry1").find("option:selected").text();
var ddlCountry2 = $("#ddlCountry2").find("option:selected").text();
drawChart(ddlCountry1, ddlCountry2);
return false;
});
function drawChart(country1, country2) {
var obj = {};
obj.country1 = country1;
obj.country2 = country2;
$.ajax({
url: "Default.aspx/GetChartData",
data: JSON.stringify(obj),
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json"
}).done(function (data) {
var options = {
'title': $('#ddlCountry1').find("option:selected").val() + " And " + $('#ddlCountry2').find("option:selected").val() + ' City Distribution',
'width': 400,
'height': 300,
bar: { groupWidth: "50%" },
legend: { position: "none" },
isStacked: true
};
var data = google.visualization.arrayToDataTable(data.d);
var chart = new google.visualization.BarChart(document.getElementById('divChart'));
chart.draw(data, options);
}).fail(function (err) {
console.log(err);
}).error(function (err) {
console.log(err);
});
}
});
</script>
<asp:DropDownList ID="ddlCountry1" runat="server" AutoPostBack="true">
</asp:DropDownList>
<asp:DropDownList ID="ddlCountry2" runat="server" AutoPostBack="true">
</asp:DropDownList>
<asp:Button Text="Submit" runat="server" ID="btnDisplay" CssClass="btn btn-link" />
<hr />
<div class="row">
<div class="col-lg-6">
<div id="divChart" style="width: 900px; height: 500px;">
</div>
</div>
</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);
}
}
ddlCountry1.Items.Clear();
ddlCountry1.Items.Insert(0, new ListItem("--Select--", "0"));
ddlCountry1.AppendDataBoundItems = true;
ddlCountry1.DataTextField = "ShipCountry";
ddlCountry1.DataValueField = "ShipCountry";
ddlCountry1.DataSource = dt;
ddlCountry1.DataBind();
ddlCountry2.Items.Clear();
ddlCountry2.Items.Insert(0, new ListItem("--Select--", "0"));
ddlCountry2.AppendDataBoundItems = true;
ddlCountry2.DataTextField = "ShipCountry";
ddlCountry2.DataValueField = "ShipCountry";
ddlCountry2.DataSource = dt;
ddlCountry2.DataBind();
}
}
[WebMethod]
public static List<object> GetChartData(string country1, string country2)
{
List<object> chartData = new List<object>();
chartData.Add(new object[] { "ShipCity", "TotalOrders" });
string query = "SELECT TOP 5 ShipCity, COUNT(orderid) TotalOrders FROM Orders WHERE ShipCountry BETWEEN @Country1 AND @Country2 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("@Country1", country1);
cmd.Parameters.AddWithValue("@Country2", country2);
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
ddlCountry1.Items.Clear()
ddlCountry1.Items.Insert(0, New ListItem("--Select--", "0"))
ddlCountry1.AppendDataBoundItems = True
ddlCountry1.DataTextField = "ShipCountry"
ddlCountry1.DataValueField = "ShipCountry"
ddlCountry1.DataSource = dt
ddlCountry1.DataBind()
ddlCountry2.Items.Clear()
ddlCountry2.Items.Insert(0, New ListItem("--Select--", "0"))
ddlCountry2.AppendDataBoundItems = True
ddlCountry2.DataTextField = "ShipCountry"
ddlCountry2.DataValueField = "ShipCountry"
ddlCountry2.DataSource = dt
ddlCountry2.DataBind()
End If
End Sub
<WebMethod()>
Public Shared Function GetChartData(ByVal country1 As String, ByVal country2 As String) As List(Of Object)
Dim chartData As List(Of Object) = New List(Of Object)()
chartData.Add(New Object() {"ShipCity", "TotalOrders"})
Dim query As String = "SELECT TOP 5 ShipCity, COUNT(orderid) TotalOrders FROM Orders WHERE ShipCountry BETWEEN @Country1 AND @Country2 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("@Country1", country1)
cmd.Parameters.AddWithValue("@Country2", country2)
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
