ASPX URL produces a JSON string, Consume with jQuery DataTable

Question

For practice, I am trying to display JSOn data into a datatable in my aspx webpage, but the datatable is stuck with the word processing. Turns out when I inspect the console, the error is Cannot read property 'length' of undefined

What does this error mean? how do I fix it? is this error because of my wrong approach?

What I did is, first I made a .aspx.cs file that produces the JSON string whenever it's URL is called:

[{"NickName":"Bob","LogIN":"6/7/1846 12:00:00 AM","LogOUT":"6/7/1846 12:00:00 AM"},{"NickName":"Bob","LogIN":"6/6/1846 12:00:00 AM","LogOUT":"6/6/1846 12:00:00 AM"},{"NickName":"Patrick","LogIN":"6/7/1846 12:00:00 AM","LogOUT":"6/7/1846 12:00:00 AM"},{"NickName":"Ward","LogIN":"6/7/1846 12:00:00 AM","LogOUT":"6/7/1846 12:00:00 AM"},{"NickName":"Ward","LogIN":"6/6/1846 12:00:00 AM","LogOUT":"6/6/1846 12:00:00 AM"},{"NickName":"Ward","LogIN":"6/5/1846 12:00:00 AM","LogOUT":"6/5/1846 12:00:00 AM"},{"NickName":"Krabs","LogIN":"","LogOUT":""},{"NickName":"Sandy","LogIN":"","LogOUT":""},{"NickName":"Pearl","LogIN":"","LogOUT":""}]

Then on my .aspx file where I am supposed to display the datatable, I wrote this script which is the suggested on the dataTables site:

 /*using the ajax function*/
       var tbl = $('#datatableJSONEx').DataTable({
           processing: true,
           serverSide: true,
           info: true,
           ajax: "DefaultJSONResult?THEstr=produceJSON",

           order: [[0, 'desc']],
           select: true,
           responsive: true,
           buttons: true,
           length: 10

       });

Then I added it's representation in HTML:

        <div>

            <table id="datatableJSONEx" class="display" cellspacing="0" width="100%">                    
                 <thead>
                    <tr>
                        <th>NickName</th>
                        <th>LogIN</th>
                        <th>LogOUT</th> 
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>NickName</th>
                        <th>LogIN</th>
                        <th>LogOUT</th> 
                    </tr>
                </tfoot>
            </table>

        </div>

</div>

What is causing the error? When the error has been addressed, is this approach correct if I want my tables to automatically update when there are changes to the server?

EDIT I changed this property in my dataTables jQuery : ajax: "DefaultJSONResult?THEstr=produceJSON" into ajax: $.ajax("DefaultJSONResult?THEstr=produceJSON")

however, it still displays processing.. and I can't see anything on the console anymore when I inspect element in google chrome.

UPDATE I changed my jQuery to have $.ajax() convert my url into an object, then I pass it into the ajax: property of the datatable:

var jqxhr = $.ajax("DefaultJSONResult?THEstr=ProduceJSON",
            {
            success: function (data, status, xhr) { //on success
                $('p').append(status);
             },
            error: function (jqXhr, textStatus, errorMessage) { //the action was a failure, know why.
                $('p').append('ERROR: ' + errorMessage);
            }
        });



        /*using the ajax function*/
       var tbl = $('#datatableJSONEx').DataTable({
           processing: true,
           serverSide: true,
           info: true,
           ajax: jqxhr,
           sAjaxDataProp : "",
           columns: [
               { data: 'NickName'},
               { data: 'TimeIN' },
               { data: 'TimeOUT' }
           ],

           order: [[0, 'desc']],
           select: true,
           responsive: true,
           buttons: true,
           length: 10

       });

the element p now returns SUCCESS and there are no errors on the console showing. BUT STILL the datatable returns nothing... is the JSON string the .aspx url returning the cause? how can I make Jquery Datatables acccept my format? What is the correct format?

Do you have model i mean class in C# which relates to datatables columns ? — Mairaj Ahmad, Feb 22 '17 at 04:41
Datatable needs `array of objects` so you have to send `array of objects `instead of `json string` — Mairaj Ahmad, Feb 22 '17 at 04:43
I see, does a system.datatable count as an array of objects? — Malcolm Salvador, Feb 22 '17 at 05:09
to get the data from the dbase, I used (System.Data.SQLClient), then to output it as a JSON string I used Newtonsoft.Json. — Malcolm Salvador, Feb 22 '17 at 05:15
No `json string` will not fill the table you have convert it to `list of objects` in `C#` and send that `list`. — Mairaj Ahmad, Feb 22 '17 at 05:17
So, instead of trying to convert the `datatable` (I get the data by using `SQLDataAdapter.Fill(Datatable)`) with the sql daya into a JSON string, I instead convert the datatable into a `List` and pass it to.. the `ajax:` property above? — Malcolm Salvador, Feb 22 '17 at 05:21

Mayank Pandeyz · Accepted Answer · 2017-02-22T05:15:18.477

1

Try this:

$(document).ready(function() {
var dataSet = [];
dataSet.push(
    [1,"Sasha","Brenna","0800 1111"],
    [2,"Sage","Mia","(01012) 405872"],
    [3,"Chelsea","Allistair","076 0578 0265"],
    [4,"Uta","Savannah","070 1247 5884"],
    [5,"Remedios","Berk","0995 181 0007"],
    [6,"Ruby","Wayne","0800 1111"],
    [7,"Faith","Fredericka","(01709) 099879"],
    [8,"Myra","Harrison","070 4241 9576"],
    [9,"Drake","Miriam","0800 733635"],
    [10,"Reed","Shannon","076 8597 5067"]
);

$('#data_table').DataTable( {
    data: dataSet,
});

In your case the dataSet get its values from ajax() call.

Working Fiddle

edited Feb 22 '17 at 05:15

answered Feb 22 '17 at 05:12

Mayank Pandeyz

25,704
4
40
59

thanks, but this approach means that the dataset is coded into the page. What if I need it to come from the database? – Malcolm Salvador Feb 22 '17 at 05:14
Check the line **In your case the dataSet get its values from ajax() call.** specify in answer. – Mayank Pandeyz Feb 22 '17 at 05:15
what does that exactly mean? I'm sorry I'm really new to this approach. – Malcolm Salvador Feb 22 '17 at 05:16
You have to get the value from database to **dataSet** using ajax or traditional approach – Mayank Pandeyz Feb 22 '17 at 05:19
I tried using the `$.ajax()` function like this `ajax: $.ajax("DefaultJSONResult?THEstr=produceJSON")`... however, it still displays processing and the console does not reveal anything.Given a URL which contains a JSON string, how would you do it? – Malcolm Salvador Feb 22 '17 at 05:36

score 1 · Answer 2 · answered Feb 22 '17 at 05:28

You need to send proper dataset to datatable if you are using server side datatable. Below is a working example of server side datatable using asp.net

[WebMethod]
[System.Web.Script.Services.ScriptMethod(UseHttpGet = true)]
public static DataTableData GetData()
{

   List<string[]> list = new List<string[]>();
   DataTableData data = new DataTableData();
   data.data = new List<string[]>();

   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "1" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "2" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "3" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "4" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "5" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "6" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "7" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "8" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "9" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "10" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "11" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "12" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "13" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "14" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "15" });
   list.Add(new string[] { "Mairaj", "Ahmad", "Minhas", "16" });

   int start = 0;
   int length = 0;
   int draw = 0;
   int.TryParse(HttpContext.Current.Request.QueryString["start"].ToString(), out start);
   int.TryParse(HttpContext.Current.Request.QueryString["length"].ToString(), out length);
            int.TryParse(HttpContext.Current.Request.QueryString["draw"].ToString(), out draw);

   data.draw = draw;
   var filter = list.Skip(start).Take(length).ToList();
   data.data = filter;

   data.recordsFiltered = list.Count;
   data.recordsTotal = list.Count;
   return data;
}

public class DataTableData
{
  public int draw { get; set; }
  public int recordsTotal { get; set; }
  public int recordsFiltered { get; set; }
  public List<string[]> data { get; set; }

 }

This is datatable definition

<table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>

                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>ID</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>ID</th>
            </tr>
        </tfoot>
    </table>

score 0 · Answer 3 · edited May 23 '17 at 12:31

I was able to output the table via using the ajax property of the Datatables. I simply followed the instructions here for the format of my particular JSON string.

The JSON string

[{"NickName":"Bob","LogIN":"6/7/1846 12:00:00 AM","LogOUT":"6/7/1846 12:00:00 AM"},{"NickName":"Bob","LogIN":"6/6/1846 12:00:00 AM","LogOUT":"6/6/1846 12:00:00 AM"},{"NickName":"Patrick","LogIN":"6/7/1846 12:00:00 AM","LogOUT":"6/7/1846 12:00:00 AM"},{"NickName":"Ward","LogIN":"6/7/1846 12:00:00 AM","LogOUT":"6/7/1846 12:00:00 AM"},{"NickName":"Ward","LogIN":"6/6/1846 12:00:00 AM","LogOUT":"6/6/1846 12:00:00 AM"},{"NickName":"Ward","LogIN":"6/5/1846 12:00:00 AM","LogOUT":"6/5/1846 12:00:00 AM"},{"NickName":"Krabs","LogIN":"","LogOUT":""},{"NickName":"Sandy","LogIN":"","LogOUT":""},{"NickName":"Pearl","LogIN":"","LogOUT":""}]

The HTML

 <div class="col-md-4">
        <h2>JQUERY Datagrid</h2>
        <p>Free Datatables via Jquery. Data is produced via JSON data source. </p>
            <div>

                <table id="datatableJSONEx" class="display">                    
                     <thead>
                        <tr>
                            <th>NickName</th>
                            <th>LogIN</th>
                            <th>LogOUT</th> 
                        </tr>
                    </thead>
                    <tfoot>
                        <tr>
                            <th>NickName</th>
                            <th>LogIN</th>
                            <th>LogOUT</th> 
                        </tr>
                    </tfoot>
                </table>
            </div>

    </div>

..and the JS

       var tbl = $('#datatableJSONEx').DataTable({

           ajax: {
               dataSrc: "",
               url: "DefaultJSONResult?THEstr=produceJSON"
           },

           columns: [
               { data: 'NickName' },
               { data: 'LogIN' },
               { data: 'LogOUT' }
           ],
           order: [[0, 'desc']],
           select: true,
           responsive: true,
           buttons: true,
           length: 10
       });

        //we set an interval so that grid automatically reloads every afer 10 secs.
       setInterval(function () {
       tbl.ajax.reload( null, false ); // ( null, false) so user paging is not reset on reload
            }, 10000 ); //10 seconds!

(thanks to Mayank for prodding me in the right direction. I more or less have a clearer idea of how ajax works. I am also able to return values via JSON.ajax() success, but I ultimately settled for the code above since with it I can also make the webpage live via .ajax.reload())

ASPX URL produces a JSON string, Consume with jQuery DataTable

3 Answers3

Linked