DataTables warning (table id = 'tblRoles'): Requested unknown parameter '0' from the data source for
DataTables warning (table id = 'tblRoles'): Requested unknown parameter '0' from the data source for
jayasankarprk
Posts: 5Questions: 2Answers: 0
Hi,
I am getting below error while using datatable,
DataTables warning (table id = 'tblRoles'): Requested unknown parameter '0' from the data source for row 0
By code is below,
In aspx page,
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="DataTables_Dot_Net2010.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>
<style>
.widthFull
{
width: 100%;
}
.fontSize10
{
font-size: 10px;
}
.displayNone
{
display:none;
}
</style>
<link href="Content/DataTables/css/demo_table_jui.css" rel="stylesheet" />
<link href="Content/themes/base/jquery.ui.all.css" rel="stylesheet" />
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox runat="server" ID="txtRoleName"></asp:TextBox><input id="btnSearch" type="button" value="Search" style="width:65px;font-size: 11pt;" ></input>
</div>
<div>
<table id="tblRoles" class="widthFull fontsize10 displayNone">
<thead>
<tr>
<th>RoleID
</th>
<th>Role
</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</form>
<script src="Scripts/jquery-1.8.2.min.js" type="text/javascript"></script>
<script src="Scripts/jquery-ui-1.8.23.min.js" type="text/javascript"></script>
<script src="Scripts/jquery.json-2.3.min.js" type="text/javascript"></script>
<script src="Scripts/DataTables/jquery.dataTables.js" type="text/javascript"></script>
<script type="text/javascript" >
$(document).ready(function () {
$("#btnSearch").click(function (e) {
getRoles();
});
});
var getRoles = function () {
$("#tblRoles").dataTable({
"oLanguage": {
"sZeroRecords": "No records to display",
"sSearch": "Search on Roles"
},
"aLengthMenu": [[4, 25, 50, 100, 150, 250, 500, -1], [4, 25, 50, 100, 150, 250, 500, "All"]],
"iDisplayLength": 4,
"bSortClasses": false,
"bStateSave": false,
"bPaginate": true,
"bAutoWidth": false,
"bProcessing": true,
"bServerSide": true,
"bDestroy": true,
"sAjaxSource": "WebService1.asmx/GetRoles",
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"bDeferRender": true,
"fnServerParams": function (aoData) {
aoData.push({ "name": "roleName", "value": $("#txtRoleName").val() });
},
"fnServerData": function (sSource, aoData, fnCallback) {
$.ajax({
"dataType": 'json',
"contentType": "application/json; charset=utf-8",
"type": "GET",
"url": sSource,
"data": aoData,
"success":
function (msg) {
var json = jQuery.parseJSON(msg.d);
fnCallback(json);
$("#tblRoles").show();
}
});
}
});
}
</script>
</body>
</html>
In Webservice,
[WebMethod]
[ScriptMethod(UseHttpGet = true)]
[WebInvoke(ResponseFormat = WebMessageFormat.Json,
BodyStyle = WebMessageBodyStyle.Bare, Method = "GET")]
public string GetRoles()
{
string outputJson = string.Empty;
int sEcho = ToInt(HttpContext.Current.Request.Params["sEcho"]);
int iDisplayLength = ToInt(HttpContext.Current.Request.Params["iDisplayLength"]);
int iDisplayStart = ToInt(HttpContext.Current.Request.Params["iDisplayStart"]);
string rawSearch = HttpContext.Current.Request.Params["sSearch"];
string roleName = HttpContext.Current.Request.Params["roleName"];
var numberOfRowsToReturn = "";
numberOfRowsToReturn = iDisplayLength == -1 ? "TotalRows" : (iDisplayStart + iDisplayLength).ToString();
string query = @"SELECT *
FROM
(SELECT row_number() OVER (ORDER BY RoleID ASC) AS RowNumber
, *
FROM
(SELECT (SELECT count(Roles.RoleID)
FROM
Roles) AS TotalRows
, ( SELECT count( Roles.RoleID) FROM Roles WHERE RoleID LIKE '%'+ @searchText +'%' OR Role LIKE '%'+ @searchText +'%' OR @searchText IS NULL ) AS TotalDisplayRows
,Roles.RoleID
,Roles.Role
FROM
Roles WHERE RoleID LIKE '%'+ @searchText +'%' OR Role LIKE '%'+ @searchText +'%' OR @searchText IS NULL) RawResults) Results
WHERE
RowNumber BETWEEN @StartIndex AND @NumberOfRows";
DataTable dt = new DataTable();
var sb = new StringBuilder();
var totalRecords = "";
var totalDisplayRecords = "";
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["mainConnection"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@searchText", roleName);
cmd.Parameters.AddWithValue("@StartIndex", iDisplayStart + 1);
cmd.Parameters.AddWithValue("@NumberOfRows", numberOfRowsToReturn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
}
if (dt != null && dt.Rows.Count > 0)
{
totalRecords = dt.Rows[0]["TotalRows"].ToString();
totalDisplayRecords = dt.Rows[0]["TotalDisplayRows"].ToString();
}
DataView dv = new DataView();
DataTable dtNew = new DataTable();
dv = dt.DefaultView;
dtNew = dv.ToTable(true, "RoleID", "Role");
outputJson = JsonConvert.SerializeObject(dtNew);
sb.Clear();
sb.Append("{");
sb.Append(@"""sEcho"": ");
sb.AppendFormat(@"""{0}""", sEcho);
sb.Append(",");
sb.Append(@"""iTotalRecords"": ");
sb.Append(totalRecords);
sb.Append(",");
sb.Append(@"""iTotalDisplayRecords"": ");
sb.Append(totalDisplayRecords);
sb.Append(", ");
sb.Append(@"""aaData"": ");
sb.Append(outputJson);
sb.Append("}");
outputJson = sb.ToString();
return outputJson;
}
public static int ToInt(string toParse)
{
int result;
if (int.TryParse(toParse, out result)) return result;
return result;
}
My json is as below,
{"sEcho": "1","iTotalRecords": 10,"iTotalDisplayRecords": 10,
"aaData":
[
{"RoleID":1,"Role":"Admin"},
{"RoleID":2,"Role":"User"},
{"RoleID":3,"Role":"Manager"},
{"RoleID":4,"Role":"Affiliate"}
]
}
I am getting the error while executing the line fnCallback(json);
I reffer the below source having version vs2010
http://jquerydatatablessamp.codeplex.com/SourceControl/latest\
Please help me...
Thanks in advance...
This discussion has been closed.