jqGrid dynamic column binding

Question

How to bind jqGrid dynamically?. The columns are not available at design time but will only be available only at runtime.

In the current jqGrid design the colmodels and other properties needs to be pre-populated for the grid to work correctly.

Any input in this direction is greatly appreciated.

Holter

can you share the whole code ? what is the format of result.colData ? – Madagascar

Answer 1

Try this in document.ready:

$.ajax(
    {
       type: "POST",
       url: "SomeUrl/GetColumnsAndData",
       data: "",
       dataType: "json",
       success: function(result)
       {
            colD = result.colData;
            colN = result.colNames;
            colM = result.colModel;

            jQuery("#list").jqGrid({
                jsonReader : {
                    cell: "",
                    id: "0"
                },
                url: 'SomeUrl/Getdata',
                datatype: 'jsonstring',
                mtype: 'POST',
                datastr : colD,
                colNames:colN,
                colModel :colM,
                pager: jQuery('#pager'),
                rowNum: 5,
                rowList: [5, 10, 20, 50],
                viewrecords: true
            })
       },
       error: function(x, e)
       {
            alert(x.readyState + " "+ x.status +" "+ e.msg);   
       }
    });
setTimeout(function() {$("#list").jqGrid('setGridParam',{datatype:'json'}); },50);

this work fine for me.

Amiens

This is a cool trick and this will definitely work.. The only concern here is you need to have two post request to bind this... one for getting the columns and for getting the grid data.. Thanks for your input.. – Holter

no you will always get one post request... the first request is via the ayax cal and you get the data with your columns, in a jsonstring when you navigate trough the data it will use the "someurl/getdata" to get the data... I had the problem also that in the begin i had 2 requests... but with this solution, to get the data first in a jsonstring and later via the url you'll get always one request. – Amiens

Hi Bruno can you please share the entire code,like how to generate the coldata,colName,coleMode in the backend. – Murton

Hi, Rakesh. I don't have the time at the moment to put some sample code online. But just create a function that creates (in json format) the colnames, colmodel and the starting data. If you know everthing at runtime, you can easy modifications to your colmodel per column. – Amiens

With jqGrid 4.3.1 this solution not seems to work. Documentation says: "If you use a jsonstring to obtain the data - after the data is retrieved the datatype option automatically is set to local - i.e. (currently) the paging will not work!" And indeed paging not work. I looked at the jqGrid source code and seems that when datatype is 'jsonstring' or 'local', total records is set to the number of records retrieved. Any suggestions? – Introit

Dear Tommaso, I didnt upgrade my grid so I don't know. I'm not active anymore in JQGrid eather. If i look at my code, i was wondering why i first use jsonstring with the first part of the data, and afterwards switch over to json. (to reduce 2 ajax calls mayby??) What i woul try is: get the model/names via an ayax call, and then create the grid with the model/names and use directly the'json'-datatype. – Amiens

Will it support server side paging ? – Cobden

Can you please provide code to generate the coldata,colName,coleMode in the backend. – Recognizee

Can some one tell me , what will be the code on the controller side for MVC, to make this answer work on view. or any reference to working complete code for Dynamic JQ grid will be much appreciated. Thanks – Cowden

Answer 2

My solution is kind of the same idea as Teoman Shipahi's excellent answer from August 2015.

I have a web service which returns a set of JSON data, but the actual columns could vary over time.

What I wanted to do was hide some of the JSON columns in my jqGrid, and set the widths of some of the columns based on if this particular JSON field was one of the important fields (in this case, SegmentName).

Here's what I came up with:

$(function () {
    //  Load the JSON data we'll need to populate our jqGrid

    // ID of a [Segment_Set] record in our database (which our web service will load the data for.
    var SegmentSetId = 12345;

    $.ajax(
    {
        type: "GET",
        url: "/Service1.svc/LoadSegmentAttributes/" + SegmentSetId,
        dataType: "json",
        success: function (JSONdata) {
            // 
            //  Work through our JSON data, and create the two arrays needed by jqGrid 
            //  to display this dynamic data.
            //
            var listOfColumnModels = [];
            var listOfColumnNames = [];

            for (var prop in JSONdata[0]) {
                if (JSONdata[0].hasOwnProperty(prop)) {
                    //  We have found one property (field) in our JSON data.
                    //  Add a column to the list of Columns which we want our jqGrid to display
                    listOfColumnNames.push(prop);

                    //  How do we want this field to be displayed in our jqGrid ?
                    var bHidden = (prop == "SegmentID") || (prop == "SegmentSequenceInx");
                    var columnWidth = (prop == "SegmentName") ? 200 : 50;

                    listOfColumnModels.push({
                        name: prop,
                        width: columnWidth,
                        sortable: true,
                        hidden: bHidden
                    });
                }
            }

            //  Now we have our JSON data, and list of Column Headings and Models, we can create our jqGrid.
            CreateJQGrid(JSONdata, listOfColumnModels, listOfColumnNames);
        }
    });
});

And here's the function which creates the jqGrid:

function CreateJQGrid(JSONdata, listOfColumnModels, listOfColumnNames) {
    //  After loading the JSON data from our webservice, and establishing the list of 
    //  Column Names & Models, we can call this function to create the jqGrid.
    $("#SegmentRulesGrid").jqGrid({

        datatype: "local",
        data: JSONdata,
        localReader: {
            id: "SegmentID",        //  The Primary Key field in our JSONdata 
            repeatitems: false
        },
        mtype: "GET",
        colNames: listOfColumnNames,
        colModel: listOfColumnModels,
        rowNum: 15,
        loadonce: true,
        gridview: true,
        autowidth: true,
        height: 350,
        pager: '#pager',
        rowList: [15, 30, 100, 300],
        rownumbers: true,
        viewrecords: true,
        caption: 'Segment Rules',
    });
}

Hope this helps.

Obviously one downside to my solution is that it insists that you load all of your JSON data before displaying it in a grid, rather than loading just one page of the data at a time. This could be a problem if you have a huge amount of data.

Chishima

Answer 3

If somebody wants to implement this functionality using mvc then http://blog.lieberlieber.com/2010/07/07/asp-net-mvc-and-a-generic-jqquery-grid-jqtgrid/ is a nicer solution.

Puerperal

Answer 4

Is it feasible to recreate the grid each time a column is added? You could store the data locally and just Unload / Recreate the grid each time, using a dynamic column model.

You may also want to look at some of the demos that show/hide columns dynamically. Depending upon how many columns you have, you might be able to use the same concept in your application.

Does that help?

Aphoristic

Answer 5

function columnsData(Data) {
    var str = "[";
    for (var i = 0; i < Data.length; i++) {
        str = str + "{name:'" + Data[i] + "', index:'" + Data[i] + "', editable: true}";
        if (i != Data.length - 1) {
            str = str + ",";
        }
    }
    str = str + "]";
    return str;
}

Maomaoism

Answer 6

Yet another solution;

 $("#datagrid").jqGrid({
        //url: "user.json",
        //datatype: "json",
        datatype: "local",
        data: dataArray,
        colNames:getColNames(dataArray[0]),
        colModel:getColModels(dataArray[0]),
        rowNum:100,
        loadonce: true,
        pager: '#navGrid',
        sortname: 'SongId',
        sortorder: "asc",
        height: "auto", //210,
        width:"auto",
        viewrecords: true,
        caption:"JQ GRID"
    });

    function getColNames(data) {
        var keys = [];
        for(var key in data) {
            if (data.hasOwnProperty(key)) {
                keys.push(key);
            }
        }

        return keys;
    }

    function  getColModels(data) {
        var colNames= getColNames(data);
        var colModelsArray = [];
        for (var i = 0; i < colNames.length; i++) {
            var str;
            if (i === 0) {
                str = {
                    name: colNames[i],
                    index:colNames[i],
                    key:true,
                    editable:true
                };
            } else {
                str = {
                    name: colNames[i],
                    index:colNames[i],
                    editable:true
                };
            }
            colModelsArray.push(str);
        }

        return colModelsArray;
    }

Fluent

Answer 7

I've tried solution suggested by bruno both with json and jsonstring type of data return, it works BUT
if option datastr : colD
  exists - further requests for data do not execute, though filter does work on first retrieved data
  do not exist - double request for data on grid loading

Isocrates

Answer 8

I would suggest to execute $("#list").jqGrid('setGridParam',{datatype:'json'}); on loadComplete event of the grid - this way the grid will exist for sure. So, just add the following to the grid definition instead of setTimeout(...) :

loadComplete : function () {
    $ ("#list").jqGrid('setGridParam',{datatype:'json'});
}

Worked for me !

Jingo

Answer 9

If you do it with the import feature, you can still utilize the paging features of jqGrid. Make sure "GetColumnsAndData" returns normal grid data as "data" and the configuration as "grid" (or change these values in "jsonGrid").

EDIT: Also make sure one of the "grid" settings returned is "url" (with a URL value to retrieve only data).

$('#grid').jqGridImport({
    imptype: 'json',
    impurl: 'SomeUrl/GetColumnsAndData',
    mtype: 'POST',
    impData: {
        '_search': 'false',
        'sidx': 'loc_short_name',
        'sord': 'asc',
        'page': '1',
        'rows': '25',
        'searchField': '',
        'searchOper': '',
        'searchString': ''
        // Add any additional, custom criteria
    },
    jsonGrid: {
        config: 'grid',
        data: 'data'
    }
});

Viridi

Answer 10

**Dynamic JQGrid From Data Table**
$(document).ready(function () {
        var ColN, ColM, ColD, capEN;
        var sPath = window.location.pathname;
        var sPage = sPath.substring(sPath.lastIndexOf('/') + 1);
        //alert(sPage);
        $.ajax({
            url: sPage+'?method=getGridHeadData',
            type: "POST",
            contentType: "application/json; charset=utf-8",
            data: {},
            dataType: "json",
            success: function (data, st) {
                if (st == "success") {
                    ColN = data.rowsHead;//jqgrid heading data
                    ColM = data.rowsM; // its column model
                    ColD = data.rows; // Data
                    createGrid();
                }
            },
            error: function () {
                alert("Error with AJAX callback");
            }
        });

        function createGrid() {
            jQuery("#AccountingCodesGrid").jqGrid({

                datatype: 'json',
                url: sPage+'?method=getGridData',
                mtype: 'POST',
                ajaxGridOptions: { contentType: 'application/json; charset=utf-8' },
                serializeGridData: function (postData) {
                    return JSON.stringify(postData);
                },
                jsonReader: { repeatitems: false, root: "rows", page: "page", total: "total", records: "records" },

                //data: ColD,
                colNames: ColN,
                colModel: ColM,
                loadonce: true,
                pager: jQuery('#pager'),
                rowNum: 5,
                rowList: [5, 10, 20, 50],
                viewrecords: true
            })


        }
        jQuery("#AccountingCodesGrid").jqGrid('navGrid', '#Pager', { edit: false, add: false, del: false }, null, null, true, { multipleSearch: true });
        var height = $(window).height();


    });

the code behind
 **In page load..................................................................**

if (Request.QueryString["method"] == "getGridData")
            {
                Request.InputStream.Position = 0;
                StreamReader ipStRdr = new StreamReader(Request.InputStream);
                string json = ipStRdr.ReadToEnd();
                JavaScriptSerializer jser = new JavaScriptSerializer();
                Dictionary<string,> dict = jser.Deserialize<dictionary><string,>>(json);

                getGridData(int.Parse(dict["page"].ToString()), int.Parse(dict["rows"].ToString()), bool.Parse(dict["_search"].ToString()), dict["sord"].ToString());
                Response.End();
            }
            else if (Request.QueryString["method"] == "getGridHeadData")
            {
                getGridHeadData();
                Response.End();
            }

**Method to get data in json form----------------------------------------------------**
public void getGridData(int page, int rows, bool _search, string sord)
        {
            DataTable dtRecords = dtSource;// Data Table
            int recordsCount = dtRecords.Rows.Count;

            JqGridData objJqGrid = new JqGridData();
            objJqGrid.page = page;
            objJqGrid.total = ((recordsCount + rows - 1) / rows);
            objJqGrid.records = recordsCount;
            objJqGrid.rows = ConvertDT(dtRecords);

            List<string> liob = new List<string>();
            foreach (DataColumn column in dtRecords.Columns)
            {
                liob.Add(column.ColumnName);
            }
            objJqGrid.rowsHead = liob;

            List<object> colcontetn = new List<object>();
            foreach (var item in liob)
            {
                JqGridDataHeading obj = new JqGridDataHeading();
                obj.name = item.ToString();
                obj.index = item.ToString();
                colcontetn.Add(obj);
            }
            objJqGrid.rowsM = colcontetn;

            JavaScriptSerializer jser = new JavaScriptSerializer();
            Response.Write(jser.Serialize(objJqGrid));

        }

Alika

Answer 11

try this on

$.ajax(
    {
       type: "POST",
       url: "SomeUrl/GetColumnsAndData",
       data: "",
       dataType: "json",
       success: function(result)
       {
            colD = result.colData;
            colN = result.colNames;
            colM = result.colModel;

            jQuery("#list").jqGrid({
                jsonReader : {
                    cell: "",
                    id: "0"
                },
                url: 'SomeUrl/Getdata',
                datatype: 'jsonstring',
                mtype: 'POST',
                datastr : colD,
                colNames:colN,
                colModel :colM,
                pager: jQuery('#pager'),
                rowNum: 5,
                rowList: [5, 10, 20, 50],
                viewrecords: true
            })
       },
       error: function(x, e)
       {
            alert(x.readyState + " "+ x.status +" "+ e.msg);   
       }
    });
setTimeout(function() {$("#list").jqGrid('setGridParam',{datatype:'json'}); },50);

Saltcellar