jQuery Grid Plugin - jqGrid - Topic: Cell Editing in asp.net webforms with a webservice

Asshiah on Cell Editing in asp.net webforms with a webservice

Wed, 28 Apr 2010 13:47:50 +0300

OK, even the cell editing works now.

Here is the webservice code I use for all the different editing method:

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()
        {
            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!

Asshiah on Cell Editing in asp.net webforms with a webservice

Wed, 28 Apr 2010 12:52:04 +0300

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:

namespace Test
{
public static class CUtils
{

        public static NameValueCollection _POST
        {
            get
            {
                return CUtils.Context.Request.Form;
            }
        }
    }

michaelg on Cell Editing in asp.net webforms with a webservice

Wed, 28 Apr 2010 11:05:56 +0300

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

Asshiah on Cell Editing in asp.net webforms with a webservice

Wed, 28 Apr 2010 10:52:23 +0300

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?

Asshiah on Cell Editing in asp.net webforms with a webservice

Tue, 27 Apr 2010 18:02:01 +0300

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:

namespace Test
{
public static class CUtils
{

        public static NameValueCollection _POST
        {
            get
            {
                return CUtils.Context.Request.Form;
            }
        }
    }

        [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

Asshiah on Cell Editing in asp.net webforms with a webservice

Tue, 27 Apr 2010 14:17:02 +0300

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:

Here is my code for the server side that enables the loading of the data from the database (This is a webservice):

using System;
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):

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?