Get data from JSON to jqGrid

Question

I'm trying to get data from:

 jQuery(document).ready(function() {

 var mydata;
        $.getJSON('@Url.Action("GetJsonData", "Home")', function(data) {

            datatype: 'json',
                mydata = data;
            // alert(mydata);

        });

To my jqGrid:

        $("#grid").jqGrid({
            datastr: data,
            datatype: 'json',
            width: '100%',
            colNames: ["Seq ID", "Fund ID", "Name", "Fund", "Bonus", "Allocation", "Blank", "Begin", "End"],
            colModel: [
                {
                    name: 'seqid',
                    index: 'seqid',
                    editable: true,
                }, {
                    name: 'fund id',
                    index: 'fund id',
                    editable: true,
                }, {
                    name: 'name',
                    index: 'name',
                    editable: true,
                }, {
                    name: 'fund',
                    index: 'fund',
                    editable: true,
                }, {
                    name: 'bonus',
                    index: 'bonus',
                    editable: true,
                }, {
                    name: 'allocation',
                    index: 'allocation',
                    editable: true,
                }, {
                    name: 'blank',
                    index: 'blank',
                    editable: true,
                }, {
                    name: 'begin',
                    index: 'begin',
                    editable: true,
                }, {
                    name: 'end',
                    index: 'end',
                    editable: true,
                }
            ],


            pager: '#pager',
            'cellEdit': true,
            'cellsubmit': 'clientArray',
            editurl: 'clientArray'
        });

Data looks like:

{
    "FUND_SEQ_ID": 1.0,
    "FUND_ID": 23,
    "FUND_NM": "INSTITUTIONAL",
    "FUND_TICKER_NM": "TINXX",
    "FUND_SALARY_IND": "A",
    "FUND_BONUS_IND": "N",
    "FUND_ALCTN_IND": "\u0000",    <- This should be null
    "BEG_DT": "20140101",
    "END_DT": "24000101"
  },

I tried: datatype: jsonstring, datastr: data, data: data.. all give me nothing or p.colModel is null or not an object.

The data in the getJSON method is there. Just not sure how to pass it.

Update: Here's how I got it to work using a DataTable in MVC 4 Razor.

In HomeController.cs I have a method:

    public string GetAssociateFromDb()
    {
        DataTable dt = new DataTable();
        string jsonData;
        string connString = ConfigurationManager.ConnectionStrings["DEFCOMP"].ConnectionString;
        using (SqlConnection connection = new SqlConnection())
        {
            connection.ConnectionString = connString;
            using (var cmd = new SqlCommand("SELECT * FROM FUND", connection))
            {
                connection.Open();
                SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
                myAdapter.Fill(dt);
                return JsonConvert.SerializeObject(dt); // Converted to JSON string
            }
        }

    }

In my view (Index.cshtml), I call that method in the url of jQGrid.

    $(document).ready(function() {


            jQuery("#grid").jqGrid({
                url: '@Url.Action("GetAssociateFromDb", "Home")',
                datatype: "json",
                width: '100%',
                colNames: ["Seq ID", "Fund ID", "Name", "Fund", "Salary", "Bonus", "Allocation", "Begin", "End"],
                colModel: [
                    { name: "FUND_SEQ_ID" },
                    { name: "FUND_ID" },
                    { name: "FUND_NM" },
                    { name: "FUND_TICKER_NM" },
                    { name: "FUND_SALARY_IND" },
                    { name: "FUND_BONUS_IND" },
                    { name: "FUND_ALCTN_IND" },
                    { name: "BEG_DT" },
                    { name: "END_DT" }
                ],
                cmTemplate: { editable: true },
            //    data: JSON.parse(data),  // Load Data
                rowNum: 10,        // Total records to show at a time by default
                loadonce: true,       
                rowList: [10, 20],  // For Paging
                pager: '#pager',
                jsonReader: {
                    repeatitems: false,
                    page: function () { return 1; },  // This was necessary.
                    root: function (obj) { return obj; },
                    records: function (obj) { return obj.length; }
                },
                viewrecords: true,
                gridview: true,
                autowidth: true,
                height: 'auto',
                hoverrows: true,
                caption: "List of Funds"
        });
    });

Solution

The main problem which I see is the naming of columns not the same as in the input JSON data. Try to replace colModel to the following:

colModel: [
    { name: "FUND_SEQ_ID" },
    { name: "FUND_ID" },
    { name: "FUND_NM" },
    { name: "FUND_TICKER_NM" },
    { name: "FUND_SALARY_IND" },
    { name: "FUND_BONUS_IND" },
    { name: "FUND_ALCTN_IND" },
    { name: "BEG_DT" },
    { name: "END_DT" }
],
cmTemplate: {editable: true},

The option cmTemplate allows you to set common properties in colModel. I recommend you also use gridview: true and autoencode: true in all your grids.

Additionally you can use

url: '@Url.Action("GetJsonData", "Home")',
datatype: "json",
loadonce: true

instead of usage of $.getJSON.

Comments

[Oleg]

The data which you included in the question are not an array on items. Do you really try to use jqGrid in the way or you just included not full response from the server? Why you don't use url: '@Url.Action("GetJsonData", "Home")' and datatype: "json"? it's the most standard way of filling of the grid data.

[Mark C.]

Thanks for the comment @Oleg. I did try that, but the grid is still empty.

[Mark C.]

The data is actually an array. It looks like the example provided, but 10 additional records wrapped in [ ].

[Mark C.]

View my updated code here. It's making IE crash. Do you see anything wrong? I did as you said, I removed the url: and changed datatype to local

[lucasdc]

What do you mean by "It's making IE crash"? What's the error?

[Mark C.]

"Stop running this script? A script on this page is causing your web browser to run slowly, if it continues to run, your computer may become unresponsive."

[Mark C.]

It loads the table, but with no data. I've updated my fiddle

[Mark C.]

Well I would like for it to be dynamic (from database / live feed), that's why I'm not using static data. I did try the datastr method but it did not work for me, unfortunately. The grid loaded with no data.

[ItayB]

who talked about static data? of course you want it to be dynamically changed.. so why not to use the 'url' and get from your server the above format? (like the simple demo examples). what do I miss?

[Mark C.]

I'm not sure how to do what you are saying. Sorry.

[Mark C.]

Thank you for your answer. I did exactly as you said..but still no data? No errors or anything, just no data.

[Oleg]

@MurDeR: You are welcome! I suppose that you made some error in your code. Look at the demo and another one. The first demo uses datatype: "jsonstring" and the second uses datatype: "json" and loads the data from the server. Both the demos works. You should compare the code of the demos with your code and you should find your error.

[Mark C.]

thanks again for your demo. I have tried your way and it works. I think my problem is $("#grid").jqGrid({ url: '@Url.Action("GetJsonData", "Home")', datatype: "json", loadonce: true, ... It sucks I can't use alert(@Url.Action.....);

[Mark C.]

can we continue this discussion somewhere else?

[Oleg]

@MurDeR: we are in different time zones. What is your current problem? What you mean with alert? You can use beforeProcessing and loadComplete callbacks (or jqGridAfterGridComplete) to do some actions after response from the server have received. The callback beforeProcessing allows you even modify the server response before it will be processed by jqGrid. The callback beforeProcessing will be called after the response have been proceed and the grid have been filled.

[Mark C.]

Thank you for the further explanation! I am having a hard time understanding why my data from my controller isn't getting to the jqGrid call? I have literally searched for hours, and while I don't anticipate you to be a personal debugging service, I feel as though you would be able to see my mistake in ~5 seconds, compared to me who has no idea where the error/bug is.

[Oleg]

@MurDeR: Do you debugged the code? You can define both beforeProcessing and loadComplete callbacks and set breakpoints inside of callbacks. You can verify that the first parameter of beforeProcessing callback (data) contains the data which you expected from the server. Do you validated (set breakpoint) that GetJsonData action are called?

[Mark C.]

It DOES hit GetJsonData and it DOES hit url: '@Url.Action("GetJsonData", "Home")',.. but that's it. It does not load anything in jqGrid after that...If you want a visualization of my JavaScript, here it is.

[Oleg]

@MurDeR: Will be called beforeProcessing?

[Mark C.]

No, should I put that above url:...?

[Oleg]

@MurDeR: You should include loadError callback in the case (see the answer for more details). Probably you return wrong formatted data from '@Url.Action("GetJsonData", "Home")'. I recommend you to trace HTTP traffic with respect of Developer Tools of IE (press F12 to start, choose Network and start the trace) or even better with Fiddler.

[Mark C.]

In my controller, the return for public JsonResult GetJsonData() looks like...[{"FUND_SEQ_ID":1,"FUND_ID":23,"FUND_NM":"INSTITUTION‌​AL MONEY MARKET FUND","FUND_TICKER_NM":"TINXX","FUND_SALARY_IND":"A","FUND_B‌​ONUS_IND":"N","FUND_‌​ALCTN_IND":"\u0000",‌​"BEG_DT":"20140101",‌​"END_DT":"24000101"}‌​,{"FUND_SEQ_ID":2,"F‌​UND_ID":30,"FUND_NM"‌​:"LARGE CAP GROWTH FUND CLASS"... etc. Do I need to format differently? I will also work on what you directed me to do. I have done 0 java script debugging before, so sorry for my inexperience.

[Oleg]

@MurDeR: Do you include loadError callback? Is it the data which you get from HTTP trace? The problem could be that the data will be placed inside of HTML page or the server could generate wrong HTTP headers (for example wrong Content-Type etc)...

[Mark C.]

Yes, I included the loadError from the link you suggested. It never gets hit

[Oleg]

@MurDeR: It's very strange! Either beforeProcessing or loadError will be called after jqGrid receive response from the server. Do you made HTTP trace? Do you have the demo online?

[Mark C.]

No, but I can supply source code and results if that would help you? I am close but I just don't know where I am going wrong.

[Oleg]

@MurDeR: If you supply source code of your demo I could take a look in it tomorrow.

[Mark C.]

Here is the source code with some notes JSFiddle

[Oleg]

@MurDeR: You have to replace return serializer.Serialize(rows); to return rows; in GetAssociateFromDB. The problem is that return Json(jsonData, JsonRequestBehavior.AllowGet); makes JSON serialization. If you serialize object than the deserialized data will be also object on the client side. If you sertilize string (like you do currently) then the string will be deserialized as the string too on the client side and processing will failed. So the current error is in the server code. You will see \" instead of " in the server response.

[Mark C.]

I'm sorry I'm just getting back to you - I was out of town. Is there a way I could have known that this was the issue? Do you know of an example I could follow? I get an error because it "Cannot convert var rows = new List<Dictionary<string, object>>(); to 'string' "

[Oleg]

@MurDeR: I'm not sure which problem you have currently. You can just use anonymous data type: new {FUND_SEQ_ID: 1.0, FUND_ID: 23,FUND_NM: "INSTITUTIONAL MONEY MARKET FUND",...}. You can use List<suchObjects> (or List<Object>).

[Mark C.]

thanks for replying! So, I am confused. Do you know of a tutorial for what I am trying to do? I am not sure how to get the data from the datatable to JSON format. I feel like this should be easy but it's been nothing but frustration. Thanks for your help, by the way.

[Oleg]

@MurDeR: Look at the code of the old answer for example. One can simplify the code if the size of data is not large (less as 10000 rows for example). One would can use loadonce: true option on the client side, one would need return all data and the paging, sorting and filtering of the data will be done on the client side by jqGrid.

[Mark C.]

Thank you for the resource. That example is a little much for my experience. I will try to keep looking for an example with DataTable that I can understand. I will stop annoying you haha thank you for your time and patience,

[Mark C.]

This question is really old man. There's no need to add your answer

[Aqib]

it took me a day to solve this query. old or new this can help anyone.