jqGrid with inline editing,deleting with server side (with little workaround)

Hi friends,

In jqGrid many developers have issue in Inline editing(Row editing) with some different types of controls. I had also faced this problem ,thats why I don't prefer Inline editing . But after so many mails from friends I thought to post Inline editing and deleting in jqGrid that I had created so long back . Before going details I want tell you ,this is a workaround (to update server side)because it is very difficult to make inline editing with server data. When I was working I googled a lot but I couldn't find a good way.In Every post I found client side Inline editing not serverside. Then I did some workaround and its works. There is no any kind of issues and its working fine in my case. Due to workaround I was not supposed to post this inline editing ,but after so many email I decided to post.

So lets go................................................................

In JqGrid add edit delete post we have already seen how to populate data and add ,edit and delete(form editing)in jqGrid. I will use same data and code for inline editing with little modification.

Our out put will be :

Complete code:

aspx page :

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="jqgridexample.aspx.cs"

Inherits="calendarcoloring.jqgridexample" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

</div>

</div>

</form>

For Inline editing I added action formatter.(its a default cell formatter in jqGrid). You can see in highlighted color.

$(document).ready(function () {

//code to get and fill dropdown from database

function getCountry() {

var country;

$.ajax({

type: "POST",

contentType: "application/json",

data: "{}",

async: false,

url: "jqgridexample.aspx/getNational",

dataType: "json",

success: function (data) {

country = data.d;

error: function (XMLHttpRequest, textStatus, errorThrown) {

debugger;

}

});

return country;

}

$.ajax({

type: "POST",

contentType: "application/json",

data: "{}",

url: "jqgridexample.aspx/getEmployee",

dataType: "json",

success: function (data) {

data = data.d;

$("#list").jqGrid({

datatype: "local",

colNames: ['','Employee Id', 'Name', 'Email', 'Phone', 'Password', 'Nationality', 'Date of Birth'],

colModel: [

<%-- // Below is the new column added for inline edit and delete in --%>

{ name: 'act', index: 'act', width: 75, align: 'center',search:false, sortable: false, formatter: 'actions',

formatoptions: {

keys: true,

editformbutton: false,

<%-- // editformbutton="false" not to open form editing dialog --%>

<%-- // this is the function that we need to call for server side data update in case of inline editing ,afterSave funtion is being called after updating data in client side (on submit and here I called a funtion getRowValue with rowId and in that function I make ajax call to save data in database and rebind the grid ,Make sure either your transaction gets success or fail you have to reload grid otherwise it will not show real data from database --%>

afterSave:function (rowid) {

getRowValue(rowid);

<%-- // Passing rowid in the function and will get all updated column data --%>

<%-- // editformbutton="false" not to open form editing dialog --%>

<%-- // deoption to delete row --%>

delOptions:

{

ajaxDelOptions: { contentType: "application/json" },

reloadAfterSubmit: false,

onclickSubmit: function (eparams) {

var retarr = {};

var sr = $("#list").getGridParam('selrow');

rowdata = $("#list").getRowData(sr);

retarr = { employeeId: rowdata.employeeId };

return retarr;

<%-- // serialization is required only if you want to send more data to server --%>

serializeDelData: function (data) {

var postData = { 'data': data };

return JSON.stringify(postData);

}

}}

<%-- // end of formatter --%>

{ name: 'employeeId', index: 'employeeId', width: 55, stype: 'text',searchoptions: { sopt: ['eq', 'ne', 'cn']}, sortable: true, editable: true, formoptions: { rowpos: 1, colpos: 1 }, editrules: { integer: true} },

{ name: 'name', index: 'name', width: 90, stype: 'text', sortable: true,searchoptions: { sopt: ['eq', 'ne', 'cn']}, editable: true, editrules: { required: true }, formoptions: { rowpos: 2, colpos: 1} },

{ name: 'email', index: 'email', width: 100, stype: 'text', sortable: true, searchoptions: { sopt: ['eq', 'ne', 'cn']},editable: true, editrules: { email: true, required: true }, formoptions: { rowpos: 2, colpos: 2} },

{ name: 'phone', index: 'phone', width: 80, align: "right", stype: 'text', searchoptions: { sopt: ['eq', 'ne', 'cn']},sortable: true, editable: true, formoptions: { rowpos: 3, colpos: 1} },

{ name: 'pwd', index: 'pwd', width: 80, align: "right", stype: 'text', edittype: 'password', sortable: true, editable: true, formoptions: { rowpos: 3, colpos: 2} },

{ name: 'nationalId', index: 'nationalId', width: 80, align: "right", formatter: 'select',stype: 'select',

editable: true, edittype: "select", editoptions: { value: getCountry() }, formoptions: { rowpos: 4, colpos: 1 }

{ name: 'dateOfBirth', index: 'dateOfBirth', width: 80, align: "right",

edittype: 'text', editable: true,

editoptions: { dataInit: function (el)

{ $(el).datepicker({

dateFormat: "dd/mm/yy",

changeMonth: true,

changeYear: true, }); } },

formoptions: { rowpos: 4, colpos: 2 }

}

data: JSON.parse(data),

rowno: 10,

loadonce:true,

/* multiselect:true,*/

rowlist: [5, 10, 20],

pager: '#pager',

viewrecords: true,

gridview: true,

sortorder: 'asc',

toppager: true,

cloneToTop: true,

altrows: true,

autowidth: false,

hoverrows: true,

height:300,

/* toolbar: [true, "top"],*/

rownumbers: true,

caption: "Employee Data",

editurl: 'jqgridexample.aspx/EditUpdateDel'

/* ondblClickRow: function(rowid) {

$(this).jqGrid('editGridRow', rowid,

{recreateForm:true,closeAfterEdit:true,

closeOnEscape:true,reloadAfterSubmit:false, width: 500});

}*/

});

$('#list').navGrid('#pager',

{

edit: false,

add: true,

del: false,

searchtext: "Search",

addtext: "Add",

edittext: "Edit",

deltext: "Delete",

cloneToTop: true

}

//for form edit here not required

, { recreateForm: true,

reloadAfterSubmit:false,

width: 500,

closeAfterEdit: true,

ajaxEditOptions: { contentType: "application/json" },

serializeEditData: function (postData) {

var postdata = { 'data': postData };

return JSON.stringify(postdata);

}

{

recreateForm: true,

beforeShowForm: function (form) {

$('#tr_employeeId', form).hide();

width: 500,

reloadAfterSubmit: false,

closeAfterAdd: false,

ajaxEditOptions: { contentType: "application/json" },

serializeEditData: function (postData) {

var postdata = { 'data': postData };

return JSON.stringify(postdata);

}

{

ajaxDelOptions: { contentType: "application/json" },

reloadAfterSubmit: false,

onclickSubmit: function (eparams) {

var retarr = {};

var sr = $("#list").getGridParam('selrow');

rowdata = $("#list").getRowData(sr);

retarr = { employeeId: rowdata.employeeId };

return retarr;

serializeDelData: function (data) {

var postData = { 'data': data };

return JSON.stringify(postData);

}

{multipleSearch:true});

$("#list").jqGrid('filterToolbar', { stringResult: true, searchOnEnter: true });

var grid = $("#list");

var topPagerDiv = $('#' + grid[0].id + '_toppager')[0]; // "#list_toppager"

/* $("#edit_" + grid[0].id + "_top", topPagerDiv).remove(); // "#edit_list_top"

$("#del_" + grid[0].id + "_top", topPagerDiv).remove(); // "#del_list_top"

$("#search_" + grid[0].id + "_top", topPagerDiv).remove(); // "#search_list_top"

$("#refresh_" + grid[0].id + "_top", topPagerDiv).remove(); // "#refresh_list_top"*/

$("#" + grid[0].id + "_toppager_center", topPagerDiv).remove(); // "#list_toppager_center"

$(".ui-paging-info", topPagerDiv).remove();

var bottomPagerDiv = $("div#pager")[0];

$("#add_" + grid[0].id, bottomPagerDiv).remove();

$("#edit_" + grid[0].id, bottomPagerDiv).remove();

$("#del_" + grid[0].id, bottomPagerDiv).remove();

$("#refresh_" + grid[0].id, bottomPagerDiv).remove();

// "#add_list"

error: function (XMLHttpRequest, textStatus, errorThrown) {

debugger;

}

});

<%-- // Now below is our hero which will be called to update data in database --%>

function getRowValue(rowId)

{

var rowData = $('#list').jqGrid ('getRowData', rowId);

<%-- // I have added oper to rowData object because in server side function I am checking with type of operation I am going to do. BY default we get edit ,but here we are doing some workaround so will not get it ,so need to add this "oper" --%>

rowData.oper="edit";

$.ajax({

type: "POST",

contentType: "application/json; charset=utf-8",

url:'jqgridexample.aspx/EditUpdateDel',// same url that used for editurl

data: JSON.stringify({ data: rowData }),

cache: false,

dataType: "json",

success: function (data) {

alert('Record Upadted successfully');

$("#list").trigger("reloadGrid")//to reload grid after successfull

error: function (result) {

$("#list").trigger("reloadGrid")//to reload grid after error.

alert('error');

}

});

}

</script>

and our cs code is same and previous post..................

.cs Page:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Newtonsoft;

using System.Web.Services;

using System.Web.Script.Serialization;

using System.Data;

using System.Data.SqlClient;

using System.Web.Script.Services;

namespace calendarcoloring

{

public partial class jqgridexample : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

[WebMethod]

public static string getEmployee()

{

SqlConnection con = newSqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "fetchemployeeData";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

con.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);

con.Close();

DataSet ds = new DataSet();

da.Fill(ds);

return Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[0]);

}

[WebMethod]

public static string getNational()

{ string returnvalue="";

SqlConnection con = newSqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "fetchNationality";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

con.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);

con.Close();

DataSet ds = new DataSet();

da.Fill(ds);

returnvalue+=""+""+""+":All;";

for (var i = 0; i < ds.Tables[0].Rows.Count; i++) {

returnvalue += ds.Tables[0].Rows[i]["nationalId"].ToString().Trim() + ":" + ds.Tables[0].Rows[i]["natiionName"].ToString().Trim() + ";";

}

returnvalue = returnvalue.Remove(returnvalue.LastIndexOf(';'));

return returnvalue;

}

[WebMethod, ScriptMethod(ResponseFormat = ResponseFormat.Json)]

public static string EditUpdateDel(object data)

{

bl businessL = new bl();

string returnMessage = "";

returnMessage = businessL.insUpdete(data);

return returnMessage;

}

blclass:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.SqlClient;

namespace calendarcoloring

{

public class bl

{

SqlConnection con = newSqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

SqlCommand cmd = new SqlCommand();

public string insUpdete(object param)

{

string returnvalue = "";

try

{

Dictionary<string, object> editData = param as Dictionary<string, object>;

cmd.CommandText = "insUpdDelete";

cmd.CommandType = CommandType.StoredProcedure;

if (editData["oper"].ToString() == "del")

{

cmd.Parameters.AddWithValue("@empId", editData["employeeId"] == DBNull.Value ? 0 : editData["employeeId"]);

cmd.Parameters.AddWithValue("@Actiontype", editData["oper"].ToString());

}

else

{

cmd.Parameters.AddWithValue("@empId", editData["employeeId"] == DBNull.Value ? 0 : editData["employeeId"]);

cmd.Parameters.AddWithValue("@name", editData["name"].ToString());

cmd.Parameters.AddWithValue("@Email", editData["email"].ToString());

cmd.Parameters.AddWithValue("@pwd", editData["pwd"].ToString());

cmd.Parameters.AddWithValue("@phone", editData["phone"].ToString());

cmd.Parameters.AddWithValue("@nationality", Convert.ToInt32(editData["nationalId"]));

cmd.Parameters.AddWithValue("@dateofbirth", editData["dateOfBirth"].ToString());

cmd.Parameters.AddWithValue("@Actiontype", editData["oper"].ToString());

}

cmd.Connection = con;

con.Open();

int m = cmd.ExecuteNonQuery();

if (m > 0)

{

if (editData["oper"].ToString() == "add")

{

returnvalue = "Record Added successfully";

}

else if (editData["oper"].ToString() == "edit")

{

returnvalue = "Record Updated successfully";

}

else if (editData["oper"].ToString() == "del")

{

returnvalue = "Record deleted successfully";

}

else

{

throw new Exception("there is problem");

}

catch (Exception ex)

{

returnvalue = ex.Message.ToString();

}

finally

{

con.Close();

}

return returnvalue;

}

tables and proc

Table :employeeTable

Column Name	Data Type
employeeId	int
name	nvarchar(500)
email	nvarchar(200)
pwd	nvarchar(8)
phone	nchar(10)
nationalityId	int
dateOfBirth	date

Another table is :nationalTable

Column Name	Data Type
nationalId	int
natiionName	nvarchar(200)

Now create a procedure to get data from database to show the employee records:

CREATE PROC [dbo].[fetchemployeeData]

SELECT employeeId,name,email,pwd,phone ,B.nationalId,CONVERT(VARCHAR(10),

dateOfBirth,103)AS dateOfBirth,B.natiionName FROM employeeTable

A JOIN nationalTable B ON A.nationalityId=B.nationalId ORDER BY name

For any doubt you can mail me any time....

If you need source file kindly leave your email id in comment box ...

If anyone has better way for Inline editing kindly send me code on mkumar.soft@gmail.com.

Happy coding.............

DEVELOPERS HUB :: MORE PRACTICAL LESS THEORY

Friday, April 15, 2016

jqGrid with inline editing,deleting with server side (with little workaround)

No comments:

Post a Comment

Convert Html to Pdf in azure function and save in blob container