Forum
Log In
Home
Topic RSS
14:17
27/04/2010
Offline
Hello,
I am trying to use the standard jqGrid with an asp.net webforms application by using webservices to load and save data from and to a MySQL database.
I have succeeded in loading the data in the grid from the database using a webservice.
But I have some problem saving the data when using cell editing.
Here is my javascript code for the client side:
datatype: function() {
$(".loading").show();
$.ajax({
url: "WebServiceJqGridLoad.asmx/users",
data: "{}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
complete: function(jsondata, stat) {
if (stat == "success") {
var thegrid = $("#grid_users")[0];
thegrid.addJSONData(eval("(" + jsondata.responseText + ").d"));
$(".loading").hide();
}
else{
$(".loading").hide();
alert("Error with AJAX callback");
}
}
});
},
colNames:['Name', 'Firstname', 'login', 'Password'],
colModel :[
{name:'name',
index:'name',
editable:false
},
{name:'firstname',
index:'firstname',
editable:false
},
{name:'login',
index:'login',
editable:true
},
{name:'password',
index:'password',
editable:true
},
],
autowidth: true,
mtype: 'POST',
cellEdit: true,
cellsubmit: 'remote',
cellurl: "WebServiceJqGridEdit.asmx/users",
gridview: true,
scroll: 1,
sortable: true,
sortname: 'name',
sortorder: 'desc',
hidegrid: false,
caption: "Users configuration"
});
Here is my code for the server side that enables the loading of the data from the database (This is a webservice):
using System.Collections.Generic;
using System.ComponentModel;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Web.Script.Serialization;
using MySql.Data.MySqlClient;
namespace Test
{
public class Row
{
public string id;
public string[] cell;
}
public class Grid
{
public string total;
public string page;
public string records;
public Row[] rows;
}
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class WebServiceJqGrid : System.Web.Services.WebService
{
[WebMethod]
public Grid users()
{
// Send back the grid data in JSON format as follows (there is an additionnal 'd' parameter that is passed automatically by the webservice and that needs to be absorbed on the client side).
/* {
total: 'xxx',
page: 'yyy',
records: 'zzz',
rows : [
{id:'1', cell:['cell11', 'cell12', 'cell13', ...]},
{id:'2', cell:['cell21', 'cell22', 'cell23', ...]}
]
} */
Grid mg = new Grid();
mg.total = "1";
mg.page = "1";
MySqlConnection oConnexion = new MySqlConnection("server=**********;database=*******;user id=*******;pwd=**********");
MySqlCommand oCommandCount = new MySqlCommand("SELECT COUNT(*) FROM *****", oConnexion);
MySqlCommand oCommand = new MySqlCommand("SELECT * FROM *****", oConnexion);
oConnexion.Open();
MySqlDataReader drCount = oCommandCount.ExecuteReader();
while (drCount.Read())
{
mg.records = drCount.GetString(0);
mg.rows = new Row[drCount.GetInt32(0)];
}
drCount.Close();
MySqlDataReader drUsers = oCommand.ExecuteReader();
int i = 0;
while (drUsers.Read())
{
mg.rows[i] = new Row();
mg.rows[i].id = (i+1).ToString();
mg.rows[i].cell = new string[4];
mg.rows[i].cell[0] = drUsers.GetString("name");
mg.rows[i].cell[1] = drUsers.GetString("firstname");
mg.rows[i].cell[2] = drUsers.GetString("login");
mg.rows[i].cell[3] = drUsers.GetString("password");
i++;
}
drUsers.Close();
oConnexion.Close();
return mg;
}
}
}
And here is the server side code that is supposed to save the data on cell editing (This is a webservice too):
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Web.Script.Serialization;
using MySql.Data.MySqlClient;
namespace Test
{
public static class CUtils
{
public static HttpContext Context
{
get
{
return System.Web.HttpContext.Current;
}
}
public static NameValueCollection _GET
{
get
{
return CUtils.Context.Request.QueryString;
}
}
public static NameValueCollection _POST
{
get
{
return CUtils.Context.Request.Form;
}
}
}
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class WebService1 : System.Web.Services.WebService
{
[WebMethod]
public void users()
{
string id = CUtils._POST["id"];
string login = CUtils._POST["login"];
string password = CUtils._POST["password"];
MySqlConnection oConnexion = new MySqlConnection("server=**********;database=*********;user id=*****;pwd=*********");
MySqlCommand oCommand = new MySqlCommand();
oCommand.Connection = oConnexion;
if (password != null)
{
oCommand.CommandText = "UPDATE **** SET mdp=" + password + " WHERE id=" + id;
}
else if (login != null)
{
oCommand.CommandText = "UPDATE *** SET utilisateur=" + login + " WHERE id=" + id;
}
oConnexion.Open();
MySqlDataReader drEdit = oCommand.ExecuteReader();
drEdit.Close();
oConnexion.Close();
}
}
}
The thing is that when i try to edit a password in my jqGrid, the page is reloaded, but no modification has been done in the database.
I am missing something trivial?
18:02
27/04/2010
Offline
In fact even if I try to use a pager and row editing, it doesn't seem to work:
jQuery("#grid_users").jqGrid({
datatype: function() {
$(".loading").show();
$.ajax({
url: "WebServiceJqGridLoad.asmx/users",
data: "{}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
complete: function(jsondata, stat) {
if (stat == "success") {
var thegrid = $("#grid_users")[0];
thegrid.addJSONData(eval("(" + jsondata.responseText + ").d"));
$(".loading").hide();
}
else{
$(".loading").hide();
alert("Error with AJAX callback");
}
}
});
},
colNames:['Name', 'Firstname', 'login', 'Password'],
colModel :[
{name:'name',
index:'name',
editable:false
},
{name:'firstname',
index:'firstname',
editable:false
},
{name:'login',
index:'login',
editable:true
},
{name:'password',
index:'password',
editable:true
},
],
autowidth: true,
heigth: "auto",
mtype: 'POST',
gridview: true,
scroll: 1,
sortable: true,
sortname: 'name',
sortorder: 'desc',
hidegrid: false,
caption: "Users configuration",
pager: "#user_pager",
editurl: "WebServiceJqGridEdit.asmx/users",
});
jQuery("#grid_users").jqGrid('navGrid','#user_pager',{edit:true,add:true,del:true});
Server side for edit url:
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Web.Script.Serialization;
using MySql.Data.MySqlClient;
namespace Test
{
public static class CUtils
{
public static HttpContext Context
{
get
{
return System.Web.HttpContext.Current;
}
}
public static NameValueCollection _GET
{
get
{
return CUtils.Context.Request.QueryString;
}
}
public static NameValueCollection _POST
{
get
{
return CUtils.Context.Request.Form;
}
}
}
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class WebService1 : System.Web.Services.WebService
{
[WebMethod]
public void users()
{
MySqlConnection oConnexion = new MySqlConnection("server=*****;database=****;user id=****;pwd=******");
MySqlCommand oCommand = new MySqlCommand();
oCommand.Connection = oConnexion;
string oper = CUtils._POST["oper"];
if (String.IsNullOrEmpty(oper))
{
string id = CUtils._POST["id"];
string login = CUtils._POST["login"];
string password = CUtils._POST["password"];
if (!String.IsNullOrEmpty(password))
{
oCommand.CommandText = "UPDATE ***** SET password=" + password + " WHERE id=" + id;
}
else if (!String.IsNullOrEmpty(login))
{
oCommand.CommandText = "UPDATE **** SET login=" + login + " WHERE id=" + id;
}
oConnexion.Open();
MySqlDataReader drEdit = oCommand.ExecuteReader();
drEdit.Close();
oConnexion.Close();
}
else
{
switch (oper)
{
case "add":
string name = CUtils._POST["name"];
string firstname = CUtils._POST["firstname"];
string login = CUtils._POST["loginr"];
string password = CUtils._POST["password"];
oCommand.CommandText = "INSERT INTO ***** (name,firstname,login,password) VALUES (" + name + "," + firstname + "," + login + "," + password + ")";
oConnexion.Open();
MySqlDataReader drAdd = oCommand.ExecuteReader();
drAdd.Close();
oConnexion.Close();
break;
case "del":
string id = CUtils._POST["id"];
oCommand.CommandText = "DELETE FROM ***** WHERE id=" + id;
oConnexion.Open();
MySqlDataReader drDel = oCommand.ExecuteReader();
drDel.Close();
oConnexion.Close();
break;
}
}
}
}
}
So the probleme isn't only for cell editing
10:52
27/04/2010
Offline
Whatever i try, i can't seem to get it working with the editing.
Does somebody know whether it is possible or not to use a editurl or a cellurl when we load the data in the grid with a datatype as function?
11:05
10/04/2010
Offline
Hi Assiah,
Im programming with ASP.NET and SQL Server, but I am also using a function for datatype to load the grid, and have no problems using editurl for add/edit/deletes.
I can't help you any more than that i'm afraid - just confirming that editurl works for me when using a function for datatype
Cheers,
Michael
12:52
27/04/2010
Offline
Ok,
I have modified the code for the webservice in charge of saving the edited data and now i can add rows, edit rows and delete rows when using the pager and editurl property as posted in my previous post.
At least, everything works with the form editing predefined with the pager.
Now, I'll try again to use cell editing.
Here is my current code for the saving data webservice:
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Web.Script.Serialization;
using MySql.Data.MySqlClient;
namespace Test
{
public static class CUtils
{
public static HttpContext Context
{
get
{
return System.Web.HttpContext.Current;
}
}
public static NameValueCollection _GET
{
get
{
return CUtils.Context.Request.QueryString;
}
}
public static NameValueCollection _POST
{
get
{
return CUtils.Context.Request.Form;
}
}
}
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class WebService1 : System.Web.Services.WebService
{
[WebMethod]
public void users()
{
MySqlConnection oConnexion = new MySqlConnection("server=**;database=**;user id=**;pwd=**");
MySqlCommand oCommand = new MySqlCommand();
oCommand.Connection = oConnexion;
string oper = CUtils._POST["oper"];
string id = CUtils._POST["id"];
string name = CUtils._POST["name"];
string firstname = CUtils._POST["firstname"];
string login = CUtils._POST["login"];
string password = CUtils._POST["password"];
switch (oper)
{
case "edit":
oCommand.CommandText = "UPDATE ** SET name='" + name + "',firstname='" + firstname + "',login='" + login + "',password='" + password + "' WHERE id=" + id;
oConnexion.Open();
MySqlDataReader drEdit = oCommand.ExecuteReader();
drEdit.Close();
oConnexion.Close();
break;
case "add":
oCommand.CommandText = "INSERT INTO ** (name,firstname,login,password) VALUES ('" + name + "','" + firstname + "','" + login + "','" + password + "')";
oConnexion.Open();
MySqlDataReader drAdd = oCommand.ExecuteReader();
drAdd.Close();
oConnexion.Close();
break;
case "del":
oCommand.CommandText = "DELETE FROM ** WHERE id=" + id;
oConnexion.Open();
MySqlDataReader drDel = oCommand.ExecuteReader();
drDel.Close();
oConnexion.Close();
break;
}
}
}
}
13:47
27/04/2010
Offline
OK, even the cell editing works now.
Here is the webservice code I use for all the different editing method:
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Web.Script.Serialization;
using MySql.Data.MySqlClient;
namespace Test
{
public static class CUtils
{
public static HttpContext Context
{
get
{
return System.Web.HttpContext.Current;
}
}
public static NameValueCollection _GET
{
get
{
return CUtils.Context.Request.QueryString;
}
}
public static NameValueCollection _POST
{
get
{
return CUtils.Context.Request.Form;
}
}
}
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class WebService1 : System.Web.Services.WebService
{
[WebMethod]
public void users()
{
MySqlConnection oConnexion = new MySqlConnection("server=**;database=**;user id=**;pwd=**");
MySqlCommand oCommand = new MySqlCommand();
oCommand.Connection = oConnexion;
string oper = CUtils._POST["oper"];
string id = CUtils._POST["id"];
string name = CUtils._POST["name"];
string firstname = CUtils._POST["firstname"];
string login = CUtils._POST["login"];
string password = CUtils._POST["password"];
switch (oper)
{
case "edit":
if (!String.IsNullOrEmpty(name) && !String.IsNullOrEmpty(firstname)) // Form or row editing
{
oCommand.CommandText = "UPDATE ** SET name='" + name + "',firstname='" + firstname + "',login='" + login + "',password='" + password + "' WHERE id=" + id;
}
else // Cell editing
{
if (!String.IsNullOrEmpty(name))
{
oCommand.CommandText = "UPDATE ** SET nom='" + name + "' WHERE id=" + id;
}
else if (!String.IsNullOrEmpty(firstname))
{
oCommand.CommandText = "UPDATE ** SET prenom='" + firstname + "' WHERE id=" + id;
}
else if (!String.IsNullOrEmpty(login))
{
oCommand.CommandText = "UPDATE ** SET utilisateur='" + login + "' WHERE id=" + id;
}
else if (!String.IsNullOrEmpty(password))
{
oCommand.CommandText = "UPDATE ** SET mdp='" + password + "' WHERE id=" + id;
}
}
oConnexion.Open();
MySqlDataReader drEdit = oCommand.ExecuteReader();
drEdit.Close();
oConnexion.Close();
break;
case "add":
oCommand.CommandText = "INSERT INTO ** (name,firstname,login,password) VALUES ('" + name + "','" + firstname + "','" + login + "','" + password + "')";
oConnexion.Open();
MySqlDataReader drAdd = oCommand.ExecuteReader();
drAdd.Close();
oConnexion.Close();
break;
case "del":
oCommand.CommandText = "DELETE FROM ** WHERE id=" + id;
oConnexion.Open();
MySqlDataReader drDel = oCommand.ExecuteReader();
drDel.Close();
oConnexion.Close();
break;
}
}
}
}
I hope it will help somebody else someday!
Most Users Ever Online: 715
Currently Online:
62 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
OlegK: 1255
markw65: 179
kobruleht: 144
phicarre: 132
YamilBracho: 124
Renso: 118
Member Stats:
Guest Posters: 447
Members: 11373
Moderators: 2
Admins: 1
Forum Stats:
Groups: 1
Forums: 8
Topics: 10592
Posts: 31289
Newest Members:
, razia, Prankie, psky, praveen neelam, greg.valainis@pa-tech.comModerators: tony: 7721, Rumen[Trirand]: 81
Administrators: admin: 66
