jqgrid in asp.net using json datatype

Question

hi i am jqgrid which is binded dynamically using json datatype.my problem is with pageing. I wrote the script for jqgrid in AddCategory.aspx and redirected this page to AddCategoryGrid.aspx where i wrote the code for binding jqGrid data. It is displaying all records in one page but not allowing the pagging.

The code in AddCategoryGrid.aspx public partial class AddCatogoryGrid : System.Web.UI.Page {

    string sidx;
    string sord;
    int page;
    int rows;
    DbProvider dbViewCat = new DbProvider();
    protected string connectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            sidx = Request.QueryString["sidx"];
            sord = Request.QueryString["sord"];
            page = Convert.ToInt32(Request.QueryString["page"]);
            rows = Convert.ToInt32(Request.QueryString["rows"]);        
            Response.Write(GetGridData(sidx, sord, page, rows));
            Response.End();

        }
    }

    public DataTable GetDataTable(string sidx, string sord, int page, int pageSize)
    {

        int startIndex = (page - 1) * pageSize;
        int endIndex = page * pageSize;
        DataTable dt = new DataTable();
        DataSet ds = dbViewCat.AdminSelectCat();
        dt = ds.Tables[0];
        return dt;
    }

    public int GetTotalCount()
    {
        DataSet count = dbViewCat.AdminSelectCount();
        int i = count.Tables[0].Rows.Count;
        return i;
    }
    public string GetGridData(string sidx, string sord, int page, int rows)
    {
        return JsonHelper.JsonForJqgrid(GetDataTable(sidx, sord, page, rows), rows, GetTotalCount(), page);
    }              
}

The script in AddCategory.aspx for JqGrid is

<script src="../js/jquery-1.3.2.js" type="text/javascript"></script>

<script src="../js/jquery-ui-1.8.2.custom.min.js" type="text/javascript"></script>
<script src="../gridjq/js/jquery.layout.js" type="text/javascript"></script>
<script src="../gridjq/js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script type="text/javascript">

    $.jgrid.no_legacy_api = true;
    $.jgrid.useJSON = true;
    function formateadorLink(cellvalue, options, rowObject) {
    //  return "<a href=AddSubCategory.aspx?CategoryID=" + cellvalue + " > " + "<img src='ThumbImage/1279090533_server_add.ico' border='0'/>" + "</a>";
   return "<a href=AddSubCategory.aspx?CategoryId=" + cellvalue + ">"+"<img src='ThumbImage/1279090533_server_add.ico' border='0'/>" + "</a>";

    } 

    function formateadorLinkStatus(cellvalue, options, rowObject) {

        if (cellvalue == 1) {
            return "<img src='../images/checked.gif' />";
        }
        else {
            return "<img src='../images/unchecked.gif' />";
        }
    }

    function formateadorLinkImage(cellvalue, options, rowObject) {
        return "<img src=CategoryThumbImage/" + cellvalue + " />";
    }       


    jQuery(document).ready(function() {
    jQuery("#list47").jqGrid({
    url: 'AddCatogoryGrid.aspx',
        datatype: "json",
        id: "CategoryName",
        colNames: ["Image", "Category", 'Status', 'Add subcategory'],
        colModel: [

    { name: 'Image', index: 'Image', width: 100, align: "center", formatter: 'showlink', formatter: formateadorLinkImage },
        { name: 'CategoryName', index: 'CategoryName', width: 150, align: 'left', sortable: true, sorttype: 'text' },
     { name: 'Status', index: 'Status', width: 100, align: "right", sortable: false, align: "center", formatter: formateadorLinkStatus },
         { name: 'CategoryId', index: 'CategoryId', width: 100, align: "center", formatter: 'showlink', formatter: formateadorLink },
         ],
        height: 280,
        width: 650,
        //autowidth: true,
        mtype: "GET",
        pager: '#plist47',
        rowNum: 10,
        rowList: [5, 10, 15,20,25],
        repeatitems: false,
        viewrecords: true,
        sortname: 'CategoryName',
        viewrecords: true,
        sortorder: "desc",
        gridview: true,
        imgpath: '/Scripts/themes/redmond/images'
        });        

    });          
</script>
<script src="../gridjq/js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script src="../gridjq/js/jquery.tablednd.js" type="text/javascript"></script>
<script src="../gridjq/js/jquery.contextmenu.js" type="text/javascript"></script>
<script src="../gridjq/js/ui.multiselect.js" type="text/javascript"></script>
<script src="../gridjq/js/grid.celledit.js" type="text/javascript"></script>
<script src="../gridjq/js/grid.inlinedit.js" type="text/javascript"></script>
<script src="../gridjq/js/grid.import.js" type="text/javascript"></script>
<script src="../gridjq/js/grid.formedit.js" type="text/javascript"></script>
<script src="../gridjq/js/grid.custom.js" type="text/javascript"></script>
<script src="../gridjq/js/grid.setcolumns.js" type="text/javascript"></script>
<script src="../gridjq/js/json2.js" type="text/javascript"></script>
<script src="../gridjq/js/jquery.fmatter.js" type="text/javascript"></script>
<script src="../gridjq/js/jquery.cookie.js" type="text/javascript"></script>
<script src="../gridjq/js/grid.tbltogrid.js" type="text/javascript"></script>

The Helper class i used is

public class JsonHelper
{
    public static string JsonForJqgrid(DataTable dt, int pageSize, int totalRecords, int page)
    {
        int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
        StringBuilder jsonBuilder = new StringBuilder();
        jsonBuilder.Append("{");
        jsonBuilder.Append("\"total\":" + totalPages + ",\"page\":" + page + ",\"records\":" + (totalRecords));
        bool First = true;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            if (First)
            {
                jsonBuilder.Append(",\"rows\":[");
                First = false;
            }
            jsonBuilder.Append("{\"i\":" + (i) + ",\"cell\":[");
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                jsonBuilder.Append("\"");
                jsonBuilder.Append(dt.Rows[i][j].ToString());
                jsonBuilder.Append("\",");
            }
            jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("]},");
        }
        if (!First)
        {
            jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("]");
        }
        jsonBuilder.Append("}");
        return jsonBuilder.ToString();
    }     
}

Can anybody find why the pagging is not working properly.

ruakh · Accepted Answer · 2012-03-12 23:26:23Z

1

public DataTable GetDataTable(string sidx, string sord, int page, int pageSize) 
{        
    int startIndex = (page-1) * pageSize;    
    int endIndex = page * pageSize;     
    string sql = @"WITH PAGED_CUSTOMERS  AS  (SELECT  CustomerID, ContactName, Address, City, PostalCode, ROW_NUMBER() OVER (ORDER BY " + sidx + @" " + sord + @") AS RowNumber  FROM CUSTOMERS) SELECT CustomerID, ContactName, Address, City, PostalCode FROM PAGED_CUSTOMERS WHERE RowNumber BETWEEN " + startIndex + @" AND " + endIndex + @";"; 
    DataTable dt = new DataTable(); 
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["mainConnection"].ConnectionString);  
    SqlDataAdapter adap = new SqlDataAdapter(sql,conn);  
    var rows=adap.Fill(dt);      
    return dt;
}

edited Mar 12, 2012 at 23:26

ruakh

185k29 gold badges292 silver badges324 bronze badges

answered Feb 3, 2011 at 6:51

Tonmoy

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

jqgrid in asp.net using json datatype

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related