I have been reading a wide variety of posts and documentation about this issue, and I have tried everything I have read and can think of - still stumped.
I'm trying to load JSON data from the server into a jqGrid in a Dot Net Nuke website. Using DNN is not my choice, so there is no option to not use it. That being said, I've set up a standard DNN website and added a C# class library project (named ApiLibrary) to it. In it, there are two classes:
RouteMapper.cs
using DotNetNuke.Web.Api;
namespace ApiLibrary
{
public class RouteMapper : IServiceRouteMapper
{
public void RegisterRoutes(IMapRoute mapRouteManager)
{
mapRouteManager.MapHttpRoute("ApiLibrary", "default", "{controller}/{action}", new[] { "ApiLibrary" });
}
}
}
and WelcomeController.cs which contains my AJAX functions. The one that I am using for the jqGrid is the following:
[WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json, RequestFormat = WebMessageFormat.Json)]
public string GetMyData(string sidx, string sord, int page, int rows)
{
var myList = GetTransportTable(); // returns List<TransportInfo>
var rowlist = new List<Row>() { };
int m = 0;
for (var i = 0; i < myList.Count; i++)
{
m = i + 1;
Row rowobj = new Row();
var stringList = new List<string>();
rowobj.id = m.ToString();
stringList.Add(myList[i].Pilot);
stringList.Add(myList[i].Vessel);
stringList.Add(myList[i].Dock);
stringList.Add(myList[i].Amount.ToString("c"));
rowobj.cell = stringList;
rowlist.Add(rowobj);
}
var jsonToReturn = new
{
total = 3,
page = 1,
records = myList.Count.ToString(),
rows = rowlist.ToArray()
};
string jData = string.Empty;
jData = JsonConvert.SerializeObject(jsonToReturn);
return jData;
}
This returns 100% valid JSON (verified on jslint) that is in the format that jqGrid wants - i.e:
{ "total": "4", "page": "1", "records": "4", "rows" : [ { "id": "1", "cell":["Myname", "Myboat", "Mydock", "144"] }, { "id": "2", "cell":["Myname1", "Myboat1", "Mydock1", "1414"] } ] }
For each event listed in the jqGrid documentation if do an alert() to display the data during that event then it displays my perfectly formatted JSON. This is telling me that jqGrid is receiving the data just fine, but something is blocking it from displaying the data.
I've tried using jsonReader, jsonmap, and pretty much every other suggestion/option that I've come across.
I have checked and doublechecked my javascript and css references and those are good to go. Here is my Javascript for loading the grid:
$("#reviewList").jqGrid({
ajaxGridOptions: { contentType: 'application/json; charset=utf-8' },
url: 'DesktopModules/ApiLibrary/API/Welcome/GetMyData',
jsonReader: {
repeatitems: false,
id: "id",
root: "rows",
page: "page",
total: "total",
records: "records",
cell: "cell"
},
colNames: ['Pilot', 'Vessel', 'Dock', 'Amount'],
colModel: [
{ name: 'Pilot', width: 250, align: 'center' },
{ name: 'Vessel', width: 250, align: 'center' },
{ name: 'Dock', width: 175, align: 'center' },
{ name: 'Amount', width: 110, align: 'center' }
],
rowNum: 10,
rowList: [10, 20, 30],
pager: "#pager2",
viewrecords: true,
sortname: 'Pilot',
sortorder: 'asc',
caption: 'Transport List Overview'
}).navGrid("#pager2", { edit: false, add: false, del: false });
It's worth noting that I have used several other AJAX calls with jQuery (both GET and POST) on the same page and have had no issues with parsing any JSON. Also, with that JSON (from jQuery AJAX calls outside of jqGrid) I can display it in jqGrid. However, since it is local data, I can't sort/page/search it - that's no good for me.
What is going wrong?
I looked a bit deeper into the DNN Services Framework, and I was able to get this working by modifying my AJAX method in WelcomController.cs to be the following:
[System.Web.Http.HttpPost]
public HttpResponseMessage GetMyData()
{
var myList = GetTransportTable(); //returns List<TransportInfo>
var rowlist = new List<Row>() { };
for (var i = 0; i < myList.Count; i++)
{
Row rowobj = new Row();
var stringList = new List<string>();
rowobj.id = i;
stringList.Add(myList[i].DockDate.ToString("d"));
stringList.Add(myList[i].Pilot);
stringList.Add(myList[i].Vessel);
stringList.Add(myList[i].Dock);
stringList.Add(myList[i].Amount.ToString("c"));
rowobj.cell = stringList;
rowlist.Add(rowobj);
}
var jsonToReturn = new
{
total = 1,
page = 1,
records = myList.Count.ToString(),
rows = rowlist
};
return Request.CreateResponse(HttpStatusCode.OK, jsonToReturn);
}
and in my javascript I removed the jsonReader and added mtype:'POST'. The new jqGrid function is:
$("#reviewList").jqGrid({
loadError: function(xhr, status, error) {
alert('load error: ' + error);
},
mtype: 'POST',
ajaxGridOptions: { contentType: 'application/json; charset=utf-8' },
url: 'DesktopModules/ApiLibrary/API/Welcome/GetMyData',
datatype: "json",
colNames: ['Date','Pilot', 'Vessel', 'Dock', 'Amount'],
colModel: [
{name: 'DockDate', index:'DockDate', width: 90, align: 'center'},
{ name: 'Pilot', index: 'Pilot', width: 250, sortable: true, align: 'center' },
{ name: 'Vessel', index: 'Vessel', width: 250, sortable: true, align: 'center' },
{ name: 'Dock', index: 'Dock', width: 175, sortable: true, align: 'center' },
{ name: 'Amount', index: 'Amount', width: 110, sortable: true, align: 'center', sorttype: 'float' }
],
rownumbers: true,
rowNum: 10,
rowList: [10, 20, 30],
pager: "#pager2",
viewrecords: true,
caption: 'Transport List Overview',
height: "auto"
//loadonce: true
}).navGrid("#pager2", { edit: false, add: false, del: false });