JSON Alert - Formatting error

Lethiel

Hi,

I tried my self to implement a serverside solution for asp.net c#. I've seen a code example here in the forums and haven taken this. I have a valid Json string (checked by jsonlint.com), but unfortunately I get the error called "DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error."

There is another problem, which i think could be a reason for the JSON Alert. In the codebehind I'm trying to get the values for echo, displayLength and displayStart. But I have to set them manually otherwise i can't even compile the Project.. Can you see any mistakes in the code?

Thanks in advance
Kevin

consumer.aspx (front)
[code]





ConsumerId
First name
Last name
Status
Edit
Event








$(document).ready(function () {
$('#tblConsumers').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "consumer.aspx",
"sPaginationType": "full_numbers",
"sDom": '<"top"f>rt<"bottom"ilp><"clear">'
});
});

[/code]


consumer.aspx.cs (code behind)
[code]
//int echo = Int32.Parse(Request.Params["sEcho"]);
int echo = 3;
//int displayLength = Int32.Parse(Request.Params["iDisplayLength"]);
int displayLength = 10;
//int displayStart = Int32.Parse(HttpContext.Current.Request["iDisplayStart"]);
int displayStart = 1;
string search = Request.Params["sSearch"];

//SEARCH (filter) - build the where clause
StringBuilder sb = new StringBuilder();
string whereClause = string.Empty;
if (!String.IsNullOrEmpty(search))
{
sb.Append(" WHERE ConsumerId LIKE '%");
sb.Append(search);
sb.Append("%' OR Firstname LIKE '%");
sb.Append(search);
sb.Append("%' OR Lastname LIKE '%");
sb.Append(search);
sb.Append("%' OR ConsumerStatusId LIKE '%");
sb.Append(search);
sb.Append("%'");
whereClause = sb.ToString();
}

//ORDERING - build the order by clause
sb.Clear();
string orderByClause = string.Empty;
//Check which column is to be sorted by in which direction
for (int i = 0; i < 11; i++)
{
if (Request.Params["bSortable_" + i] == "true")
{
sb.Append(Request.Params["iSortCol_" + i]);
sb.Append(" ");
sb.Append(Request.Params["sSortDir_" + i]);
}
}
orderByClause = sb.ToString();
//Replace the number corresponding the column position by the corresponding name of the column in the database
if (!String.IsNullOrEmpty(orderByClause))
{
orderByClause = orderByClause.Replace("0", ", ConsumerId");
orderByClause = orderByClause.Replace("1", ", Firstname");
orderByClause = orderByClause.Replace("2", ", Lastname");
orderByClause = orderByClause.Replace("3", ", ConsumerStatusId");
//Eliminate the first comma of the variable "order"
orderByClause = orderByClause.Remove(0, 1);
}
else
orderByClause = "ConsumerId ASC";
orderByClause = "ORDER BY " + orderByClause;

//T-SQL query - ROW_NUMBER() is used for db side pagination
sb.Clear();
string query = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ({0}) AS RowNumber,* FROM ( SELECT ( SELECT COUNT(*) FROM CONSUMER {1} ) AS TotalDisplayRows, (SELECT COUNT(*) FROM CONSUMER) AS TotalRows,ConsumerId,Firstname,Lastname,ConsumerStatusId FROM CONSUMER {1} ) RawResults ) Results WHERE RowNumber BETWEEN {2} AND {3}";
query = String.Format(query, orderByClause, whereClause, displayStart + 1, displayStart + displayLength);

//Get result rows from DB
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString
);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
IDataReader rdrBrowsers = cmd.ExecuteReader();

/// JSON output - build JSON output from DB results
sb.Clear();
string outputJson = string.Empty;
int totalDisplayRecords = 0;
int totalRecords = 0;
while (rdrBrowsers.Read())
{
if (totalRecords == 0)
totalRecords = Int32.Parse(rdrBrowsers["TotalRows"].ToString());
if (totalDisplayRecords == 0)
totalDisplayRecords = Int32.Parse(rdrBrowsers["TotalDisplayRows"].ToString());
sb.Append("[");
sb.Append("\"" + rdrBrowsers["ConsumerId"] + "\",");
sb.Append("\"" + rdrBrowsers["Firstname"] + "\",");
sb.Append("\"" + rdrBrowsers["Lastname"] + "\",");
sb.Append("\"" + rdrBrowsers["ConsumerStatusId"] + "\"");
sb.Append("],");
}
outputJson = sb.ToString();
outputJson = outputJson.Remove(outputJson.Length - 1);
sb.Clear();

sb.Append("{");
sb.Append("\"sEcho\": ");
sb.Append(echo);
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();
[/code]

fbas

You should really check the json output in the debugger to get specifics and spot what's amiss. see this blog article:

http://tote-magote.blogspot.com/2011/08/debugger-basics-for-datatables.html

You might be putting un-escaped characters into your json, like backslashes or double quotes that are messing up the json parsing.

Lethiel

Hi, thanks four your answer.. I will read this article..

btw, this is my valid json output:

[code]
{
"sEcho": 3,
"iTotalRecords": 194225,
"iTotalDisplayRecords": 194225,
"aaData": [
[
"2285",
"Laurent",
"Jaccard",
"7"
],

....9 more of them...

]
}
[/code]

fbas

try setting iTotalDisplayRecords to 10 (displayLength)

could be an issue of the json parsing going out of bounds on the aaData array

Lethiel

Ok, i set iTotalDisplayRecords to the displaylength in the codebehind. the json output still is valid, but the same error occurs :-/

[code]
{
"sEcho": 3,
"iTotalRecords": 194225,
"iTotalDisplayRecords": 10,
"aaData": [
[
"2285",
"Laurent",
"Jaccard",
"7"
],

... 9 more...

]
}
[/code]

fbas

Can you provide a link to your project? There's obviously more info to this that we'll need in order to diagnose the issue.

GregP

iTotalDisplayRecords shouldn't be 10, unless you've done a search or other filtering that leaves 10 records. Pagination does not factor into the iTotalDisplayRecords integer.

Normally you should get a different error (not JSON formatting error), but I noticed that each aaData record only has 4 members; your HTML table has 6. If the extra 2 are "placeholder" columns for widgets and so forth, I suspect you need to use aoColumns or aoColumnDefs to handle the empty columns.

fbas

thanks, Greg. Guess I had that mixed up.

Lethiel

Well.. we're developing offline. But i can make a zipped File with the entire project, which you can download. Would that help you? Otherwise, i will have a look, if we can set it online..

fbas

Wouldn't help me because I wouldn't have your data, or if I did, I'd have to go through a lot of set-up to import data into MySQL, etc.

Lethiel

[quote]GregP said: Normally you should get a different error (not JSON formatting error), but I noticed that each aaData record only has 4 members; your HTML table has 6. If the extra 2 are "placeholder" columns for widgets and so forth, I suspect you need to use aoColumns or aoColumnDefs to handle the empty columns. [/quote]

I changed the table to 4 members. The same error still occurs.

At the moment, unfortunately i am not able to upload the website. But I made a screenshot which includes the output of firebug. Maybe you get any ideas what could be the problem.

http://picfront.de/d/8mIf

btw, how does jquery datatables recognize the json output? Maybe i have something wrong on the client site (html output, javascript function)

Thanks Kevin

allan

Your screenshot only shows when is being sent to the server - not what is sent back. Click the "Antwort" tab to see what is coming back - it won't be valid JSON if you are getting this error. You can check your JSON using http://jsonlint.com . DataTables uses the JSON parser which is built into jQuery, so it is rock solid :-)

Allan

Lethiel

Hi

I was a little bit stupid, the string was caught in the form element. You can see that on the next screenshot.

http://picfront.de/d/8mJ8

So i tried to change the type to POST with fnServerData, because my Website is actually in a "post" - form element (my fault, sorry!). The error doesn't occur anymore. But now the data isn't displayed. Below 2 Screenshots of Firebug. I think there's a problem with the values of displayStart, etc. which I mentioned in the first post. Eventually we're getting closer to the target :-)

http://picfront.de/d/8mJa (Response)
http://picfront.de/d/8mJb (POST Parameter)

Thanks for your patience.. :)

GregP

The first screenshot "(Response)" is just showing your script and some HTML. If that is truly the response (and you haven't just captured the wrong screenshot) this is the problem. You do not want a whole page as a response, you just want JSON. Your response should look something like this:

[code]
{ "iTotalRecords": 1, "iTotalDisplayRecords": 1,"aaData":[{ "label": "Volume Transferred", "hour": "0.000 Bytes", "day": "0.000 Bytes", "week": "551.952 GB" }, { "label": "Files Transferred", "hour": 0, "day": 0, "week": 9443 }, { "label": "Successful Transfers", "hour": 0, "day": 0, "week": 9442 }, { "label": "Failed Transfers", "hour": 0, "day": 0, "week": 1 }]}
[/code]

(note: this is a sample from one of my own tables, your aaData contents will be much different, but in a similar format. Depending on how you do it, you might not have key:value pairs.)

Lethiel

This is truly the right screenshot. In the codebehind File, i just give the json value to a string called "outputJson". I thought, jquery datatables would automatically take this string and fill it into the tags? I just want the json between the . I have no key: value pairs, because i define the headers in the tags.. Or am I totally wrong?

GregP

You don't need key-value pairs; that's an optional way of nesting the data that I happen to use.

But what I'm saying is that you are NOT outputting JSON. You are outputting a whole page. I can see the (etc) and the inline script, and all kinds of things that are just webpage contents, not JSON.

Lethiel

Well, I filled some td and tr tags in the json string.. now on the screenshot below you can see the json output with the rest of the characters.. Is this the right way? Besides it freezes while "Processing..." and the search is not working of course..

http://picfront.de/d/8mJx

GregP

This is not the right way. Your response must ONLY contain the JSON. It's not valid JSON otherwise. It must only return the part from {sEcho.. to ..]]}. Your response still has a tonne of HTML above and below the JSON.

There's no problem having tags INSIDE your JSON as part of the data fields, although judging by the screenshot, you shouldn't have them in your case. You are conflating two things: creation of an HTML table, and creation of JSON data. These things are not meant to be combined. You don't need to supply the tags, because DataTables will take care of all that. JSON should just have your data. I say you CAN have tags inside, but people do this for things like adding an or other HTML content in a cell. They do not use it to create the cell itself.

Your response is still broken until it sends ONLY the JSON.

Have a look at my sample a few above. That's not a snippet of "the JSON part of the response". That is the entire response. For further verification, when you're looking at the "response" sub-tab, copy all of that out and paste it into jsonlint.com. Only when it passes do you have a valid response.

Lethiel

Hi

I cleared now all the html stuff in the codebehind File and it works! :-) I don't know, why i didn't get that.. maybe too much of coding. Now i'm gonna modify the query a bit and do multiple text searching. I think, i will read some threads in the forums.

Thank you very much for your help and your patience.. I will ask my boss to make a donation.. :-)

btw: the solution works fine with 190'000 rows.. so if someone would like to have the full code, just write to me.. :)

GregP

Awesome!