This post shows how to use jqGrid to create an excel-like gridview for editing items in a SharePoint list. The grid rendered by jqGrid is similar to SharePoint’s Datasheet view, but it requires no ActiveX control. Additionally it can be programmed in JavaScript to give a custom edit experience, and can be used to edit lists on different site collections or even on different farms.
The first thing we need to do is get the jqGrid libraries into SharePoint. Googling for jqGrid download brings up http://www.trirand.com/blog/?page_id=6.
On this page select all the options, and click the download button. For this example I’ve extracted all the files in the downloaded zip to a new folder under the layouts directory, called jqGrid. If you don’t have access to your server, you can just as easily deploy the scripts to a document library in SharePoint.
[screenshot]
Now that we’ve got the libraries in SharePoint,, create a new TeamSite called jqGrid. Go to the sites home page and edit it in SharePoint designer. Enter the following code at the very beginning of the pages PlaceHolderAdditionalPageHead .
<script type="text/JavaScript" src="/_layouts/jqgrid/js/jquery-1.7.2.min.js"></script><script type="text/JavaScript" src="/_layouts/jqgrid/js/jquery.jqGrid.src.js"></script> <script type="text/JavaScript" src="/_layouts/jqgrid/js/i18n/grid.locale-en.js"></script>
This will load jQuery and jqGrid onto the page. At this point it’s a good idea to save your page and then load it with Fiddler running to ensure the JavaScript libraries are loaded correctly. Now that we’ve got the libraries loaded, we need to put an HTML table and div into the PlaceHolderMain as follows:
</pre> <div id="gridPager">
Make sure you place the code immediately after thetag and not within another tag, Now we need to insert some JavaScript to have jqGrid build the grid in taskTable and will place paging controls, and other components that interact with the grid into gridPager. Add the following scriptblock to the PlaceHolderAdditionalPageHead just after the script tags we just added.
<script type="text/JavaScript">
_spBodyOnLoadFunctionNames.push("buildgrid");
function buildgrid() {
$("#taskTable").jqGrid({
url: "/jqgrid/_vti_bin/listdata.svc/Tasks",
datatype: 'json',
sortable: true,
rownumbers: true,
jsonReader: {
root: "d.results",
repeatitems: false
},
colNames: ["Task Title", "Due", "Task Status", "Task Priority"],
colModel: [{
name: 'Title'},
{
name: 'DueDate'},
{
name: 'StatusValue'},
{
name: 'PriorityValue'}],
});
}
</script>
This code uses jQuery to find the taskTable we previously added to the page, and then calls the jqGrid method on the results passing in a single object that contains the parameters used to build the grid. The url parameter tells jqGrid where to get its data. In our case it’s “/jqgrid/_vti_bin/listdata.svc/Tasks”. This is the Rest Url for the tasks list in the new site we just created. We’ll use the Tasks list in this example, but any list can just as easily be used. The datatype parameter tells jqGrid that we are getting data in the JSON format. The jsonReader parameter tells jqGrid that the data to display in the grid can be found in the d.results node of the returned JSON object, and that the elements in d.results are not repeatable. The colNames parameter is used to add a title to be displayed for each column in the grid. The value for this parameter is an array of strings which must contain one string for each column. Finally the colModel parameter defines the actual columns to be used in the grid. The value of this parmeter is an array of objects which must contain one object for each column. In this simple example the objects within the colModel parameter just define the name attribute. The name attribute tells jqGrid the fields to select from the objects that it finds in d.results. The number of elements in the colNames and colModel must be the same — one for each column in the grid.
An explanation of these, and all the other jqGrid parameters, can be found in the jqGrid wiki at http://www.trirand.com/jqgridwiki/doku.php?id=wiki:jqgriddocs.
Save the page, then go to your site and add few items to the task list. Then go to the site home page and you should see a basic table displaying the task’s Title and Status.
If you view the page with fiddler now, you’ll see that the data request goes to http://yourserver/jqgrid/_vti_bin/listdata.svc/tasks?_search=false&nd=1338858958819&rows=-1&page=1&sidx=&sord=asc
jqGrid is designed to work more easily with some backend code that understands the query string parameters search, row, page, etc. While WCF rest service (which SharePoint rest services is built upon) supports similar constructs I haven’t yet figured out how to have jqGrid compose each of these parameters in a dialect that WCFrest understands. But this is not an issue as WCF rest simply ignores these unrecognized parameters , and returns all items in the list.
Before we move on to turn this basic html table into an editable grid, we need to install jQuery.UI. to do this, browse to http://jqueryui.com/download. On this page, check off all the components, and then choose a theme from the dropdown on the right (the Redmond theme seems to fit well with SharePoint). Select version 1.8.21 if it’s not already selected and click the download button. Open the downloaded .zip file and extract its content to a new subfolder under the layouts directory called jQueryUI. Again, if you don’t have access to your server, you can just as easily deploy the scripts to a document library in SharePoint.
Now that we’ve got jQuery.UI loaded, we can modify the page to reference it, and begin to make the grid editable. First add the followingtag to the beginning of the PlaceHolderAdditionalPageHead, before jquery-1.7.2.min.js.
Then add this script tag after the grid.locale-en.js script tag.
<script type="text/JavaScript" src="/_layouts/jqueryui/js/jquery-ui-1.8.21.custom.min.js"></script>
Your PlaceHolderAdditionalPageHead should now begin like this:
<script type="text/JavaScript" src="/_layouts/jqgrid/js/jquery-1.7.2.min.js"></script> <script type="text/JavaScript" src="/_layouts/jqgrid/js/jquery.jqGrid.src.js"></script><script type="text/JavaScript" src="/_layouts/jqgrid/js/i18n/grid.locale-en.js"></script> <script type="text/JavaScript" src="/_layouts/jqueryui/js/jquery-ui-1.8.21.custom.min.js"></script><script type="text/JavaScript">// <![CDATA[ _spBodyOnLoadFunctionNames.push("buildgrid"); ...
This added the neccessary libraries to the page. Now we need to mark each columns as editable by adding an edit:true attribute to each column in the colModel. We can also tell jsGrid that the DueDate is in ‘ISO8601Long’ format and should be converted to ‘m/d/Y’ fomat and to use the jqueryUI Datapicker to edit it. While we’re ata it, we can adjust the columns widths. Change the colModel as folows:
colModel: [
{ name: 'Title',editable:true ,sortable:true,index:'Title', width:100},
{ name: 'DueDate',
editable:true , width:100 , formatter: 'date',
formatoptions: {
srcformat: 'ISO8601Long',
newformat: 'm/d/Y'
},
edittype: 'text',
editoptions: {
size: 12,
maxlengh: 12,
dataInit: function (element) {
$(element).datepicker({ dateFormat: 'mm/dd/yy' }
)
}
},
editrules: {
date: true
}
},
{ name: 'StatusValue',editable:true, width:100, sortable:true, index:'StatusValue'} ,
{ name: 'PriorityValue',editable:true, width:100} ,
]
and we need to tell tell jqGrid to edit the row when it is selected by adding an onSelectRow function to the parameters passed to jqGrid:
onSelectRow: function (id) {
$("#taskTable").editRow(id, true, null, null);
}
The complete JavaScript block should now look like this :
<script type="text/JavaScript">
_spBodyOnLoadFunctionNames.push("buildgrid");
function buildgrid() {
$("#taskTable").jqGrid({
url: "/jqgrid/_vti_bin/listdata.svc/Tasks",
datatype: 'json',
sortable: true,
rownumbers: true,
jsonReader: {
root: "d.results",
repeatitems: false
},
colNames: ["Task Title", "Due", "Task Status", "Task Priority"],
colModel: [
{
name: 'Title',
editable: true,
sortable: true,
index: 'Title',
width: 100},
{
name: 'DueDate',
editable: true,
width: 100,
formatter: 'date',
formatoptions: {
srcformat: 'ISO8601Long',
newformat: 'm/d/Y'
},
edittype: 'text',
editoptions: {
size: 12,
maxlengh: 12,
dataInit: function(element) {
$(element).datepicker({
dateFormat: 'mm/dd/yy'
})
}
},
editrules: {
date: true
}},
{
name: 'StatusValue',
editable: true,
width: 100,
sortable: true,
index: 'StatusValue'},
{
name: 'PriorityValue',
editable: true,
width: 100},
],
onSelectRow: function(id) {
$("#taskTable").editRow(id, true, null, null);
}
});
}
// ]]></script>
If you view the page now, when you click on a row, you will be able to edit the values in the columns, but when you press enter (which is how you save a row in jqGrid) you will get an error message because we have not defined the editUrl. The editUrl parameter is the url that jqgrid will use to save items. ListData.svc passes the url and etag of each object in d.results in fields called __metadata.uri and __metadata.etag.You can see this if you run fiddler when you load the page. When updating an item using listdata.svc you need to create an HTTP PUT request to the items uri (__metadat.uri) and pass the ‘etag’ of the item in an ‘If-Match’ HTTP Header. The JSON representation of the listitem can be passed in the body of the request. First lets add these two columns (the etag and uri) to the grid so we can see what they are (we can hide them later). Be sure to mark them as editable:false. The colNames and colModel attributes of the jqgrid parameter object should now look like this:
colNames:["Task Title","Due","Task Status","Task Priority","url","etag"],
colModel: [
{ name: 'Title',editable:true ,sortable:true,index:'Title', width:100},
{ name: 'DueDate',
editable:true , width:100 , formatter: 'date',
formatoptions: {
srcformat: 'ISO8601Long',
newformat: 'm/d/Y'
},
edittype: 'text',
editoptions: {
size: 12,
maxlengh: 12,
dataInit: function (element) {
$(element).datepicker({ dateFormat: 'mm/dd/yy' }
)
}
},
editrules: {
date: true
}
},
{ name: 'StatusValue',editable:true, width:100,sortable:true,index:'StatusValue'} ,
{ name: 'PriorityValue',editable:true, width:100} ,
{ name: '__metadata.uri', editable: false,width: 400},
{ name: '__metadata.etag', editable: false,width: 100}
],
Then we need to adjust the onSelectRow funnction to set the grids editUrl property to the url of the selected item whenever a user selects a row.
onSelectRow: function (id) {
var rowData = $("#taskTable").getRowData(id);
var newUrl = rowData['__metadata.uri'];
$(this).setGridParam({
'editurl': newUrl
});
$("#taskTable").editRow(id, true, null, null);
}
Now save the page, go to your browser, reload the page, and click on a row to edit it. When you press enter to save the row you should get an error message saying that the URI is not valid for a POST operation. We need to to modify the request to add an HTTP Header that tells listdata.svc that we really want an HTTP MERGE request and an HTTP Header to specify the etag. A MERGE operation will update only the fields that are specified and changed from current version as outlined here: http://msdn.microsoft.com/en-us/library/ff798339.aspx). We’ll also need to serialize the row data in JSON format so that it can be understood by listdata.svc. Add the serializeRowData and ajaxRowOptions as shown in the following:
<script type="text/JavaScript">
_spBodyOnLoadFunctionNames.push("buildgrid");
function buildgrid() {
$("#taskTable").jqGrid({
url: "/jqgrid/_vti_bin/listdata.svc/Tasks",
datatype: 'json',
sortable: true,
rownumbers: true,
jsonReader: {
root: "d.results",
repeatitems: false
},
colNames: ["Task Title", "Due", "Task Status", "Task Priority", "url", "etag"],
colModel: [{
name: 'Title',
editable: true,
sortable: true,
index: 'Title',
width: 100},
{
name: 'DueDate',
editable: true,
width: 100,
formatter: 'date',
formatoptions: {
srcformat: 'ISO8601Long',
newformat: 'm/d/Y'
},
edittype: 'text',
editoptions: {
size: 12,
maxlengh: 12,
dataInit: function(element) {
$(element).datepicker({
dateFormat: 'mm/dd/yy'
})
}
},
editrules: {
date: true
}},
{
name: 'StatusValue',
editable: true,
width: 100,
sortable: true,
index: 'StatusValue'},
{
name: 'PriorityValue',
editable: true,
width: 100},
{
name: '__metadata.uri',
editable: false,
width: 400},
{
name: '__metadata.etag',
editable: false,
width: 100}],
onSelectRow: function(id) {
var rowData = $("#taskTable").getRowData(id);
var newUrl = rowData['__metadata.uri'];
$(this).setGridParam({
'editurl': newUrl
});
$("#taskTable").editRow(id, true, null, null);
},
serializeRowData: function(postdata) {
var x = JSON.stringify(postdata);
return x;
},
ajaxRowOptions: {
contentType: 'application/json; charset=utf-8',
datatype: 'json',
mtype: 'POST',
async: true,
beforeSend: function(jqXHR, settings) {
grid = $("#taskTable").jqGrid();
var selectedRow = grid.jqGrid('getGridParam', 'selrow');
var etag = grid.jqGrid('getCell', selectedRow, '__metadata.etag');
jqXHR.setRequestHeader("X-HTTP-Method", "PUT");
jqXHR.setRequestHeader("If-Match", etag);
}
}
});
}</script>
Again save the page, then go to your browser, reload the page and click on a row to edit it. When you press enter to save the row you should now get an error message saying that the property name “oper” is not valid. jqGrid by default sends its commands (add, change, delete) in an “oper” field , but listdata.svc uses http headers to specify the commands. We’ll just need to remove the “oper” property from the object before sending it. We’ll also need to remove the __metadata.etag , __metadata.uri, and id properties of the JSON object before sending it becuase these are not recognized by listdata.svc Modify the beforeSend function within the ajaxRowOptions as follows:
beforeSend: function (jqXHR, settings) {
grid = $("#taskTable").jqGrid(); // get the selected row
var selectedRow = grid.jqGrid('getGridParam', 'selrow');
var etag = grid.jqGrid('getCell', selectedRow, '__metadata.etag');
jqXHR.setRequestHeader("X-HTTP-Method", "MERGE");
jqXHR.setRequestHeader("If-Match", etag);
var postdata = $.parseJSON(settings.data);
delete postdata['oper']; // dont send operation to the server
delete postdata["__metadata.etag"];
delete postdata["__metadata.uri"];
delete postdata["id"];
settings.data = JSON.stringify(postdata);
}
Once more, save the page, then go to your browser, reload the page and click on a row to edit it. Change the Title and Status. It should appear that your updates were saved, but if you reload the page, you’ll see that the Status is now empty,unless you typed in a valid status. This is beacause the status field is actually a choice column(in some cases the text you typed in may be added as the new status for the item, even though it is not a valid choice). We need to create a dropdown list to let the user select from the list of valid Statuses. If you browse to http://yourservername/jqgrid/listdata.svc you will see that there are collections called TasksStatus and TaskPriority that define the valid statuses and priorities for tasks. So let’s make Status and Priority dropdowns. We will need to load the valid values for statuses and priorities into two JavaScript arrays, and then tell jqGrid to build an html select control using theses arrays to let users select a value for the status and priority columns . First let’s declare two arrays to hold the statuses and priorities, and add two new functions, called loadStatus and loadPriority. Add this code just before the buildGrid function :
var statuses = {}; // THIS will hold the array of valid statuses
var priorities= {}; // THIS will hold the array of valid priorities
function loadStatus(){
return $.ajax({ // return the ajax object so we can chain it in a jQuery when statement.
async: true,
type: "GET",
url: "/jqgrid/_vti_bin/listdata.svc/TasksStatus",
dataType: "json",
success: function(data, textStatus, jqXHR) {
$(data.d.results).each(function() {
statuses[this.Value] = this.Value; // this is the construct required by jqGrid
});
},
error: function(jqXHR, textStatus, errorThrown) {
alert(textStatus);
}
});
}
function loadPriority(){
return $.ajax({ // return the ajax object to i can chain it in a jQuery when statement.
async: true,
type: "GET",
url: "/jqgrid/_vti_bin/listdata.svc/TasksPriority",
dataType: "json",
success: function(data, textStatus, jqXHR) {
$(data.d.results).each(function() {
priorities [this.Value] = this.Value;
});
},
error: function(jqXHR, textStatus, errorThrown) {
// eval("var response = " + jqXHR.responseText);
// alert(response.error.message.value);
alert(textStatus);
}
});
}
The loadStatus and loadPriority functions asynchronously load the statuses and priorities into the two arrays. The functions return the actual jQuery ajax object so that they can be chained together in a jQuery ‘when’ function. The when function will wait until each of the ajax requests is complete before continuing . We’ll use this functionality to retrieve the statuses and priorities asynchronously, and only show the grid after both have been retrieved. Add a new function called pageLoad just before the loadStatus fubnction as follows:
function pageLoad() {
$.when(loadStatus(),loadPriority()).then(buildgrid);
}
This function effectively says “when the ajax objects returned by loadStatus and loadPriority are complete, then call buildgrid. The two ajax requests are executed simultaneously, and we won’t continue until they both complete.
Now we can modify _spBodyOnLoadFunctionNames to call pageLoad rather than buildgrid so that the grid is displayed only after we have the priorities and statuses:
_spBodyOnLoadFunctionNames.push("pageload");
Now modify the colModel specify so that both the priority and status columns render as html select elements when being edited. We use the value property of the editoptions to set the contents of the select controls to the previously created arrays:
colNames:["Task Title","Task Status","Task Priority","url","etag"],
colModel: [
...
{ name: 'StatusValue',editable:true, width:100,sortable:true,edittype:'select', editoptions:{value:statuses}} ,
{ name: 'PriorityValue',editable:true, width:100,edittype:'select', editoptions:{value:priorities}} ,
...
]
Your JavaScript should now look like this:
<script type="text/JavaScript">_spBodyOnLoadFunctionNames.push("pageload");
var statuses = {};
var priorities = {};
function pageLoad() {
$.when(loadStatus(), loadPriority()).then(buildgrid);
}
function loadStatus() {
return $.ajax({.async: true,
type: "GET",
url: "/jqgrid/_vti_bin/listdata.svc/TasksStatus",
dataType: "json",
success: function(data, textStatus, jqXHR) {
debugger;
$(data.d.results).each(function() {
statuses[this.Value] = this.Value;
});
},
error: function(jqXHR, textStatus, errorThrown) {
alert(textStatus);
}
});
}
function loadPriority() {
return $.ajax({
async: true,
type: "GET",
url: "/jqgrid/_vti_bin/listdata.svc/TasksPriority",
dataType: "json",
success: function(data, textStatus, jqXHR) {
debugger;
$(data.d.results).each(function() {
priorities[this.Value] = this.Value;
});
},
error: function(jqXHR, textStatus, errorThrown) {
alert(textStatus);
}
});
}
function buildgrid() {
$("#taskTable").jqGrid({
url: "/jqgrid/_vti_bin/listdata.svc/Tasks",
datatype: 'json',
sortable: true,
rownumbers: true,
jsonReader: {
root: "d.results",
repeatitems: false
},
colNames: ["Task Title", "Due", "Task Status", "Task Priority", "url", "etag"],
colModel: [{
name: 'Title',
editable: true,
sortable: true,
index: 'Title',
width: 100},
{
name: 'DueDate',
editable: true,
width: 100,
formatter: 'date',
formatoptions: {
srcformat: 'ISO8601Long',
newformat: 'm/d/Y'
},
edittype: 'text',
editoptions: {
size: 12,
maxlengh: 12,
dataInit: function(element) {
$(element).datepicker({
dateFormat: 'mm/dd/yy'
})
}
},
editrules: {
date: true
}},
{
name: 'StatusValue',
editable: true,
width: 100,
sortable: true,
edittype: 'select',
editoptions: {
value: statuses
}},
{
name: 'PriorityValue',
editable: true,
width: 100,
edittype: 'select',
editoptions: {
value: priorities
}},
{
name: '__metadata.uri',
editable: false,
width: 400},
{
name: '__metadata.etag',
editable: false,
width: 100}],
onSelectRow: function(id) {
var rowData = $("#taskTable").getRowData(id);
var newUrl = rowData['__metadata.uri'];
$(this).setGridParam({
'editurl': newUrl
});
$("#taskTable").editRow(id, true, null, null);
},
serializeRowData: function(postdata) {
var x = JSON.stringify(postdata);
return x;
},
ajaxRowOptions: {
contentType: 'application/json; charset=utf-8',
datatype: 'json',
mtype: 'POST',
async: true,
beforeSend: function(jqXHR, settings) {
grid = $("#taskTable").jqGrid();
var selectedRow = grid.jqGrid('getGridParam', 'selrow');
var etag = grid.jqGrid('getCell', selectedRow, '__metadata.etag');
jqXHR.setRequestHeader("X-HTTP-Method", "MERGE");
jqXHR.setRequestHeader("If-Match", etag);
var postdata = $.parseJSON(settings.data);
delete postdata['oper'];
delete postdata["__metadata.etag"];
delete postdata["__metadata.uri"];
delete postdata["id"];
settings.data = JSON.stringify(postdata);
}
}
});
}</script>
Go ahead and save the file in SharePoint Designer, Refresh your browser, and edit an item. The priority and status should display as dropdowns. Change the values on a row and press enter to save. If you reload the page you should see that the values were, in fact saved. So far so good. But now try to change a row, press enter to save, and then change the row again without reloading, You should see an error saying that the etags don’t match. That’s because when we updated the item the first time, a new etag value was assigned. When we attempted to update it a second time, we sent the original etag. The original etag we sent did not match the new etag assigned after our first update so listdata.svc stops the update. It assumes someone else has update the item because the etags don’t match.
If you start fiddler, and then update an item using the jqGrid, you’ll see that listdata.svc returned an HTTP 204 response, with a an HTTP header named Etag, that contains the newly assigned Etag that should be included on subsequent Updates. So we’ll need to add a success callback to our ajaxRowOptions to get that HTTP Header and put it in our grid so that our next update will use it instead of our original etag. We’ll also need to pass the selected row to the success callback so that it knows which row to update with the newly assigned etag. There is an issue Internet Explorer that causes it to drop all headers when it receives an HTTP 204 response as documented here: http://www.enhanceie.com/ie/bugs.asp. To get around this, we’ll need to use a second Ajax request in our success handler to get the new etag, and pass the selected row along to the success callback of this second request.
First , change the beforeSend function on the ajaxRowOptions to add the selected row to the ajax request:
beforeSend: function (jqXHR, settings) {
grid = $("#taskTable").jqGrid(); // get the selected row
this.selectedRow= grid.jqGrid('getGridParam', 'selrow');// add the selected row to the ajax object so we can get at in in the callback
var etag = grid.jqGrid('getCell', this.selectedRow, '__metadata.etag');
jqXHR.setRequestHeader("X-HTTP-Method", "MERGE");
jqXHR.setRequestHeader("If-Match", etag);
var postdata = $.parseJSON(settings.data);
delete postdata['oper']; // dont send operation to the server
delete postdata['id']; // dont send operation to the server
delete postdata["__metadata.etag"];
delete postdata["__metadata.uri"];
settings.data = JSON.stringify(postdata);
},
And then add the success callback to the ajaxRowOptions:
success: function (data, textStatus, jqXHR) {
// requery the resource to get the new etag // this is due to a bug in ie that drops all headers associated with an h204
var selectedRow= this.selectedRow;
$.ajax({
beforeSend: function (jqXHR, settings) { // need to pass the selectedRow to the second (nested callback)
this.selectedRow= selectedRow;
},
async: false,
contentType: 'application/json; charset=utf-8',
datatype: 'json',
url: this.url,
success: function (data, textStatus, jqXHR) {
var etag = jqXHR.getResponseHeader("ETag");
$("#taskTable").jqGrid("setCell", this.selectedRow, "__metadata.etag", etag); // update the etag on the selected row
}
})
}
Now you should be able to make multiple updates to any row in the grid , without reloading the page.
One last issue to fix is that if you click the first row to edit it , and then click the second row to edit it (without saving the first), two rows appear to be in edit mode. When the user clicks the second row to edit it, we should cancel the edit operation on the first row, and restore its contents.
First we need to add a variable to the top of the script block to keep track of the last selected row:
<script type="text/JavaScript">
_spBodyOnLoadFunctionNames.push("pageload");
var statuses = {}; // THIS will hold the array of valid statuses
var priorities= {}; // THIS will hold the array of valid priorities
var lastSel; // This wil hold the last selected row
And then we need to adjust the onSelectRow function
onSelectRow: function (id) {
var rowData = $("#taskTable").getRowData(id);
var newUrl = rowData['__metadata.uri'];
$(this).setGridParam({
'editurl': newUrl
});
if (id && id !== lastSel) {
$("#taskTable").restoreRow(lastSel);
lastSel = id;
}
$("#taskTable").editRow(id, true, null, null);
},
So we’ve gotten the basic row editing working now. In a future post I will show how to add and delete rows, edit a list on a different server, and how to edit lookup columns. Much of the script in this post is boilerplate code that would be used in any jqGrid application that accesses SharePoint lists. jqGrid can be customized using extensions. In the future I hope to create an extension that can be applied to jqGrid so the boilerplate code would not need to be entered on every page. In the mean time there is extensive documentation available for jqgrid on the jqgrid wiki: http://www.trirand.com/jqgridwiki/doku.php?id=wiki:jqgriddocs.
And finally, here is the complete JavaScript listing, once again
<script type="text/JavaScript">
_spBodyOnLoadFunctionNames.push("pageload");
var statuses = {}; // THIS will hold the array of valid statuses
var priorities = {}; // THIS will hold the array of valid priorities
var lastSel; // This wil hold the last selected row
function pageLoad() {
$.when(loadStatus(), loadPriority()).then(buildgrid);
}
function loadStatus() {
return $.ajax({ // return the ajax object to i can chain it in a jQuery when statement. C'nt load grid until these all complete.
async: true,
type: "GET",
url: "/jqgrid/_vti_bin/listdata.svc/TasksStatus",
dataType: "json",
success: function(data, textStatus, jqXHR) {
$(data.d.results).each(function() {
statuses[this.Value] = this.Value; // this is the construct required by jqGrid
});
},
error: function(jqXHR, textStatus, errorThrown) {
alert(textStatus);
}
});
}
function loadPriority() {
return $.ajax({ // return the ajax object to i can chain it in a jQuery when statement. C'nt load grid until these all complete.
async: true,
type: "GET",
url: "/jqgrid/_vti_bin/listdata.svc/TasksPriority",
dataType: "json",
success: function(data, textStatus, jqXHR) {
$(data.d.results).each(function() {
priorities[this.Value] = this.Value;
});
},
error: function(jqXHR, textStatus, errorThrown) {
// eval("var response = " + jqXHR.responseText);
// alert(response.error.message.value);
alert(textStatus);
}
});
}
function buildgrid() {
$("#taskTable").jqGrid({
url: "/jqgrid/_vti_bin/listdata.svc/Tasks",
datatype: 'json',
sortable: true,
rownumbers: true,
jsonReader: {
root: "d.results",
repeatitems: false
},
colNames: ["Task Title", "Due", "Task Status", "Task Priority", "url", "etag"],
colModel: [
{
name: 'Title',
editable: true,
sortable: true,
index: 'Title',
width: 100},
{
name: 'DueDate',
editable: true,
width: 100,
formatter: 'date',
formatoptions: {
srcformat: 'ISO8601Long',
newformat: 'm/d/Y'
},
edittype: 'text',
editoptions: {
size: 12,
maxlengh: 12,
dataInit: function(element) {
$(element).datepicker({
dateFormat: 'mm/dd/yy'
})
}
},
editrules: {
date: true
}},
{
name: 'StatusValue',
editable: true,
width: 100,
sortable: true,
edittype: 'select',
editoptions: {
value: statuses
}},
{
name: 'PriorityValue',
editable: true,
width: 100,
edittype: 'select',
editoptions: {
value: priorities
}},
{
name: '__metadata.uri',
editable: false,
width: 400},
{
name: '__metadata.etag',
editable: false,
width: 100}
],
onSelectRow: function(id) {
var rowData = $("#taskTable").getRowData(id);
var newUrl = rowData['__metadata.uri'];
$(this).setGridParam({
'editurl': newUrl
});
if (id && id !== lastSel) {
$("#taskTable").restoreRow(lastSel);
lastSel = id;
}
$("#taskTable").editRow(id, true, null, null);
},
serializeRowData: function(postdata) { //USED ON EDIT
var x = JSON.stringify(postdata);
return x;
},
ajaxRowOptions: { // USED ON EDIT
contentType: 'application/json; charset=utf-8',
datatype: 'json',
mtype: 'POST',
async: true,
beforeSend: function(jqXHR, settings) {
grid = $("#taskTable").jqGrid(); // get the selected row
this.selectedRow = grid.jqGrid('getGridParam', 'selrow'); // add the selected row to the ajax object so we can get at in in the callback
var etag = grid.jqGrid('getCell', this.selectedRow, '__metadata.etag');
jqXHR.setRequestHeader("X-HTTP-Method", "MERGE");
jqXHR.setRequestHeader("If-Match", etag);
var postdata = $.parseJSON(settings.data);
delete postdata['oper']; // dont send operation to the server
delete postdata['id']; // dont send operation to the server
delete postdata["__metadata.etag"];
delete postdata["__metadata.uri"];
settings.data = JSON.stringify(postdata);
},
success: function(data, textStatus, jqXHR) {
debugger
// requery the resource to get the new etag // this is due to a bug in ie that drops all headers associated with an h204
var selectedRow = this.selectedRow;
$.ajax({
beforeSend: function(jqXHR, settings) { // need to pass the selectedRow to the second (nested callback)
this.selectedRow = selectedRow;
},
async: false,
contentType: 'application/json; charset=utf-8',
datatype: 'json',
url: this.url,
success: function(data, textStatus, jqXHR) {
var etag = jqXHR.getResponseHeader("ETag");
$("#taskTable").jqGrid("setCell", this.selectedRow, "__metadata.etag", etag); // update the etag on the selected row
}
})
}
}
});
}
</SCRIPT>
Yet Another SharePoint Blog 
Thank you for this post. Its really helpful.However i have a query.
I am loading project budget data in jqiGrid and wanted to add comment to each cell(it can be done on double click of cell – opening a dialog with two fields – one for amount and another to add comment) of the jqGrid as we do in MSExcel. Is this possible by any chance? Please help.
I’m not sure if this can be done with jqGrid. You may be able to get help with this bu posting your query to stackoverflow, or the trirand message board at http://www.trirand.com.
Also, I need to save the data back to SQL server 2008 r2 database upon editing JqGrid data. I have written a method in my RESTful WCF service to save the data back. What should be the EditUrl – “/WCFService.svc/EditBudgetData” or something like that. I dont think it should be similar to what you have posted above.
Hi again. The purpose of this post was to explain how to get jqgrid to work with listdata.svc. If you’re creating a custom web service to work with jqgrid you won’t have to overcome many of the obstacles I did here because you’re in control of both sides of the conversation.
However, I haven’t written a custom WCF service to work with jqgrid. I think this post may be able to help you http://www.codeproject.com/Articles/38298/Working-with-jqGrid-and-a-WCF-Service.
Hi, Thanks for your help!
I am done with code implementation for Web Part in VS 2010,however, i am unable to deploy my custom web part on Share point site i have created. When i add the Web Part, Share point errors out with a message : ‘Cannot import this Web Part’.
Please Help!
My VS 2010 solution has 4 projects:
— Data Access Layer project {using LINQ to SQL classes}
— WCF service {interfacing with Data access layer and SharePoint UI layer – creates JSON formatted strings using WebInvoke}
— SharePoint project with Custom WebPart, includes Javascripts to use jggrid functionality.
I’m not sure howe you’re going about doing your deployment, but this post seems to have some good information on things to check when you get that error meessage:
http://sharepoint.stackexchange.com/questions/11820/custom-web-part-deployment-produces-cannot-import-this-webpart-error
Hey Russ, thanks for the fantastic post. I’ve been able to get the Edit functionality working by using your example, but I’m not able to figure out how to Create items using ListData.svc… any chance you can post up that example?
I don’t think i have the source for this example any longer. You could just put a link to your newform.aspx on the page, or are you trying to add items in the grid?
Many many thanks for replying and so quickly!
I would like to do it using JQGrid’s mechanisms, either inline Grid or its Popup and also by using ListData.svc.
Currently, I can only create new items this way, by using javascript and SP.ClientContext. It’s not good enough. I want to do it your way!
{
jQuery(“#taskgrid”).jqGrid(‘navButtonAdd’, “#taskgridpager”,
{
caption: “Add Task”,
// buttonicon: “ui-icon-newwin”,
onClickButton: AddTask,
position: “last”,
title: “”,
cursor: “pointer”
});
}
function callCreateListItemSvc() {
var j = $(‘#taskTitle’).val();
var clientContext = new SP.ClientContext(‘/dev’);
var oList = clientContext.get_web().get_lists().getByTitle(‘Tasks’);
var itemCreateInfo = new SP.ListItemCreationInformation();
this.oListItem = oList.addItem(itemCreateInfo);
oListItem.set_item(‘Title’, j);
oListItem.update();
clientContext.load(oListItem);
clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
}
function onQuerySucceeded() {
jQuery(“#taskgrid”).trigger(“reloadGrid”)
}
function onQueryFailed(sender, args) {
alert(‘Request failed. ‘ + args.get_message() + ‘\n’ + args.get_stackTrace());
}
Thanks again,
J
if you want to update the list using listdata.svc you need to do a post to listdata.svc. I did a short writeup on it here: https://yetanothersharepointblog.wordpress.com/2013/03/29/updating-sharepoint-multi-value-lookup-fields-using-the-rest-api/ but i’m sure you could find more extensive writeups elsewhere. Heres the msdn link: http://msdn.microsoft.com/en-us/library/ff798339.aspx
.
btw, have you seen http://datatables.net/. check out http://www.sharepointhillbilly.com/Lists/Posts/Post.aspx?ID=49.
The library is a little easier to work with than jqgrid.
these are the inputs for my code: