[Solved] Google Stacked Column Chart Error: Specified cast is not valid

Hi clonekoadi,

Since your total column DataType is BIGINT you need to type cast the Field to Long.

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

Database

CREATE TABLE vgraph
(
	total	bigint,
	statusid	varchar(45),
	team	varchar(150)
)

INSERT INTO vgraph VALUES(1,'COMPLETED','ADB')
INSERT INTO vgraph VALUES(2,'COMPLETED','ABC')
INSERT INTO vgraph VALUES(2,'IN-PROGRESS','ADB')
INSERT INTO vgraph VALUES(1,'IN-PROGRESS','ABC')

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.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);
    function drawChart() {
        var options = {
            title: 'Team wise Status',
            width: 600,
            height: 400,
            legend: { position: 'top', maxLines: 3 },
            bar: { groupWidth: '75%' },
            isStacked: true
        };
        $.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.ColumnChart($("#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>

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#

[WebMethod]
public static List<object> GetChartData()
{
    //Fetch the Statistical data from database.
    string query = "SELECT * FROM vgraph";
    DataTable dt = GetData(query);

    //Get the DISTINCT statusid.
    List<object> chartData = new List<object>();
    List<string> status = (from p in dt.AsEnumerable()
                                select p.Field<string>("statusid")).Distinct().ToList();

    //Insert Label for status in First position.
    status.Insert(0, "status");

    //Add the status Array to the Chart Array.
    chartData.Add(status.ToArray());


    //Get the DISTINCT team.
    List<string> teams = (from p in dt.AsEnumerable()
                        select p.Field<string>("team")).Distinct().ToList();

    //Loop through the team.
    foreach (string team in teams)
    {
        List<object> totals = (from p in dt.AsEnumerable()
                                where p.Field<string>("team") == team
                                select p.Field<long>("total")).Cast<object>().ToList();

        totals.Insert(0, team.ToString());
        chartData.Add(totals.ToArray());
    }

    return chartData;
}

private static DataTable GetData(string query)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter(query, con))
        {
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
    }
}

VB.Net

<WebMethod()>
Public Shared Function GetChartData() As List(Of Object)
    'Fetch the Statistical data from database.
    Dim query As String = "SELECT * FROM vgraph"
    Dim dt As DataTable = GetData(query)

    'Get the DISTINCT statusid.
    Dim chartData As List(Of Object) = New List(Of Object)()
    Dim status As List(Of String) = (From p In dt.AsEnumerable() Select p.Field(Of String)("statusid")).Distinct().ToList()

    'Insert Label for status in First position.
    status.Insert(0, "status")

    'Add the status Array to the Chart Array.
    chartData.Add(status.ToArray())

    'Get the DISTINCT team.
    Dim teams As List(Of String) = (From p In dt.AsEnumerable() Select p.Field(Of String)("team")).Distinct().ToList()

    'Loop through the team.
    For Each team As String In teams
        Dim totals As List(Of Object) = (From p In dt.AsEnumerable() Where p.Field(Of String)("team") = team _
                                         Select p.Field(Of Long)("total")).Cast(Of Object)().ToList()

        totals.Insert(0, team.ToString())
        chartData.Add(totals.ToArray())
    Next
    Return chartData
End Function

Private Shared Function GetData(ByVal query As String) As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
            Dim dt As DataTable = New DataTable()
            sda.Fill(dt)
            Return dt
        End Using
    End Using
End Function

Screenshot