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"
        });
    });

Answer 1

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.