Error with C#: This XML file does not appear to have any style information associated with it.

jlamato

Hello everyone,
I am currently trying to use DataTables in an application where I work. I am building the application using C# and ASP.NET, and am trying to make a DataTables user control that I can just add to any web page. I have most of it working, but I keep coming across this error whenever I try and generate the tables. It just hangs in the processing loop, and I had to open up the console in Chrome to find this error:

[quote]
This XML file does not appear to have any style information associated with it. The document tree is shown below.

{"sEcho": "1","iTotalRecords": 8,"iTotalDisplayRecords": 8, "aaData": [ ALL OF MY TABLE ROWS ARE HERE ARE IN HERE...]}
[/quote]

Here is a link to a zipped folder of the solution: https://dl.dropboxusercontent.com/u/46643291/DataTableCSharp.zip. It is a VS2012 solution.
Here are the relevant pieces of code:

JQueryDatatable.ascx.cs:
[code]using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace DataTableCSharp {
public partial class JQueryDataTable : System.Web.UI.UserControl {
protected void Page_Load(object sender, EventArgs e) {

Table HeaderTable = new Table();
HeaderTable.ID = "sampleTable";
HeaderTable.CssClass = "widthFull fontsize10 displayNone";

string tableName = "Table";
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(string.Empty) + @"\App_Data\TestDB.accdb";
string sqlString = "SELECT Table.* FROM [" + tableName +"]";
OleDbConnection connect = new OleDbConnection(conString);
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlString, connect);
adapter.Fill(ds);
CreateHTMLTable(ds.Tables[0]);

}


private void WriteComment(String comment, HtmlTextWriter writer) {
writer.Write("\n<!-- " + comment + " -->\n");
}

public void CreateHTMLTable(DataTable table) {
Table htable = new Table();
htable.Attributes.Add("class", "widthFull fontsize10 displayNone");
htable.ID = "sampleTable";
TableRow row = new TableRow();
int count = 1;
row.TableSection = TableRowSection.TableHeader;
foreach (DataColumn column in table.Columns) {
TableCell cell = new TableCell();
cell.Text = column.ColumnName;
row.Cells.Add(cell);
if (count++ >= 5) break;
}
htable.Rows.Add(row);
tableDiv.Controls.Add(htable);
}
}
}
[/code]

JQueryDataTable.ascx:
[code]
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="JQueryDataTable.ascx.cs" Inherits="DataTableCSharp.JQueryDataTable" %>



.widthFull
{
width: 100%;
}

.fontSize10
{
font-size: 10px;
}
.displayNone
{
display:none;
}











Click to load table
LoadTable








$(document).ready(function () {
$("#get").click(function (e) {

e.preventDefault();
getTable();

});
});


var getTable = function () {

$("#FeaturedContent_DataTable_sampleTable").dataTable({
"oLanguage": {
"sZeroRecords": "No records to display",
"sSearch": "Search"
},
"aLengthMenu": [[2, 5, 10, 20, 25, 50, 100, 150, 250, 500, -1], [2, 5, 10, 20, 25, 50, 100, 150, 250, 500, "All"]],
"iDisplayLength": 20,

"bSortClasses": false,
"sScrollY": 800,
"sScrollX": 1500,

"bScrollCollapse": true,
"bStateSave": false,
"bPaginate": true,
"bAutoWidth": true,
"bProcessing": true,
"bServerSide": true,
"bDestroy": true,
"sAjaxSource": "TableService.asmx/GetItems",
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sServerMethod": "GET",
"bDeferRender": true,
"fnServerData": function (sSource, aoData, fnCallback) {
$.ajax({

"dataType": 'json',
"contentType": "application/json",
"type": "GET",
"url": sSource,
"data": aoData,
"success":
function (msg) {
var json = jQuery.parseJSON(msg.d);
fnCallback(json);
$("#FeaturedContent_DataTable_sampleTable").show();
}
});

}
});
}

[/code]

More below...

jlamato

TableService.asmx.cs:
[code]
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Script.Services;
using System.Web.Services;
using System.Xml;

namespace DataTableCSharp {
///
/// Summary description for TableService
///
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class TableService : System.Web.Services.WebService {
[WebMethod]
[ScriptMethod(UseHttpGet = true)]



public string GetItems() {
string tableName = "Table";
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(string.Empty) + @"\App_Data\TestDB.accdb";
string sqlString = "SELECT Table.* FROM [" + tableName+"]";
OleDbConnection connect = new OleDbConnection(conString);
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlString, connect);
adapter.Fill(ds);
string query = GenerateQueryString(ds.Tables[0], tableName);

DataSet NewSet = new DataSet();
adapter = new OleDbDataAdapter(query, connect);
adapter.Fill(NewSet);

string JSON = DataTableToJSON(NewSet.Tables[0]);
return JSON;

}


public static int ToInt(string toParse) {
int result;
if (int.TryParse(toParse, out result)) return result;

return result;
}

public string GenerateQueryString(DataTable table, string tableName) {

int iDisplayLength = ToInt(HttpContext.Current.Request.Params["iDisplayLength"]);
int iDisplayStart = ToInt(HttpContext.Current.Request.Params["iDisplayStart"]);
var numberOfRowsToReturn = "";
numberOfRowsToReturn = iDisplayLength == -1 ? "TotalRows" : (iDisplayStart + iDisplayLength).ToString();
String FilterString = GenerateFiltering(table);
String OrderString = GenerateOrderString(table);
string query = "SELECT Table.* FROM [" + tableName + "] " + FilterString + " " + OrderString;
return query;
}

public string GenerateOrderString(DataTable table) {

var sb = new StringBuilder();
string orderByClause = string.Empty;
sb.Append(ToInt(HttpContext.Current.Request.Params["iSortCol_0"]));

sb.Append(" ");

sb.Append(HttpContext.Current.Request.Params["sSortDir_0"]);

orderByClause = sb.ToString();

if (!String.IsNullOrEmpty(orderByClause))
for (int i = 0; i < table.Columns.Count; i++)
orderByClause = orderByClause.Replace(i.ToString(), table.Columns[i].ColumnName);
else
orderByClause = table.Columns[0].ColumnName + " ASC";

orderByClause = "ORDER BY " + orderByClause;
return orderByClause;
}

public string GenerateFiltering(DataTable table) {
string rawSearch = HttpContext.Current.Request.Params["sSearch"];
string filteredWhere = string.Empty;
string wrappedSearch = "'%" + rawSearch + "%'";
var sb = new StringBuilder();
if (rawSearch.Length > 0) {
sb.Append("WHERE ");
int columncounter = 0;
foreach (DataColumn column in table.Columns) {
sb.Append(column.ColumnName + " LIKE " + wrappedSearch);

if (++columncounter != table.Columns.Count)
sb.Append(" OR ");
}

}
filteredWhere = sb.ToString();
return filteredWhere;

}


public string DataTableToJSON(DataTable table) {
int sEcho = ToInt(HttpContext.Current.Request.Params["sEcho"]);
int iDisplayLength = ToInt(HttpContext.Current.Request.Params["iDisplayLength"]);
int iDisplayStart = ToInt(HttpContext.Current.Request.Params["iDisplayStart"]);
int endRow = iDisplayStart + iDisplayLength - 1;

string OutputJSON = String.Empty;
StringBuilder sb = new StringBuilder();
string totalRecords = table.Rows.Count.ToString();
string totalDisplayRecords = totalRecords;

string rowClass = "";

int count = 0;
foreach (DataRow row in table.Rows) {
if (count >= iDisplayStart && count < endRow) {
sb.Append("{");
sb.AppendFormat(@"""DT_RowId"": ""{0}""", count);
sb.Append(",");
sb.AppendFormat(@"""DT_RowClass"": ""{0}""", rowClass);
//sb.Append(",");

int columnCounter = 0;
foreach (DataColumn column in table.Columns) {
sb.Append(",");
sb.AppendFormat("\"" + columnCounter + "\": \""
+ row[column].ToString().Replace("\"", "").Replace("\n", "\\n").Replace("\r", "\\r")

+ "\"");
columnCounter++;
}
sb.Append("},");
}
count++;
}


if (totalRecords.Length == 0) {
sb.Append("{");
sb.Append(@"""sEcho"": ");
sb.AppendFormat(@"""{0}""", sEcho);
sb.Append(",");
sb.Append(@"""iTotalRecords"": 0");
sb.Append(",");
sb.Append(@"""iTotalDisplayRecords"": 0");
sb.Append(", ");
sb.Append(@"""aaData"": [ ");
sb.Append("]}");
OutputJSON = sb.ToString();

return OutputJSON;
}

if (sb.Length > 0)
OutputJSON = sb.Remove(sb.Length - 1, 1).ToString();
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;
}

}
}

[/code]

I am not sure if it will help at all, but here is a link to the DataTables debugger: http://debug.datatables.net/eviwun

Hiopefully you will be able to help me out. i am not too familiar with JavaScript, so it is somewhat frustrating having no idea how to fix these issues. If you need anything else, please let me know.

Thanks,
-Joe