jqGrid - Unique ID for new row

Question

I am trying to asign a unique ID to the new rows i add to my jqGrid. I use free jqGrid 4.11.0, a java rest service for the server side and MS SQL for databases.

inlineEditing: {

    ajaxSaveOptions: { contentType: "application/json" },
    serializeSaveData: function (postData) {

        var idArray = grid.getCol('id');
        var count = 0;
        var k = 1;

            while(postData.id.search('jqg') != '-1'){

                for(var i =0 ; i<idArray.length;i++){

                    if(k == idArray[i]){
                        count++;
                    }
                    if(count == 0){

                        postData.id = k  ;
                        break;                              
                    }
                }                       
                k++;
            }               
        return JSON.stringify(postData);
    }
}

I noticed the default ID given by jqGrid is jqg + a number. Any suggestions on how i could make a unique ID thats not currently in use on any of the database records? Should i do this from the server side or it can be made in the jqGrid code?

At some point in the evening, this code was kind of working and kept on giving id 7 to all new rows [even though it should have kept on increasing]. After some changes[cant remember what changes], every time i click save to submit the new row to the server, the page freezes and chrome suggest i should kill the process.

Please let me know what and if i should add more information.

Edit1 :

$(function () {

var grid = $('#grid'),
    checkboxEditOptionEvents = [

        {type : 'click', data: {'Yes': 'Yes'}, fn: function (e, id) {
            var checkboxCol = grid.getCol('sefDepartament'),
                ids = grid.jqGrid('getDataIDs'),
                numberOfCheckedBoxes = [],
                k;

            for (k = 0; k < ids.length; k++) {
                    if(checkboxCol[k] == 'Yes'){
                        numberOfCheckedBoxes.push(checkboxCol[k]);
                        if (numberOfCheckedBoxes.length == 1){
                            alert('Please deselect the other checked box first');
                            $(this).prop("checked",false);
                            numberOfCheckedBoxes = 0;
                        }
                    }
        }
    }}];

var experienceFunction = function(cellvalue, options ,rowObject){

            var joinYearVar =  rowObject.joinYear,
             YY = joinYearVar.slice(0,4),
             MM = joinYearVar.slice(5,7),
             DD= joinYearVar.slice(8,11);

            return moment("\"" +YY+MM+DD+"\"" , "YYYYMMDD").fromNow();

};

var checkboxFormatFunc = function(cellvalue, options ,rowObject){
    if(cellvalue == 'Yes'){
        return 'Yes';
    }
    return 'No';
};

var  afterSaveFunction =   function(id){

            var prenumeVar = grid.getCell(id, 'prenume').trim(),
                numeVar = grid.getCell(id,'nume').trim(),
                usernameVar =  numeVar +'.'+ prenumeVar,
                emailVar = usernameVar + '@test.com';
            usernameVar =usernameVar.replace(/\s/g , '').trim();
            emailVar = emailVar.replace(/\s/g , '');

            grid.setCell(id, 'username', usernameVar);
            grid.setCell(id, 'email', emailVar);

};

var colModelSettings = [

    {name:'id', label:'id',key: true,hidden: true, width:10,sorttype:'number',editable: false},     
    {name:'nume',label:'Nume',width:90, align: 'center',editable:true,searchoptions: {sopt: ['eq','bw','ew','cn']}, editrules:{required:true}, editoptions: {defaultValue: ' '},formatter: 'text'},
    {name:'prenume',label:'Prenume',width:100,editable:true,searchoptions: {sopt: ['eq','bw','ew','cn']},align: 'center',editrules:{required:true},editoptions: {defaultValue: ' '},formatter: 'text'},
    {name:'username',label:'Username',searchoptions: {sopt: ['eq','bw','ew','cn']},width:125,align: 'center'  },
    {name:'email',label:'Email',width:135,searchoptions: {sopt: ['eq','bw','ew','cn']},align: 'center'},
    {name:'sefDepartament',label:'Sef Departament',width:90,editable:true,align: 'center', stype:"select", searchoptions:{sopt: ['eq','ne'],value: "Yes:Yes;No:No"},formatter: checkboxFormatFunc,edittype:'checkbox',editoptions: { dataEvents: checkboxEditOptionEvents,value:'Yes:No', defaultValue: 'No' }},
    {name:'position',label:'Position',editable:true,stype: 'select',formatter: 'select',searchoptions: {sopt: ['eq','ne'],value: ' : ;position 1:position 1;position 2:position 2;position 3:position 3;position 4:position 4;position 5:position 5'},
    align: 'center',edittype:'select',editoptions:{defaultvalue: 'P0: ',value: ' : ;position 1:position 1;position 2:position 2;position 3:position 3;position 4:position 4;position 5:position 5'},width: 75},

    {name:'joinYear',label:'Join Year',formatter:'date', formatoptions: {newformat:'d-m-Y'}, datefmt: 'dd-mm-yyyy', editable:true,searchtype: 'datepicker',align: 'center',width: 70,
    searchoptions:{dateFormat:'dd-mm-yy',dataInit: function (elem){
        $(elem).datepicker({ showButtonPanel: true, dateFormat: 'yy-mm-dd'});},sopt: ['eq','ne']},

    editoptions:{size:20,defaultValue: ' ',dataInit: function (elem) {

        $(elem).datepicker({ showButtonPanel: true, dateFormat: 'dd-mm-yy'});
    }}},

    {name:'experience', label:'Experience', formatter: experienceFunction, searchoptions:{sopt: ['eq','bw','ew','cn']}, editable:'hidden', editoptions:{defaultValue: ' '},align: 'center',width: 60},
    {name:'actiuni',label: 'Actiuni',formatter: 'actions', formatoptions: {afterSave:afterSaveFunction},editable: false,sortable: false,search: false,width: 20 }
    ];

grid.jqGrid({

    pager: '#pager', 
    url: "/RestWithDatabaseConnection/rest/fetchData",
    editurl:'/RestWithDatabaseConnection/rest/update',
    datatype: "json",
    height: 250,    
    viewrecords: true,
    scrollOffset:0,
    sortorder: 'asc', 
    caption:'Employee List' ,
    autowidth: true,
    colModel: colModelSettings,
    beforeSelectRow : function(id){ 

                        var idsArray = grid.jqGrid('getDataIDs');
                        var i;
                        for(i=0;i<idsArray.length;i++){
                            if($('#'+idsArray[i]).is('[editable="1"]') ){
                            grid.editRow(idsArray[i],true);
                            return false;
                            }
                        }   
                        return true;
                    },

    inlineEditing: {

        ajaxSaveOptions: { contentType: "application/json" },
        serializeSaveData: function (postData) {

            var idArray = grid.getCol('id');
            var count = 0;
            var k = 1;

                while(postData.id.search('jqg') != '-1'){

                    for(var i =0 ; i<idArray.length;i++){

                        if(k == idArray[i]){

                            count++;
                        }
                        if(count == 0){

                            postData.id = k  ;
                            break;
                        }
                    }
                    k++;
                }

            return JSON.stringify(postData);
        }
    }
});


grid.jqGrid('navGrid', '#pager', {edit:false, add:false, delete:true, save:false, cancel:false, search:true, searchtext: 'Search', refresh:true},

        {},{},{     
                url: '/RestWithDatabaseConnection/rest/delete',
                mtype: 'DELETE',
                reloadAfterSubmit: true,
                ajaxDelOptions: {
                    contentType: "application/json",
                },
                serializeDelData: function(postdata) {

                    return JSON.stringify(postdata);

            }},{},{},{},{} );

grid.jqGrid('inlineNav','#pager',
{
    edit:true,
    edittext: 'Edit',
    save:true,
    savetext: 'Save',
    add:true,
    cancel: true,
    canceltext: 'Cancel',
    cancelicon: 'ui-icon-cancel',
    addicon:'ui-icon-plus',
    addtext: 'Add',
    addedrow: 'last',
    addParams: {

        position: 'last',
        addRowParams: { 

            aftersavefunc : afterSaveFunction,
            keys: true,
        }
            },
    editParams:{

        url: '/RestWithDatabaseConnection/rest/update',
        mtype : "POST",
        keys: true,
        aftersavefunc : afterSaveFunction,
    }
})})

Edit2 - Server Responce to fetchData :

[{"id":"3","nume":"Aladin","prenume":"Zoro","username":"Aladin.Zoro","email":"Aladin.Zoro@test.com","sefDepartament":"Yes","position":"position 4","joinYear":"2015-11-08","experience":"2 months"},

{"id":"2","nume":"Harap","prenume":"Alb","username":"Harap.Alb","email":"Harap.Alb@test.com","sefDepartament":"No","position":"position 1","joinYear":"2016-01-03","experience":"9 days  "},

{"id":"4","nume":"Don","prenume":"Homa","username":"Don.Homa","email":"Don.Homa@test.com","sefDepartament":"No","position":"position 4","joinYear":"2015-09-06","experience":"4 months"},

{"id":"5","nume":"Dorel","prenume":"Gigel","username":"Dorel.Gigel","email":"Dorel.Gigel@test.com","sefDepartament":"No","position":"position 4","joinYear":"2016-01-10","experience":"2 days"},

{"id":"1","nume":"Ivan","prenume":"Stefan","username":"Ivan.Stefan","email":"Ivan.Stefan@test.com","sefDepartament":"No","position":"position 2","joinYear":"2016-01-10","experience":"2 days"}]

Answer 1

Below are some advises to solve your main problem and to improve the JavaScript code which you posted.

First of all, the generation of new rowids locally is required for local editing scenario. One should generate the new rowids on the server in case of saving the data on the backend in the database. Typical implementation consist on having PRIMARY KEY defined as int IDENTITY in every table. It makes the ids unique and fixed. Deleting of some row and creating the new one will never be interpreted as editing of the old row because the new row will get always new id, which was never used before (in the table).

To have advantage of ids generated on the server side one have two main choices:

1. reloading the grid after every Add row operation.
2. extending the communication with the server on editing so, that the server returns new id, generated in the database table, back to jqGrid. One can use aftersavefunc callback (for Add new row only) for updating the rowid after successful creating the row on the server. Many standard implementations of RESTful services returns back full row data inclusive id on both Add or Edit. One can use the data inside of aftersavefunc callback and use something like $("#" + rowid).attr("id", newRowid); to update the new row. It one saved the id in some additional columns (like you use hidden id column) then one should use setCell method additionally to update the cell too.

The first choice is the mostly simple and I would recommend you to implement it first of all. Only if reloading of the grid will not satisfy the users, who add many rows one after another, then you should write a little more code and to implement the second scenario.

You current code use inlineNav for Add and Edit operations, implemented using inline editing, and the method navGrid for Delete operation, implemented using form editing. The form editing, inclusive Delete, uses reloadAfterSubmit: true option by default. It means that the grid will be reloaded from the server (from url: "/RestWithDatabaseConnection/rest/fetchData") after deleting of every row. You can solve your main problem by replacing afterSaveFunction to the following:

var afterSaveFunction = function () {
        $(this).trigger("reloadGrid", [{current: true, fromServer: true}]);
    };

The option current to hold the current selection after reloading and the option fromServer: true have sense only in case if you use loadonce: true option additionally. You can just use reloadGridOptions: {fromServer: true} option of navGrid to force reloading the data from the server on click on the Refresh/Reload button of the navigator bar. If you have not so much data which you need to display in the grid (for example less as 1000 of rows) then such behavior would be recommended.

Some more common advices to you to improve your code:

You can consider to use height: "auto" instead of height: 250 and to manage the maximal height of the grid by specifying rowNum value. The option scrollOffset: 0 will be unneeded in the case.

The format of the data returned from the server looks so, that you don't implemented server side paging, sorting and filtering. You should use loadonce: true and forceClientSorting: true options. The loadonce: true informs jqGrid to save all the data returned from the server locally in internal data parameter. You can any time access the array by usage $('#grid').jqGrid("getGridParam", "data"). The value of rowNum (the default value is 20) will be used for local paging. The sortname and the sortorder will be used for local sorting. And you will use searching dialog (added by navGrid) or the filter toolbar (added by filterToolbar) for local searching/filtering. It simplify the server code, improve the performance of the grid from the user's point of view, and simplifies the interface between the server and the client. You can use the classical RESTful interface on the server without any extensions.

Another remark: I'd recommend you to remove unneeded hidden id column (name:'id', label:'id', key: true, hidden: true, ...). The information about the rowid will be saved in id attribute of the rows (<tr> element) and one don't need to hold duplicate information in the hidden <td> element in every row.

There are many other parts of your code, which could be improved. For example the DELETE operation which you use on the server side seems be strange. You use mtype: 'DELETE', but you send the id of deleted row inside of body of the request to the server instead of appending it to the URL. Corresponds to the standards, the HTTP DELETE should contains no body. You can use jqGrid option formDeleting to specify all Delete options and you can define url parameter as function:

formDeleting: {
    mtype: "DELETE",
    url: function (rowid) {
        return "/RestWithDatabaseConnection/rest/delete/" + rowid;
    },
    ajaxDelOptions: { contentType: "application/json" },
    serializeDelData: function () {
        return "";
    }
}

You need of cause modify your server code of /RestWithDatabaseConnection/rest/delete/ to use the same communication protocol and to get the id of deleted from from the URL.

You can use navOptions parameter of free jqGrid to specify the options of navGrid:

navOptions: { edit: false, add: false }

(searchtext: 'Search' and other options which you use seems to have default values and I removed there).

To be closer to REST standards one can use HTTP PUT operation for row editing and HTTP POST for adding new rows. You should implement different entry points for both operation on the backend. You use /RestWithDatabaseConnection/rest/update already and you can implement /RestWithDatabaseConnection/rest/create for adding new rows. You can use the following inlineEditing changes for example to implement the scenario:

inlineNavOptions: { add: true, edit: true },
inlineEditing: {
    url: function (id, editOrAdd) {
        return "/RestWithDatabaseConnection/rest/" +
            (editOrAdd === "edit" ? "update" : "create");
    },
    mtype: function (editOrAdd) {
        return editOrAdd === "edit" ? "PUT" : "POST";
    },
    keys: true,
    serializeSaveData: function (postData) {
        return JSON.stringify(dataToSend);
    },
    aftersavefunc: function () {
        $(this).trigger("reloadGrid", [{current: true, fromServer: true}]);
    },
    addParams: {
        addRowParams: {
            position: "last",
            serializeSaveData: function (postData) {
                var dataToSend = $.extend({}, postData);
                // don't send any id in case of creating new row
                // or to send `0`:
                delete dataToSend.id; // or dataToSend.id = 0; 
                return JSON.stringify(dataToSend);
            }
        }
    }
}