SimonR SimonR - 2 months ago 14
Ajax Question

Google Charts Dashboard, AJAX call fails to load data from SQL DB

Ive been trying to load data from a SQL table thats generated daily via a SP, Table consists of 4 columns.

My Ajax call gets the data and puts into an array -

Array

Heres my code im using to draw the view and pass the array -

var chartData;

$(document).ready(function () {
$.ajax({
url: "/Reporting/LeaveList",
data: "",
dataType: "json",
type: "POST",
contentType: "application/json; chartset=utf-8",
success: function (data) {
//console.log(data);
console.log(typeof data);
chartData = data;
},
error: function () {
alert("Error loading data! Please try again.");
}
}).done(function () {
google.setOnLoadCallback(createTable);

});
});

function createTable() {

var data = new google.visualization.DataTable();


data.addColumn('string', 'Date');
data.addColumn('number', 'AnnualLeave');
data.addColumn('number', 'Sick');
data.addColumn('number', 'Total');
data.addRow(chartData[0])


// Create a dashboard.
var dash_container = document.getElementById('dashboard_div'),
myDashboard = new google.visualization.Dashboard(dash_container);

// Create a date range slider
var myDateSlider = new google.visualization.ControlWrapper({
'controlType': 'ChartRangeFilter',
'containerId': 'control_div',
'options': {
'filterColumnLabel': 'Date'
}
});

// Table visualization
var myTable = new google.visualization.ChartWrapper({
'chartType': 'Table',
'containerId': 'table_div'
});

// Bind myTable to the dashboard, and to the controls
// this will make sure our table is update when our date changes
myDashboard.bind(myDateSlider, myTable);

// Line chart visualization
var myLine = new google.visualization.ChartWrapper({
'chartType': 'LineChart',
'containerId': 'line_div',
});

// Bind myLine to the dashboard, and to the controls

myDashboard.bind(myDateSlider, myLine);

myDashboard.draw(data);


And heres some of the controller -

data = (
from u in db.StaffReportingDay
select new StaffReportingDayVM
{
Date = u.Date.ToString(),
AnnualLeave = u.AnnualLeave,
CompassionateLeave = u.CompassionateLeave,
Sick = u.Sick,
StudyLeave = u.StudyLeave,
Total = u.Total
}).ToList();
}


var ChartOne = new object[data.Count + 1];
ChartOne[0] = new object[]
{
"Date",
"Annual Leave",
"Sick Leave",
"Total on Leave"
};

int j = 0;

foreach(var i in data)
{
j++;
ChartOne[j] = new object[] {i.Date.ToString(), i.AnnualLeave, i.Sick, i.Total };
}


return Json(ChartOne, JsonRequestBehavior.AllowGet);


What i cant get is the array to pull into the view, i just get an error -

Uncaught Error: Type mismatch. Value Annual Leave does not match type number in column index 1

Ive tried many things but would like some pointers and other people to give their insight please.

Answer

Solved with

 function drawVisualization() {
    var data = new google.visualization.DataTable();
    data.addColumn('date', 'Date');
    data.addColumn('number', 'AnnualLeave');
    data.addColumn('number', 'CompassionateLeave');
    data.addColumn('number', 'StudyLeave');
    data.addColumn('number', 'Sick');
    data.addColumn('number', 'Total');
    $.each(chartData, function (i, row) {
        data.addRow([
          (new Date(row.Date)),
          parseFloat(row.AnnualLeave),
          parseFloat(row.CompassionateLeave),
          parseFloat(row.StudyLeave),
          parseFloat(row.Sick),
          parseFloat(row.Total)
        ]);
    });
Comments