Data column(s) for axis #0 cannot be of type string in Google Charts with ASP.NET and SQL Server?

Question

I'm trying to display some data in Google Charts but get this error:

Data column(s) for axis #0 cannot be of type string..

This is my first time to use Google Chart in general and I am trying to use with ASP.NET Webforms and SQL Server. I have a WebMethod that returns the data from the database in a format of an array that is bound with Google Chart.

Here's the code of the script:

<script>
    var chartData; // globar variable for hold chart data
    google.load("visualization", "1", { packages: ["corechart"] });

    // Here We will fill chartData

    $(document).ready(function () {

        $.ajax({
            url: "../../Pages/Test.aspx/GetSentimentData",
            data: "",
            dataType: "json",
            type: "POST",
            contentType: "application/json; chartset=utf-8",
            success: function (data) {
                chartData = data.d;
            },
            error: function () {
                alert("Error loading data! Please try again.");
            }
        }).done(function () {
            // after complete loading data
            google.setOnLoadCallback(drawChart3);
            drawChart3();
        });
    });


    function drawChart3() {
        var data = google.visualization.arrayToDataTable(chartData);

        var options = {
            title: "Distribution of Sentiment Analysis Results",
            hAxis: { title: 'University'},
            vAxis: { title: 'Number of Tweets' },
            pointSize: 5
        };

        var columnChart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        columnChart.draw(data, options);

    }
</script>

So could you please tell me how I can create a Google Column Chart where each item in x-axis will have three series?

see last EDIT in answer here --> Google Bar Chart data preparation — Group by Column — WhiteHat
@WhiteHat, thanks for your help. However, the answer that you referred me to it, has a dummy data not data from database. Actually, I don't know how I can adjust mine based on this reference. So could you please help me based on the code I have? — Tech Lover

WhiteHat WhiteHat · Accepted Answer · 2017-07-05T18:41:25

once the data table is built,
use a data view to create a new column for each unique sentiment label

then aggregate the data view to remove duplicates and total each column

see following snippet...

function drawChart3() {
  var data = google.visualization.arrayToDataTable(chartData);

  // create data view
  var view = new google.visualization.DataView(data);

  // init column arrays
  var aggColumns = [];
  var viewColumns = [0];

  // build view & agg column for each sentiment label
  data.getDistinctValues(1).forEach(function (label, index) {
    // add view column
    viewColumns.push({
      calc: function (dt, row) {
        if (dt.getValue(row, 1) === label) {
          return dt.getValue(row, 2);
        }
        return null;
      },
      label: label,
      type: 'number'
    });

    // add agg column
    aggColumns.push({
      aggregation: google.visualization.data.sum,
      column: index + 1,
      label: label,
      type: 'number'
    });
  });

  // set view columns
  view.setColumns(viewColumns);

  // agg view by university
  var group = google.visualization.data.group(
    view,
    [0],
    aggColumns
  );

  var options = {
    title: "Distribution of Stanford CoreNLP Sentiment Analysis Results by University",
    hAxis: { title: 'University'},
    vAxis: { title: 'Number of Tweets' },
    pointSize: 5
  };

  var columnChart = new google.visualization.ColumnChart(document.getElementById('stanfrod_sentiment_by_uni_chart_div'));
  columnChart.draw(group, options);  // <-- use agg data table
}

note

recommend not using jsapi to load the library, according to release notes...

The version of Google Charts that remains available via the jsapi loader is no longer being updated consistently. Please use the new gstatic loader (loader.js) from now on.

<script src="https://www.gstatic.com/charts/loader.js"></script>

this will also change the load statement to...

google.charts.load('current', {
  callback: drawChart,
  packages: ['corechart']
});

also

you shouldn't be calling setOnLoadCallback more than once

you can depend on google's callback to know when the document is ready

no need for --> $(document).ready

once the callback fires, you can draw as many charts as needed

load google, then use ajax to get data, see setup here --> Javascript ajax get data from C#

Data column(s) for axis #0 cannot be of type string in Google Charts with ASP.NET and SQL Server?

1 Answers