<script type="text/javascript">
$(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 --%>
<%-- // 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 --%>
<%-- // 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]
AS
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.
