JqGrid SubGrid with Entity framework MVC

Question

I have two tables (Parent/Child) that i want to display using the subgrid functionality in Jqgrid. the Main Grid Populates the data from my datamodel (parent table) but when the subgrid is expanded the data from the child table is not populated. have a look at my code, where am i going wrong?

Jquery

$(function () {
    $("#grid").jqGrid({
        url: "./EditManifest/GetTodoLists",
        datatype: 'json',
        mtype: 'Get',
        colNames: ['ID', 'BL', 'CARRIER BL', 'CARRIER', 'DESTINATION', 'TIN', 'TELEPHONE', 'ADRESS',],
        colModel: [
            { key: true, name: 'ID', index: 'ID', editable: false },
            { key: true, name: 'BL', index: 'BL', editable: false, sortable: true },
            { key: false, name: 'CARRIER_BL', index: 'CARRIER_BL', editable: true, edittype: 'text' },
            { key: false, name: 'CARRIER', index: 'CARRIER', editable: true, edittype: 'text' },
            { key: false, name: 'DESTINATION', index: 'DESTINATION', editable: true, edittype: 'text' },
            { key: false, name: 'TIN', index: 'TIN', editable: true, edittype: 'text' },
            { key: false, name: 'TELEPHONE', index: 'TELEPHONE', editable: true, edittype: 'text' },
            { key: false, name: 'ADRESS', index: 'ADRESS', editable: true, edittype: 'text' },
           ],
        pager: jQuery('#pager'),
        rowNum: 10,
        rowList: [10, 20, 30, 40],
        height: '100%',
        viewrecords: true,
        multiselect: false,
        subGrid: true,
        subGridUrl: "./EditManifest/subgrid",
        subGridModel: [{
            colNames: ['BL', 'Container', 'Size', 'Type', 'TotalWeight', 'HS Code', 'ATA'],
            colModel: [{ key: true, name: "BL", index: "BL", width: 80 },
                        { key: true, name: "ContianerNumber", index: "ContianerNumber", width: 130 },
                        { name: "Contianersize", index: "Contianersize", width: 70 },
                        { name: "ContianerType", index: "ContianerType", width: 70 },
                        { name: "TOTALWEIGHT", index: "TOTALWEIGHT", width: 70 },
                        { name: "hscode", index: "hscode", width: 130 },
                        { name: "ArrivalDate", index: "ArrivalDate", width: 70 }]
        }
        ],
        caption: 'Edit Manifest Data',
        emptyrecords: 'No records to display',
        jsonReader: {
            root: "rows",
            page: "page",
            total: "total",
            records: "records",
            repeatitems: false,
            Id: "0"
        },
        autowidth: true,
        multiselect: false

    }).navGrid('#pager', { edit: true, add: false, del: false, search: false, refresh: true },
        {
            // edit options
            zIndex: 100,
            url: './EditManifest/Edit',
            closeOnEscape: true,
            closeAfterEdit: true,
            recreateForm: true,
            afterComplete: function (response) {
                if (response.responseText) {
                    alert(response.responseText);
                }
            }
        });
});

controller

  public class EditManifestController : Controller
    {
        public StatusFollowUPEntities db = new StatusFollowUPEntities();
        public ActionResult Index()
        {
            return View();
        } 
    public JsonResult GetTodoLists(string sidx, string sord, int page, int rows)  
            {
                int pageIndex = Convert.ToInt32(page) - 1;
                int pageSize = rows;
                var todoListsResults = db.tblBLs.Select(
                        a => new
                        {
                            a.ID,
                            a.BL,
                            a.CARRIER_BL,
                            a.CARRIER,
                            a.DESTINATION,
                            a.SAILINGDATE,
                            a.TIN,
                            a.TELEPHONE,
                            a.ADRESS,

                        });
                int totalRecords = todoListsResults.Count();
                var totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows);
                if (sord.ToUpper() == "DESC")
                {
                    todoListsResults = todoListsResults.OrderByDescending(s => s.BL);
                    todoListsResults = todoListsResults.Skip(pageIndex * pageSize).Take(pageSize);
                }
                else
                {
                    todoListsResults = todoListsResults.OrderBy(s => s.BL);
                    todoListsResults = todoListsResults.Skip(pageIndex * pageSize).Take(pageSize);
                }
                var jsonData = new
                {
                    total = totalPages,
                    page,
                    records = totalRecords,
                    rows = todoListsResults
                };
                return Json(jsonData, JsonRequestBehavior.AllowGet);
            }
    public JsonResult subgrid(string id)
            {

                var ctrDetails = db.tblCtrDetails.Where(a => a.BL == id).Select(
                        a => new
                        {

                            a.BL,
                            a.ContianerNumber,
                            a.Contianersize,
                            a.ContianerType,
                            a.TOTALWEIGHT,
                            a.hscode,
                            a.ArrivalDate,


                        });

                //Returning json data
                return Json(ctrDetails);
            }
}

View

<div>
    <table id="grid"></table>
    <div id="pager"></div>
</div>
<script src="@Url.Content("~/Scripts/.....

score 1 · Accepted Answer · answered Oct 07 '15 at 20:14

There are couple issues in your frontend code and backend code.

The subGridModel option works a little bit different than root jqGrid - there is no colNames or colModel, you should be using name and mapping.

...
subGridModel: [{
    name: [ 'BL', 'Container', 'Size', 'Type', 'TotalWeight', 'HS Code', 'ATA' ],
    mapping: [ 'BL', 'ContianerNumber', 'Contianersize', 'ContianerType', 'TOTALWEIGHT', 'hscode', 'ArrivalDate' ]
}],
...

Also in order for mapping to have effect you need to adjust jsonReader settings.

...
jsonReader: {
    root: 'rows',
    page: 'page',
    total: 'total',
    records: 'records',
    repeatitems: false,
    Id: '0',
    subgrid: {
        root: 'rows',
        repeatitems: false
    }
},
...

Now your action needs to return data in format matching the settings defined in jsonReader.

public JsonResult subgrid(string id)
{
    var subGridData = new
    {
        rows = db.tblCtrDetails.Where(a => a.BL == id).Select(a => new
        {
            a.BL,
            a.ContianerNumber,
            a.Contianersize,
            a.ContianerType,
            a.TOTALWEIGHT,
            a.hscode,
            a.ArrivalDate
        })
    };

    return Json(subGridData, JsonRequestBehavior.AllowGet);
}

This should do the trick.

thanks for the response tom, you really are awesome. i made the changes you pointed out but the sub grid is still not expanding, when i press on the expand(+) all i get is a loading msg. does it have anything to do with the foreign key field connecting the subgrid with the main? — Benion, Oct 08 '15 at 08:49
@Benion Can you use FireBug Or Devtools to grab request/response when you are expanding the subgrid? — tpeczek, Oct 08 '15 at 16:26

JqGrid SubGrid with Entity framework MVC

1 Answers1