JQGrid does not display data
I have created both client and server side for JQgrid and ASP.net. The grid is displayed but with no data. I could not see the result. The grid displayed but no data.
Server side coding
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Script.Serialization;
namespace sample
{
public struct JQGridResults
{
public int page;
public int total;
public int records;
public JQGridRow[] rows;
}
public struct JQGridRow
{
public int id;
public string[] cell;
}
[Serializable]
public class User
{
public int UserID
{ get; set; }
public string UserName
{ get; set; }
public string FirstName
{ get; set; }
public string MiddleName
{ get; set; }
public string LastName
{ get; set; }
public string EmailID
{ get; set; }
/// <summary>
/// Summary description for jqgridhandler
/// </summary>
public class jqGridHandler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
HttpRequest request = context.Request;
HttpResponse response = context.Response;
string _search = request["_search"];
string numberOfRows = request["rows"];
string pageIndex = request["page"];
string sortColumnName = request["sidx"];
string sortOrderBy = request["sord"];
int totalRecords;
Collection<User> users = GetUsers(numberOfRows, pageIndex, sortColumnName, sortOrderBy, out totalRecords);
string output = BuildJQGridResults(users, Convert.ToInt32(numberOfRows), Convert.ToInt32(pageIndex), Convert.ToInt32(totalRecords));
response.Write(output);
}
private string BuildJQGridResults(Collection<User> users, int numberOfRows, int pageIndex, int totalRecords)
{
JQGridResults result = new JQGridResults();
List<JQGridRow> rows = new List<JQGridRow>();
foreach (User user in users)
{
JQGridRow row = new JQGridRow();
row.id = user.UserID;
row.cell = new string[6];
row.cell[0] = user.UserID.ToString();
row.cell[1] = user.UserName;
row.cell[2] = user.FirstName;
row.cell[3] = user.MiddleName;
row.cell[4] = user.LastName;
row.cell[5] = user.EmailID;
rows.Add(row);
}
result.rows = rows.ToArray();
result.page = pageIndex;
result.total = tot开发者_StackOverflowalRecords / numberOfRows;
result.records = totalRecords;
return new JavaScriptSerializer().Serialize(result);
}
private Collection<User> GetUsers(string numberOfRows, string pageIndex, string sortColumnName, string sortOrderBy, out int totalRecords)
{
Collection<User> users = new Collection<User>();
string connectionString = "";
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = "select * from tblusers" ;
command.CommandType = CommandType.Text; // StoredProcedure;
SqlParameter paramPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
paramPageIndex.Value = Convert.ToInt32(pageIndex);
command.Parameters.Add(paramPageIndex);
SqlParameter paramColumnName = new SqlParameter("@SortColumnName", SqlDbType.VarChar, 50);
paramColumnName.Value = sortColumnName;
command.Parameters.Add(paramColumnName);
SqlParameter paramSortorderBy = new SqlParameter("@SortOrderBy", SqlDbType.VarChar, 4);
paramSortorderBy.Value = sortOrderBy;
command.Parameters.Add(paramSortorderBy);
SqlParameter paramNumberOfRows = new SqlParameter("@NumberOfRows", SqlDbType.Int);
paramNumberOfRows.Value = Convert.ToInt32(numberOfRows);
command.Parameters.Add(paramNumberOfRows);
SqlParameter paramTotalRecords = new SqlParameter("@TotalRecords", SqlDbType.Int);
totalRecords = 0;
paramTotalRecords.Value = totalRecords;
paramTotalRecords.Direction = ParameterDirection.Output;
command.Parameters.Add(paramTotalRecords);
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
User user;
while (dataReader.Read())
{
user = new User();
user.UserID = (int)dataReader["UserID"];
user.UserName = Convert.ToString(dataReader["UserName"]);
user.FirstName = Convert.ToString(dataReader["FirstName"]);
user.MiddleName = Convert.ToString(dataReader["MiddleName"]);
user.LastName = Convert.ToString(dataReader["LastName"]);
user.EmailID = Convert.ToString(dataReader["EmailID"]);
users.Add(user);
}
}
totalRecords = (int)paramTotalRecords.Value;
}
return users;
}
}
public bool IsReusable
{
// To enable pooling, return true here.
// This keeps the handler in memory.
get { return false; }
}
}
}
}
Client side
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="sample.aspx.cs" Inherits="sample.WebForm1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<!-- The jQuery UI theme that will be used by the grid -->
<link rel="stylesheet" type="text/css" media="screen" href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.11/themes/redmond/jquery-ui.css" />
<!-- The jQuery UI theme extension jqGrid needs -->
<link rel="stylesheet" type="text/css" media="screen" href="themes/ui.jqgrid.css" />
<!-- jQuery runtime minified -->
<script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.5.2.min.js" type="text/javascript"></script>
<!-- The localization file we need, English in this case -->
<script src="js/trirand/i18n/grid.locale-en.js"type="text/javascript"></script>
<!-- The jqGrid client-side javascript -->
<script src="js/trirand/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
$("#UsersGrid").jqGrid({
url: 'jqGridHandler.ashx',
datatype: 'json',
height: 250,
colNames: ['UserID', 'UserName', 'FirstName', 'MiddleName', 'LastName', 'EmailID'],
colModel: [
{ name: 'UserID', index: 'UserID', width: 100, sortable: true },
{ name: 'UserName', width: 100, sortable: true },
{ name: 'FirstName', width: 100, sortable: true },
{ name: 'MiddleName', width: 100, sortable: true },
{ name: 'LastName', width: 100, sortable: true },
{ name: 'EmailID', width: 150, sortable: true }
],
rowNum: 10,
rowList: [10, 20, 30],
pager: '#UsersGridPager',
sortname: 'UserID',
viewrecords: true,
sortorder: 'asc',
caption: 'JSON Example'
});
$("#UsersGrid").jqGrid('navGrid', '#UsersGridPager', { edit: false, add: false, del: false });
});
</script>
</head>
<body>
<%--<form id="HtmlForm" runat="server">--%>
<table id="UsersGrid" cellpadding="0" cellspacing="0"></table>
<div id="UsersGridPager"></div>
<%-- </form>--%>
</body>
</html>
What is wrong with it? Data is not displayed. Thank you
Updated
$(document).ready(function () {
$.ajax({
type: 'POST',
contentType: "application/json; charset=utf-8",
url: "jqGridHandler.ashx",
datatype: 'json',
success: function (result) {
var JQResult = JSON.parse(result);
colD = JQResult.colData;
colN = JQResult.colNames;
var colM = JQResult.colModel;
alert(result.colModel);
jQuery("#UsersGrid").jqGrid(
{
jsonReader: { repeatitems: false, cell: "", id: "0" },
url: "jqGridHandler.ashx",
datatype: 'json',
mtype: 'POST',
data: colD,
ColNames: colN,
ColModel: ColM,
height: "auto",
gridview: true,
Pager: '#UsersGrid',
rowNum: 5,
rowList: [5, 10, 20, 50],
viewrecords: true,
loadComplete: function (data) {
alert('loaded completely');
},
loadError: function () {
alert('error');
}
});
},
error: function (x, e) {
alert(x.readyState + ' ' + x.status + e.msg);
}
});
});
Updated vesion for dynamic one
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Script.Serialization;
namespace jqGridInWebForm {
/// <summary>
/// Summary description for jqGridHandler
/// </summary>
public struct JQGridResults {
public int page;
public int total;
public int records;
public JQGridRow[] rows;
}
public struct JQGridRow {
public int id;
public string[] cell;
}
public enum GridType
{
GRID_TYPE_TEXT,
GRID_TYPE_DATE,
GRID_TYPE_INT,
GRID_TYPE_DOUBLE
}
[Serializable]
public class User {
public int UserID { get; set; }
public string UserName { get; set; }
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public string EmailID { get; set; }
public string Phone { get; set; }
}
public class clsGridData
{
public List<string> _Columns = new List<string>();
public List<string[]> _Cells = new List<string[]>();
public void InitFields(string P_SQL, string P_TYPE)
{
int _count;
string connectionString = "";//P_SQL
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = "select * from tblusers";
command.CommandType = CommandType.Text; // StoredProcedure;
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
string MyField;
for (_count = 0; _count < dataReader.FieldCount; _count++)
{
MyField = dataReader.GetName(_count);
_Columns.Add(MyField);
}
while (dataReader.Read())
{
string[] MyCell = new string[dataReader.FieldCount];
for (_count = 0; _count < dataReader.FieldCount; _count++)
{
MyCell[_count] = Convert.ToString(dataReader[_count]);
}
_Cells.Add(MyCell);
}
}
}
}
}
}
/// <summary>
/// Summary description for jqgridhandler
/// </summary>
public class jqGridHandler: IHttpHandler
{
public void ProcessRequest(HttpContext context) {
HttpRequest request = context.Request;
HttpResponse response = context.Response;
string _search = request["_search"];
string numberOfRows = request["rows"];
string pageIndex = request["page"];
string sortColumnName = request["sidx"];
string sortOrderBy = request["sord"];
//int totalRecords;
//List<User> users = GetUsers(numberOfRows, pageIndex, sortColumnName, sortOrderBy, out totalRecords);
clsGridData i_grid_data = new clsGridData();
i_grid_data.InitFields("","");
string output = BuildJQGridResults(i_grid_data, Convert.ToInt32(numberOfRows), Convert.ToInt32(pageIndex));
//string output = BuildJQGridResults(users, Convert.ToInt32(numberOfRows), Convert.ToInt32(pageIndex), Convert.ToInt32(totalRecords));
response.Write (output);
}
private string BuildJQGridResults(clsGridData P_GRID_DATA, int numberOfRows, int pageIndex)
{
JQGridResults result = new JQGridResults();
List<JQGridRow> rows = new List<JQGridRow>();
int _count;
foreach (String[] Cells in P_GRID_DATA._Cells)
{
JQGridRow row = new JQGridRow();
row.cell = new string[Cells.GetUpperBound(0)];
//row.id = user.UserID;
for (_count = 0;_count< Cells.GetUpperBound(0); _count++)
{
row.cell[_count] = Cells[_count].ToString();
}
rows.Add(row);
}
result.rows = rows.ToArray();
result.page = pageIndex;
result.total = (P_GRID_DATA._Cells.Count + numberOfRows - 1) / numberOfRows;
result.records = P_GRID_DATA._Cells.Count;
return new JavaScriptSerializer().Serialize(result);
}
//private string BuildJQGridResults(List<User> users, int numberOfRows, int pageIndex, int totalRecords) {
// JQGridResults result = new JQGridResults ();
// List<JQGridRow> rows = new List<JQGridRow> ();
// foreach (User user in users)
// {
// JQGridRow row = new JQGridRow ();
// row.id = user.UserID;
// row.cell = new string[6];
// row.cell[0] = user.UserID.ToString ();
// row.cell[1] = user.UserName;
// row.cell[2] = user.FirstName;
// row.cell[3] = user.MiddleName;
// row.cell[4] = user.LastName;
// row.cell[5] = user.EmailID;
// row.cell[6] = user.Phone ;
// rows.Add (row);
// }
// result.rows = rows.ToArray ();
// result.page = pageIndex;
// result.total = (totalRecords + numberOfRows - 1) / numberOfRows;
// result.records = totalRecords;
// return new JavaScriptSerializer ().Serialize (result);
//}
private List<User> GetDummyUsers(string numberOfRows, string pageIndex, string sortColumnName, string sortOrderBy, out int totalRecords)
{
var data = new List<User>
{
new User(){EmailID = "test@microsoft.com", FirstName = "John", LastName = "Araya", UserID = 1, UserName = "Efrem"}
};
totalRecords = data.Count;
return data;
}
private List<User> GetUsers(string numberOfRows, string pageIndex, string sortColumnName, string sortOrderBy, out int totalRecords)
{
List<User> users = new List<User> ();
string connectionString = "";
using (SqlConnection connection = new SqlConnection (connectionString))
{
using (SqlCommand command = new SqlCommand ())
{
command.Connection = connection;
command.CommandText = "select * from tblusers";
command.CommandType = CommandType.Text; // StoredProcedure;
//SqlParameter paramPageIndex = new SqlParameter ("@PageIndex", SqlDbType.Int);
//paramPageIndex.Value = Convert.ToInt32 (pageIndex);
//command.Parameters.Add (paramPageIndex);
//SqlParameter paramColumnName = new SqlParameter ("@SortColumnName", SqlDbType.VarChar, 50);
//paramColumnName.Value = sortColumnName;
//command.Parameters.Add (paramColumnName);
//SqlParameter paramSortorderBy = new SqlParameter ("@SortOrderBy", SqlDbType.VarChar, 4);
//paramSortorderBy.Value = sortOrderBy;
//command.Parameters.Add (paramSortorderBy);
//SqlParameter paramNumberOfRows = new SqlParameter ("@NumberOfRows", SqlDbType.Int);
//paramNumberOfRows.Value = Convert.ToInt32 (numberOfRows);
//command.Parameters.Add (paramNumberOfRows);
//SqlParameter paramTotalRecords = new SqlParameter ("@TotalRecords", SqlDbType.Int);
//totalRecords = 0;
//paramTotalRecords.Value = totalRecords;
//paramTotalRecords.Direction = ParameterDirection.Output;
//command.Parameters.Add (paramTotalRecords);
connection.Open ();
using (SqlDataReader dataReader = command.ExecuteReader ())
{
User user;
while (dataReader.Read ())
{
user = new User ();
user.UserID = (int)dataReader["UserID"];
user.UserName = Convert.ToString (dataReader["UserName"]);
user.FirstName = Convert.ToString (dataReader["FirstName"]);
user.MiddleName = Convert.ToString (dataReader["MiddleName"]);
user.LastName = Convert.ToString (dataReader["LastName"]);
user.EmailID = Convert.ToString (dataReader["EmailID"]);
users.Add (user);
}
}
//totalRecords = (int)paramTotalRecords.Value;
}
totalRecords = 0;
return users;
}
}
public bool IsReusable {
// To enable pooling, return true here.
// This keeps the handler in memory.
get { return false; }
}
}
}
I have modified the source, now records are displayed very smoothly but the only problem is that the search is not working, can you please have a look? My Codes are given below:
The aspx page:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="JQGrid.aspx.cs" Inherits="JQGrid" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<%--<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.8/themes/ui-darkness/jquery-ui.css" type="text/css" media="all" />--%>
<link rel="stylesheet" type="text/css" media="screen" href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/redmond/jquery-ui.css" />
<link href="jqScripts/css/ui.jqgrid.css" rel="stylesheet" type="text/css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js" type="text/javascript"></script>
<script src="jqScripts/js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="jqScripts/js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
$("#UsersGrid").jqGrid({
url: 'jqGridHandler.ashx',
datatype: 'json',
height: 250,
colNames: ['CustomerID', 'CompanyName', 'ContactName', 'ContactTitle', 'Address', 'City', 'PostalCode', 'Country'],
colModel: [
{ name: 'CustomerID', index: 'CustomerID', width: 75, sortable: true },
{ name: 'CompanyName', width: 100, sortable: true },
{ name: 'ContactName', width: 100, sortable: true },
{ name: 'ContactTitle', width: 100, sortable: true },
{ name: 'Address', width: 100, sortable: true },
{ name: 'City', width: 100, sortable: true },
{ name: 'PostalCode', width: 100, sortable: true },
{ name: 'Country', width: 150, sortable: true }
],
rowNum: 10,
rowList: [10, 20, 30],
pager: '#UsersGridPager',
sortname: 'CustomerID',
viewrecords: true,
sortorder: 'asc',
caption: 'My Data'
});
$("#UsersGrid").jqGrid('navGrid', '#UsersGridPager', { edit: false, add: false, del: false });
});
</script>
</head>
<body>
<form id="HtmlForm" runat="server">
<table id="UsersGrid" cellpadding="0" cellspacing="0">
<div id="UsersGridPager">
</div>
</table>
</form>
</body>
</html>
and the ashx:
<%@ WebHandler Language="C#" Class="jqGridHandler" %>
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Script.Serialization;
public class jqGridHandler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
HttpRequest request = context.Request;
HttpResponse response = context.Response;
string _search = request["_search"];
string numberOfRows = request["rows"];
//string numberOfRows = "10";
string pageIndex= request["page"];
string sortColumnName= request["sidx"];
string sortOrderBy = request["sord"];
int totalRecords;
//public DataTable GetDataTable(string sidx, string sord, int page, int pageSize)
Collection<User> users = GetUsers(numberOfRows, pageIndex, sortColumnName, sortOrderBy, out totalRecords, _search);
string output = BuildJQGridResults(users, Convert.ToInt32(numberOfRows), Convert.ToInt32(pageIndex), Convert.ToInt32(totalRecords));
response.Write(output);
}
private string BuildJQGridResults(Collection<User> users,int numberOfRows, int pageIndex,int totalRecords)
{
JQGridResults result = new JQGridResults();
List<JQGridRow> rows = new List<JQGridRow>();
foreach (User user in users)
{
JQGridRow row = new JQGridRow();
row.id = user.CustomerID;
row.cell = new string[8];
row.cell[0] = user.CustomerID;
row.cell[1] = user.CompanyName;
row.cell[2] = user.ContactName;
row.cell[3] = user.ContactTitle;
row.cell[4] = user.Address;
row.cell[5] = user.City;
row.cell[6] = user.PostalCode;
row.cell[7] = user.Country;
rows.Add(row);
}
result.rows = rows.ToArray();
result.page = pageIndex;
result.total = totalRecords / numberOfRows;
result.records = totalRecords;
return new JavaScriptSerializer().Serialize(result);
}
private Collection<User> GetUsers(string numberOfRows, string pageIndex, string sortColumnName, string sortOrderBy, out int totalRecords, string _search)
{
Collection<User> users = new Collection<User>();
string connectionString = "Data Source=ritetechno\\sqlexpress;Initial Catalog=Northwind;Integrated Security=True";
//<add name="constr" connectionString="Data Source=Abdul-THINK;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
int numRows=Convert.ToInt32(numberOfRows)*(Convert.ToInt32(pageIndex));
int excluderows=Convert.ToInt32(numberOfRows)*((Convert.ToInt32(pageIndex)-1));
command.Connection = connection;
command.CommandText = "SELECT TOP " + numRows + " CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country FROM Customers WHERE CustomerID NOT IN (SELECT TOP " + excluderows +" CustomerID FROM Customers)";
command.CommandType = CommandType.Text;
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
User user;
while (dataReader.Read())
{
user = new User();
user.CustomerID = Convert.ToString(dataReader["CustomerID"]);
user.CompanyName = Convert.ToString(dataReader["CompanyName"]);
user.ContactName = Convert.ToString(dataReader["ContactName"]);
user.ContactTitle = Convert.ToString(dataReader["ContactTitle"]);
user.Address = Convert.ToString(dataReader["Address"]);
user.City = Convert.ToString(dataReader["City"]);
user.PostalCode = Convert.ToString(dataReader["PostalCode"]);
user.Country = Convert.ToString(dataReader["Country"]);
users.Add(user);
}
}
string cmdTotRec = "SELECT COUNT(*) FROM Customers";
SqlCommand chkTotRec = new SqlCommand(cmdTotRec, connection);
totalRecords = Convert.ToInt32(chkTotRec.ExecuteScalar().ToString());
connection.Close();
}
return users;
}
}
public bool IsReusable
{
// To enable pooling, return true here.
// This keeps the handler in memory.
get { return false; }
}
public struct JQGridResults
{
public int page;
public int total;
public int records;
public JQGridRow[] rows;
}
public struct JQGridRow
{
public string id;
public string[] cell;
}
[Serializable]
public class User
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
}
}
Your main error is that you defined class jqGridHandler (public class jqGridHandler : IHttpHandler) inside of the User class. You should define it on the top level.
I don't comment the code of GetUsers included string connectionString = "" for example. You should know how you access your database.
Another minor error is the line
result.total = totalRecords / numberOfRows;
which should be fixed to
result.total = (totalRecords + numberOfRows - 1) / numberOfRows;
It is not so important, but I would prefer to use List<User> instead of Collection<User>.
After the changes the code will work (you can download from here the demo project which I used). I recommend you additionally implement loadError event handle. See the answer as an example.
加载中,请稍侯......
精彩评论