JqGrid query RowNumber error

Question

hello all, i am bindind the jqgrid my controller class consists of the following code ,this code I had got from code,now it is giving error for RowNumber please tell why& how can I solve the problem.

public ActionResult GetGridData(string sidx, string sord, int page, int rows) { return Content(JsonHelper.JsonForJqgrid(GetDataTable(sidx,sord,page,rows), rows, GetTotalCount(), page), "application/json"); }

    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 Register_Id,FullName,CompanyName, EmployeeName, RegisterDate,
                        ROW_NUMBER() OVER (ORDER BY " + sidx + @" " + sord + @") AS RowNumber
                     FROM GP_Register
                    )
                    SELECT  Register_Id,FullName,CompanyName, EmployeeName, RegisterDate
                    FROM GP_Register
                    WHERE RowNumber BETWEEN " + startIndex + @" AND " + endIndex + @";";

        DataTable dt = new DataTable();
        SqlConnection conn = new SqlConnection("Data Source=MAXNET016;Initial Catalog=GuestPass;User ID=sa;Password=maxsql");
        conn.Open();
            SqlDataAdapter adap = new SqlDataAdapter(sql,conn);
        var rows=adap.Fill(dt);
        return dt;
    }

    public int GetTotalCount() {
        string sql = @"SELECT COUNT(*) FROM GP_Register WHERE Register=0";
        SqlConnection conn=null;
        try {
            conn = new SqlConnection("Data Source=MAXNET016;Initial Catalog=GuestPass;User ID=sa;Password=maxsql");
            SqlCommand comm = new SqlCommand(sql, conn);
            conn.Open();
            return (int)comm.ExecuteScalar();
        } catch {
        } finally {
            try {
                if (ConnectionState.Closed != conn.State) {
                    conn.Close();
                }
            }catch {
            }
        }
        return -1;
      }

And in the aspx page i have taken a jqgrid:

jQuery(document).ready(function() { jQuery("#list").jqGrid({ url: '/Home/GetGridData/', datatype: 'json', mtype: 'GET', colNames: ['FullName', 'CompanyName', 'EmployeeName', 'RegisterDate'], colModel: [ { name: 'FullName', index: 'FullName', width: 100, align: 'left'}, { name: 'CompanyName', index: 'CompanyName', width: 150, align: 'left' }, { name: 'EmployeeName', index: 'EmployeeName', width: 250, align: 'left', sortable: false , editable: true, edittype: "textarea", editoptions: { rows: "2", cols: "20"} }, { name: 'RegisterDate', index: 'RegisterDate', width: 150, align: 'left', sortable: false, editable: true, edittype: "text", editoptions: { size: 10, maxlength: 15} }, ], pager: jQuery('#pager'), rowNum: 10, rowList: [5, 10, 20, 50], sortname: 'Register_Id', sortorder: "asc", viewrecords: true, imgpath: '/scripts/themes/steel/images', caption: 'Customer Information' }).navGrid(pager, { edit: true, add: true, del: true, refresh: true, search: true }); }); jQuery("#list").jqGrid('filterToolbar'); function checkboxFormatter(el, cval, opts) { cval = cval + ""; cval = cval.toLowerCase(); var bchk = cval.search(/(false|0|no|off|n)/i) "); // var s; // s = (""); // cval = 1; // while (cval " + cval + ""); // cval++; // } // s = s + (""); // $(el).html(s); // var s; s = ("") cval = 1; while (cval " + cval + ""); cval++; } s = s + (""); $(el).html(s); }

Please help me Thanks Ritz

Answer 1

Sorry, that's a little too much code to wade through this late at night.

You might get some mileage from this though.