How to add rows and column from json

Honeydew

I just came through your documentation and trying to implement DataTables in my project. The html code I have written so far is:

        $(document).ready(function () {
            $('#example').DataTable({
                "responsive": true,
                "processing": true, 
                "serverSide": true, 

                'ajax': {
                    "type": "POST",
                    "url": 'Handlers/jQueryDatatableHandler.ashx',
                    "data": { Operation: 'EmployeeList', searchText: ''},
                    "dataSrc": ""
                },
                success: function (result) {
                    console.log(result);
                    var data = JSON.parse(result);
                    alert(result);
                },
                "columnDefs":
                [{
                    "targets": [1],
                    "visible": false,
                    "searchable": false
                }]
               
            });
        });

Now the jQueryDatatableHandler.ashx code is as follows:

    public void EmployeeList(HttpRequest request, HttpResponse response)
    {
        string searchTxt = request.QueryString["searchText"];
        if (searchTxt == null) searchTxt = "";

        EmployeeListParameters mlp = new EmployeeListParameters();
        mlp.searchText = searchTxt;

 int totalRecords;
        EmployeeDataProvider mvdp = new EmployeeDataProvider();
        List<NEmployee> empList;

        msgList = mvdp.GetEmployeeDetails(mlp, out totalRecords);
        output = new JavaScriptSerializer().Serialize(msgList);
        response.Write(output);
    }

So the output I am getting is something like this:

{
    "page": 1,
    "total": 1,
    "records": 3,
    "colnames": [" ", "EmployeeId", "Name", "Title", "Joining", "Viewed"],
    "rows": [{
        "id": 47220,
        "cell": ["0", "47220", "JamesBond", "Manager", "10/26/2018 9:47:07 PM", "False"]
    }, {
        "id": 47218,
        "cell": ["0", "47218", "Manju Sharma", "Accountant", "10/24/2018 1:37:57 AM", "True"]
    }, {
        "id": 47216,
        "cell": ["0", "47216", "James Corbett", "GM", "10/23/2018 10:39:45 PM", "True"]
    }]
}

The first column needs to be shown as checkbox column.

My question is how do I show the column names and rows in datatable above?

EDIT: Updated to use Markdown Code formatting.

kthorngren

I'm not familiar with the server code you are using but I suspect that it doesn't support the communications parameters that are used by server side processing, as explained here:
https://datatables.net/manual/server-side

If not then you will want to remove "serverSide": true,.

Your ajax.dataSrc option of "dataSrc": "" is not correct. Based on what you are returning it would be "dataSrc": "rows".

Looks like you are trying to use the jQuery ajax success function. This is not supported when using the Datatables ajax option. The reason is this would overwrite the jQuery success function causing problems.

Your data is not in a format that is easily applied to Datatables. It can be made to work but if you can change how its returned to match what Datatables is expecting it would be better. This doc explains the data sources:
https://datatables.net/manual/data/#Data-source-types

You can look at these examples to get a better idea of what Datatables likes to work with:

Array based data:
https://datatables.net/examples/ajax/simple.html

Object based data:
https://datatables.net/examples/ajax/objects.html

I assume you want the checkbox to be checked if the first column is "1" or unchecked if "0". You can use this example to get started:
https://editor.datatables.net/examples/api/checkbox.html

If you are not using Editor you can ignore that part of the config. The Datatables portion of the example shows how to display the checkbox with columns.render and how to update the checked state using rowCallback.

Hope the above helps. Please post any questions.

Kevin

Honeydew

Thanks Kevin for the suggestions, appreciate it. I am new to it so just trying to build it asap but it seems I will need to spend quite a time to understand it.
Could you please suggest can I set the columns and rows from a single JSON. Can you suggest an example where the column and rows are build, i mean how should i form the JSON and set the columns/rows at runtime?
Thanks once again!

kthorngren

Dynamically building the columns gets asked often. Here is one example:
http://live.datatables.net/huyexejo/1/edit

It doesn't use ajax but an external ajax call. It gets the key names from the first row of data. In your case you would just use the colnames array.

columns.title is used to set the table header name.

Kevin

Honeydew

It is not working . It seems I will need to fix the JSON format i.e.
{"colnames":[" ","EmployeeId","Name","Title","Department","Viewed"],"rows":[{"cell":["0","47220","JamesBond","Manager","10/26/2018 9:47:07 PM","False"]},{"cell":["0","47218","Manju Sharma","Accountant","10/24/2018 1:37:57 AM","True"]},{"cell":["0","47216","James Corbett","GM","10/23/2018 10:39:45 PM","True"]}]}

to the correct one. I would appreciate if you could please advise how it should be formed so that I can set both columns and rows
Thanks

kthorngren

I took your data and placed it into this test case:
http://live.datatables.net/pejaxepa/1/edit

This example has two tables. The first with your original set of data. It has a loop to build the columns and a loop to modify the 'rows' data structure to load into Datatables. It creates an array of arrays. This process may be slow depending on how many rows you will have in production.

The second example uses the same columns array but uses a modified data structure for the data. If you can return your rows as an array of arrays like this then the client side will not be impacted by needing to manipulate the returned data.

These loops you would put in the ajax success function like I have in my first example:
http://live.datatables.net/huyexejo/1/edit

Kevin

Honeydew

Thanks Kevin for all the help. I have implemented your code in my aspx page but it gives me error:

Uncaught TypeError: Cannot read property 'length' of undefined
at Object.success (test.aspx:24)
at fire (jquery-3.3.1.js:3268)
at Object.fireWith [as resolveWith] (jquery-3.3.1.js:3398)
at done (jquery-3.3.1.js:9305)
at XMLHttpRequest.<anonymous> (jquery-3.3.1.js:9548)

aspx code :

<link rel="stylesheet" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script>       
    $(document).ready(function () {
        $.ajax({
           "url": "Handlers/jQueryDatatableHandler.ashx",
           "data": { Operation: 'EmployeeList', searchText: ''},
           success: function (data) {                  
                var json = data;
                console.log(json); // this returns the json as mentioned in example, works fine
                console.log(json.colnames.length); // this line throws error                   
            }
        });
    });
</script>

Honeydew

Hey Kevin, I resolved the above error by adding this line:
var json = JSON.parse(data);
and it is working fine.
Thanks a lot for the example.

Now the only thing is first column should be shown as checkbox and hide the last one.

kthorngren

I updated the example to show both:
http://live.datatables.net/pejaxepa/1/edit

For the checkbox I used the example at the link I provided previously:
https://editor.datatables.net/examples/api/checkbox.html

i placed the checkbox code into columnDefs and used columnDefs.targets to apply the code to column 0. rowCallback is used to check the box if data[0] is 1.

The last column is hidden by setting columnDefs.targets to -1 and setting columns.visible to false.

HTH,
Kevin