jqGrid: how to update row id if primary key columns was edited

Question

Primary key values are used as row ids in json data returned from server. If primary key value is edited and saved two times, second save causes error since jqGrid passes original primary key value again to edit method.

How to update jqGrid row id to new primary key value if primary key value is changed in inline editing ?

$(function () {
        var grid = $("#grid");
        grid.jqGrid({
                url: '<%= ResolveUrl("~/Grid/GetData?_entity=Strings")%>',
                datatype: "json",
                mtype: 'POST',
                scroll: 1,
                autoencode: true,
                colModel: [{
                    name: 'Source',
                    fixed: true,
                    editable: true,
                    width: 30
                }, { /* this is primary key passed also as id */
                    name: 'Est',
                    fixed: true,
                    editable: true,
                    width: 271
                }, {
                    name: 'Eng',
                    fixed: true,
                    editable: true,
                    width: 167
                }],
                gridview: true,
                pager: '#pager',
                viewrecords: true,
                editurl: '<%= ResolveUrl("~/Grid/Edit?_entity=Strings")%>',
        ...

If separate form is used for editing we can use reloadAfterSubmit: true which refreshes id. Mabe it is possible to refresh inline edited row somehow. Or can we change row id after edit method request completes — Andrus, May 29 '11 at 20:54

score 4 · Accepted Answer · edited Jan 21 '13 at 11:49

4

The rowid is nothing more as the value of id attribute of the corresponding <tr> element of the grid. So to change the rowid oldRowid to newRowid you should do something like the following:

$("#" + oldRowid).attr("id", newRowid);

edited Jan 21 '13 at 11:49

Diego

16,436
26
84
136

answered May 29 '11 at 20:57

Oleg

220,925
34
403
798

row ids are created from all primary key columns and urlencoded to remove characters not allowed in row ids. Which event should be used to replace row id ? How to find previous and new row id values for replace ? – Andrus May 30 '11 at 10:51
@Andrus: removing or escaping of special characters are needed only if you write a general solution which will use other people who not really understand what he do. In your own solution you should use of course as `ids` more reasonable values. So you should never need problem escaping of characters. If you do has the problem you should use `$.jgrid.jqID`. – Oleg May 30 '11 at 11:24
@Andrus: the question about the event can't be answered without knowing the context. For example it is important to know which editing mode you use. Moreover I answered you only to show how the problem with id editing can be in general solved. I don't think that it should be ever done. Every good database design means that **ids should be never changed**. – Oleg May 30 '11 at 11:28
@Oleg: question states that inline editing is used. Database uses natural primary keys. Row ids are created in server by truncation composite key columns. It is possible to duplicate composite key columns as hidden columns if this can simplify the id change. Should editRow aftersavefunc be used ? Should server edit method handler return new row id so aftersavefunc can perform row id change ? – Andrus May 30 '11 at 15:46
1

@Andrus: In case of inline editing `aftersavefunc` is a good place. You the server just set the new `id` as the value of `Est` entered by the server you don't need (but can if other requirements exist) send any additional information in the server. About the "natural primary keys": users don't know what is the primary key, so it is pure the part of the database design only. In the multiuser environment the best (natural) keys are integer autoincrement. It is my conviction. The place here is too small for details. – Oleg May 30 '11 at 16:25
@Oleg: I modified controller to return new row id and created `function aftersavefunc(rowID, response) { alert( 'old: '+ rowID + '\nNew: '+ response.responseText ); $("#"+rowID).attr("id", response.responseText); }` alert box shows that correct ids are passed. However if saving row second time, initial rowID is passed again to server. It looks like this command does not chage rowID. How to force jqGrid to pass correct rowID on second save ? – Andrus May 30 '11 at 18:21
@Andrus: If you post url with your solution I could try to debug it to find out the reason. – Oleg May 30 '11 at 18:30
@Oleg: I send testcase to you using your company contact email. – Andrus May 31 '11 at 18:56
@Andrus: my e-mail is oleg.kiriljuk@ok-soft-gmbh.com – Oleg May 31 '11 at 19:18
@Andrus: I posted you that you use currently some characters in the id which [must be escaped](http://api.jquery.com/category/selectors/) if they used in the jQuery selectors. So you have to replace in your `aftersavefunc` function the statement `$("#"+rowID).attr("id", response.responseText);` to the `$("#"+rowID.replace(/([\.\+\%\:\[\]])/g,"\\$1")).attr("id", response.responseText);`. If you use more special characters in the `id` you should extend the code. The best way is to use INT AUTOINCREMENT (`int IDENTITY(1,1) NOT NULL`) as the definition of the primary key in all tables. – Oleg Jun 01 '11 at 11:19
@Oleg: jquery selector help does not provide full list of metacharacters. It wrote: If you wish to use any of the meta-characters ( such as !"#$%&'()*+,./:;<=>?@[\]^`{|}~ ). I changed controller to encode all non-englich letters and numbers in row ids. This seems to resolve the issue. I marked your answer as answer. Thank you. – Andrus Jun 01 '11 at 13:17
@Andrus: I do recommend you to change design of your database tables to use autoincremental integer ids (on MS SQL Server `int IDENTITY(1,1) NOT NULL` datatype). You can easy to extend existing tables with the ids. It will save many time in the future especially in multiuser environment and as a database for web application. Additionally I would recommend you to include non-nullable `timestamp` (`rowversion`) column in all tables and use it for the optimistic concurrency. If you not sure is it really good you can just ask new question on the stackoverflow and read the opinions of other users. – Oleg Jun 01 '11 at 17:43
@Oleg: I have existing database schema deployed to hundreds of sites with existing application in every those site. Schema uses natural keys for approx 20 tables . It works for many years. Re-factoring this schema and changing application is huge work. Also Joel Celko calls peoply who put id column in every table `id-iots` in its SQL book. So db refactoring is not reasonable. – Andrus Jun 03 '11 at 07:30
@Andrus: I don't know your environment of cause, but I still think that the changing of private key or adding new `INT IDENTIFY` column and the corresponding `UNIQUE` Constraint on the new column is what you need to do. Addin of new column mostly is transparancy for old applications and all continue working. The `UNIQUE` Constraint is almost the same as the primary key and it can be used to identify the row of the data. In jqGrid you can use the new column as the `id` and have no problem. You can easy generate a script with empty DB and post me I show what I mean on the example. – Oleg Jun 03 '11 at 10:29
"Also Joel Celko calls peoply who put id column in every table id-iots in its SQL book." I have heard this a few times, but never any compelling reasons to do it. It seems more of a 'data purist' idea, whereas IRL it is often very handy to have an auto-increment int as the primary key for reasons you are currently discovering. – Brandon Jun 30 '11 at 23:26
@Brandon: Which the book of Joel Celko you quote exactly? **When** the book was published? In the development of the web application is implementing of concurrency very important, but the old approach with locking of database objects is very dangerous in the web applications. So one use **optimistic concurrency** approach. In the case you should have more **uniqueness** in the data. The usage of `int IDENTITY(1,1) NOT NULL` as id and additional non-nullable `timestamp` (`rowversion`) column helps. Look at [here](http://stackoverflow.com/questions/2658443/concurrency-handling/2663654#2663654). – Oleg Jul 01 '11 at 05:18
@Oleg I was quoting Andrus. Yes universally unique IDs (primary keys) have some advantages, but in my opinion any gains are outweighed by the added complexity of managing non-numeric primary keys and the overhead of remembering/looking up for each table which field is the primary key. With a numeric auto-incrementing key, joins, foreign keys, and some other common tasks, such as getting the newest row, become trivial, with non-numeric keys not so much. – Brandon Jul 07 '11 at 15:53
@Brandon: Sorry, but I am not sure that I can full follow you. I think the choose of primary key should be shown in the context. Currently Andrus has tables with long English text in the first column declared as the primary key and many other column which contains translations of the text in other languages. If I correct understand he use such table for localization of the texts in his application. Such kind of very long text as the primary key is 1) slow in SQL by indexing 2) he has problem id will be appended in some cases to URL 3) modification of the English text follow to id modification. – Oleg Jul 07 '11 at 17:09
@Brandon: It rebuild the index and make more complex the working in multi-user environment (concurrency problems). So **in the situation** I recommended Andrus to use `int IDENTITY(1,1) NOT NULL` as the primary key or as an additional non-nullable column and to place UNIQUE CONSTRAIN to the column. So he will be able to find the text by the column value. He can use the value at least as the `id` of the jqGrid row. In the way he will solve an additional problem with escaping of special characters in the rowid of the jaGrid. – Oleg Jul 07 '11 at 17:15
@Oleg He should listen to you, I am not attempting to contradict your recommendations for this particular situation, but was merely commenting on the general case of whether it is worthwhile to include a numeric primary key from the outset. Andres suggested it is not, however I can find no compelling reasons behind that choice, although I have heard it several times before. – Brandon Jul 08 '11 at 00:04

score -1 · Answer 2 · edited Nov 22 '12 at 00:01

#gridPreSeleccion = id grid
grid multiselect=true
function eliminarSeleccionados() {
    var idsContribuyentesSelect = jQuery("#gridPreSeleccion").jqGrid('getGridParam', 'selarrrow');
    if(idsContribuyentesSelect.length == 0) {
        jQuery.MessageAlertSath("Es necesario seleccionar una fila.")
    } else {
        var ids = jQuery("#gridPreSeleccion").jqGrid('getDataIDs');
        var a = ids.length;
        var j = 0;
        while(j == 0) {
            if(jQuery("#gridPreSeleccion").jqGrid('getGridParam', 'selarrrow').length <= 0) {
                j = 1;
            } else {
                for(var i = 0; i < a; i++) {
                    if(idsContribuyentesSelect[0] == ids[i]) {
                        jQuery('#gridPreSeleccion').delRowData(ids[i]);
                        break;
                    }
                }
            }
        }
    }
}

jqGrid: how to update row id if primary key columns was edited

2 Answers2

Linked