DataTables with WebMethod

jonr

I have spent all day trying to get somewhere evaluating DataTables for my web site and so far have not got anywhere.

I can see many examples of how to use datatables with server side json data files but I want to be able to populate a data table from SQL server.

I always use WebMethods to collect data and am familiar with passing parameters to my WebMethod functions and processing the resulting data and I tend to use JSON.stringify objects because I prefer to use properties of objects both server side and client side.

My latest attempt has this

        $('#actuals').DataTable({
            "processing": true,
            "serverSide": true,
            "ajax": {
                "url": "PlanF.aspx/getMeasures",
                "type": "POST",
                "error": error,
                "success": success
            },
            "columns": [
                { "data": "first_name" },
                { "data": "last_name" },
                { "data": "position" },
                { "data": "office" },
                { "data": "start_date" },
                { "data": "salary" }
            ]
        } );

taken straight from the web site.

I have included error and success methods so that I can work out what is going wrong.

What seems to be happeing is that the entire content of my web page markup is being passed to JSON.parse() and is failing with the dreaded

"Unexpected token < in JSON at position 4"

error

I really like the way that DataTables look and act but I can't get anything working.

furthermore, research on this site and Google don't throw up anyone else that seems to be struggling.

I am beginning to wonder whether its even possible but so far I have been unable to get past javascript on the client.

can anyone help me?

thanks

jON

bindrid

All of my data tables run off web methods. Maybe I can give you some tips to help you through it. I would like to see your web method that you are using server side so I can see best how to answer your questions.

bindrid

Let me build an example of how I do things when server side is set to true starting with my web service. This example uses stuff common to what is done on the DataTable website. (Note: I use c# and Visual Studio for all of this and it all works in my dev environment)

This post is what my server side looks like:

First of all, here is the class I use serverside to deserialize what is being sent from the client.

        public class DataTableParameter
        {
            public int draw { get; set; }
            public int length { get; set; }
            public int start { get; set; }
            public List<columm> columns { get; set; }
        }
        public struct columm
        {
            public string data;
            public string name;
            public Boolean searchable;
            public Boolean orderable;
            public searchValue Search;
        }
        public struct searchValue
        {
            public string value;
            public Boolean regex;
        }

This is the class object that I serialize and send back to the client:

        // Single datatable row  
        public struct dtData
        {
            public string name;
            public string position;
            public Int32 salary;
            public string start_date;
            public string office;
            public string extn;
        }

        // So my web service returns a serialize version of this
        public struct DataTableResponse
        {
            public int draw;
            public int recordsTotal;
            public int recordsFiltered;
            public List<dtData> data;
        }

And my web method:

        [WebMethod]
        public string GetDTDataSerializedList(String ClientParameters)
        {
            DataTableParameter dtp = JsonConvert.DeserializeObject<DataTableParameter>(ClientParameters);
            // GetTheData method gets the data and sets the recordsTotal and recordsFiltered values.       
            DataTableResponse res = getTheData(dtp);
            res.draw = dtp.draw;
            return JsonConvert.SerializeObject(res);
        }

bindrid

A continuation of my last post (Sorry Allan if you don't like this). This is what my client looks like.

First off, don't use "success" in this context. Its owned by DataTables. You can use dataFilter (as I do) to examine or change the web method response before DataTable sees it.

   $(document).ready(function () {

            $('#example').DataTable({
                "processing": false,
                "serverSide": true,
                paging:true,
                pageLength:5,
                "ajax": {
                    contentType: "application/json; charset=utf-8",
                    url: "wsService.asmx/GetDTDataSerializedList",
                    type: "Post",

                    data: function (dtParms) {
                        // I send it as a string to reduce the chance of an error before it reaches the server.
                        // Note that "ClientParmeters" exactly matches the name used by the web method.
                        return JSON.stringify({ ClientParameters: JSON.stringify(dtParms) });

                    },
                    dataFilter: function (res) {
                        // Web Method always returns the reponse in a d so it looks like
                        // {d:string} where the string is the reponse from the web method. 
                        // Its a string because I serialize it before sending it out.
                        // This bit of code takes that into account and converts it 
                        // to what DataTable is expecting.
                        var parsed = JSON.parse(res);
                        return parsed.d;
                    },
                    error: function (x, y) {
                        debugger;
                        console.log(x);

                    }
                },
                "columns": [
                { "data": "name" },
                  { "data": "position" },
                { "data": "office" },
                {"data":"extn"},
                { "data": "start_date" },
                { "data": "salary" }
                ]
            });

        });

jonr

Sorry Bindrid, I didn't realise that I was not going to get replies emailed to me. I am only just now checking back with some further details.

I only included success and error calls so that I could trap the datastream coming back, no real processing was used in those functions, just a "debugger" call.

I use VB for my sins and I show my solution below. Reading through your code I have taken a slightly different tack as I couldnt find a way to get the DataTable version of ajax to do anything useful for me. I use a webmethod to grab data and then pass that into the DataTable in the success event.

function getActuals(job) {

    var Criteria = { Rno: job.Rno };

    $.ajax({
        type: 'POST',
        url: 'PlanF.aspx/GetMeasures',
        contentType: "application/json; charset=utf-8",
        dataType: 'json',
        data: JSON.stringify({ 'Criteria': Criteria }),
        success: function (results, args) {
            if (results.d.length < 1) {
                alert("No data found");
            } else {

                editor = new $.fn.dataTable.Editor({
                    table: "#actuals",
                    idSrc: 'ID',
                    fields: [{
                        label: "Hole No:",
                        name: "HoleNo"
                    }, {
                        label: "Length:",
                        name: "Length"
                    }, {
                        label: "Number:",
                        name: "Number"
                    }, {
                        label: "Width:",
                        name: "Width"
                    }, {
                        label: "Depth:",
                        name: "Depth"
                    }
                    ]
                })

                var table = $('#actuals').DataTable({
                    // responsive: true,
                    destroy: true,
                    data: results.d,
                    ordering: false,
                    searching: false,
                    keys: {
                        columns: ':not(:first-child)',
                        keys: [9]
                    },
                    select: {
                        style: 'os',
                        selector: 'td:first-child'
                    },        
                    formOptions: {
                        inline: { onBlur: 'submit' }
                    },
                    "columns": [
                        { "data": "HoleNo" },
                        { "data": "Length" },
                        { "data": "Number" },
                        { "data": "Width" },
                        { "data": "Depth" }
                    ]
                });

                $(".modal-title").text("Job: " + job.Rno + " - Actual Measures");
                $(".modal").modal("show");

                // Inline editing on click
                $('#actuals').on('click', 'tbody .even td:not(:first-child)', function (e) { editor.inline(this); });

                // Inline editing on tab focus
                $('#actuals').on('key-focus', function (e, datatable, cell) { debugger; editor.inline(cell.index()) });
            }
            return false;
        },
        error: function (xhr, status, error) {
            hideLightBox();
            var err = eval("(" + xhr.responseText + ")");
            alert(err.Message);
            return false;
        }
    });
}

I am actually getting somewhere after a little assistance from Allan however I am now stumped as the above code refuses to allow me to "inline" edit using tab key between cells.

jonr

I tried to add this to my code above, found in another online example:

                // Disable KeyTable while the main editing form is open
                editor
                    .on( 'open', function ( e, mode, action ) {
                        if ( mode === 'main' ) {
                            table.keys.disable();
                        }
                    } )
                    .on( 'close', function () {
                        table.keys.enable();
                } );

But this fails in the 'close' method, the variable table is defined ok but table.keys is undefined.

both editor and table are global var's.

jON

jonr

in fact, immediately after table is assigned I find that:

table.ordering
table.searching
table.keys

are all undefined.

jON

allan

Its important to note that @bindrid's example (thank you for providing it!) is using server-side processing which adds complexity on the server-side. You only really need that if you are using tens of thousands or more rows. For less than 50k rows I would suggest just using client-side processing.

When using client-side processing all you need to do is have your web method return JSON.

This error message:

"Unexpected token < in JSON at position 4"

Suggests that the server is not currently returning JSON. Its probably returning XML or perhaps HTML. You need to change your web method to return JSON.

Regards,
Allan

jonr

Sorry Allan,

I am calling the same web method that I am calling in my second, working example.

it is returning the exact same data,

Please see the attached image.

my code ..

   function getActuals(job) {

        $('#actuals').DataTable({
            processing: true,
            serverSide: true,
            destroy: true,
            ajax: {
                "url": "PlanF.aspx/getMeasures",
                "type": "POST",
                "error": error,
                "success": success
            },
            columns: [
                { "data": "HoleNo" },
                { "data": "Task" },
                { "data": "Length" },
                { "data": "Number" },
                { "data": "Width" },
                { "data": "Depth" }
            ]
        });

    }

jON

jonr

The image should now be attached.

As you can see the reponse I am getting back is the content of my aspx page.

jON

allan

Jo Jon,

I don't see where success or error are defined, so the very first thing to do is to remove them. As noted in the ajax documentation you should not override DataTables defaults.

Secondly, do you need server-side processing? If not, remove serverSide and save yourself the pain of that.

Third, the data being returned appears to be HTML, not JSON data with which the table should be populated.

Allan

jonr

Hi Allan,

I seem to be struggling to make myself understood here so I have simplified it one step further to prove my point.

I tried with serverside processing enabled and disabled. Now disabled as suggested.

success and error were only added so I could capture and debug to find the reported error otherwise I get an error from your code which is superficial and only tells me that the response is not json. As per the attachment.

The 3 attachements refer.

You can see one call that is successful, this is my usual method for calling for data and is exactly the same used for the cal for DataTables.

In the other call the reponse reported is quite clearly the markup from my page. Same method call and apparently using the same ajax calling conventions.

all is the same session and indeed all in the same function as per this revised code:

    function getActuals(job) {

        var Criteria = { Rno: job.Rno };

        $.ajax({
            type: 'POST',
            url: 'PlanF.aspx/GetMeasures',
            contentType: "application/json; charset=utf-8",
            dataType: 'json',
            data: JSON.stringify({ 'Criteria': Criteria })
        });


        $('#actuals').DataTable({
            processing: true,
            destroy: true,
            ajax: {
                url: "PlanF.aspx/getMeasures",
                type: "POST",
                dataType: 'json',
                data: JSON.stringify({ 'Criteria': Criteria })
            },
            columns: [
                { "data": "HoleNo" },
                { "data": "Task" },
                { "data": "Length" },
                { "data": "Number" },
                { "data": "Width" },
                { "data": "Depth" }
            ]
        });

now do you believe me :-)

jON

kthorngren

Are the filenames case sensitive?

In one case you have 'PlanF.aspx/GetMeasures' and the other "PlanF.aspx/getMeasures". What happens if you change the second to match the first?

Kevin

jonr

Thanks for the thought Kevin, I have to say I believed that I had interchanged case before on webmethods and not had a problem.

However, I just tried with the same method name (casewise) and the problem persists.

jON

bindrid

From what I see, your ajax is still not what I expect both calls. I really need to see your web method definition before I can respond.

bindrid

I took another look. Since you did not expand one of the objects in the data array, I could not verify all of the column data items. However, based on what I do see, I expect your code to look something like this if you are doing the ajax before the table is initialized:

        $(document).ready(function () {
            // this ajax gets all of the data at once based on Criteria
            $.ajax({
                type: 'POST',
                url: 'PlanF.aspx/GetMeasures',
                contentType: "application/json; charset=utf-8",
                dataType: 'json',
                data: JSON.stringify({ 'Criteria': Criteria }),
                success: function (res) {
                    displayTable(res.d);
                }
            });
        });

        function displayTable(data) {
            $('#actuals').DataTable({
                destroy: true,
                data:data,
                columns: [
                    { "data": "HoleNo" },
                    { "data": "Task" },
                    { "data": "Length" },
                    { "data": "Number" },
                    { "data": "Width" },
                    { "data": "Depth" }
                ]
            });
        }

And your web method looks something like (my vb is a little rusty)

  <ScriptService()> _
    Public Class PlanF
        Inherits System.Web.Services.WebService


            <WebMethod(EnableSession:=True)> _
            Public Function GetMeasures(ByVal Criteria as String) as List(Of YourOutputClassName)

               ' bunch of code that deserializes Criteria and gets the list of data

              Return YourDataList
        End Function 


    End Class

jonr

Hi Bindrid,

I think my web method is actually arbitary here as I call my method immediately followed by the DataTables call to the same method. The first returms a json response as would do the second if there wasn't a problem in the calling.

For brevity, my method (sorry its in VB) is as follows (please no comments on non parameterised calls, this is my testbed as can be seen by my hardcoding of a Job number as a default and will be changed later with more secure code):

<WebMethod()> _
    Public Shared Function GetMeasures(Criteria As JobCriteria) As Object

        If Criteria Is Nothing Then
            Criteria = New JobCriteria
            Criteria.Rno = "A-0597042-243"
        End If

        Dim Measures = New List(Of JobMeasure)
        Dim sSQL As String = ""

        ' Try
        Using db As New MainUtil(ConfigurationManager.ConnectionStrings("FernsGroup").ConnectionString)
            With db
                sSQL = "SELECT RNo, HoleNo, HoleRowID, OLength, ONumber, OWidth, ODepth, ALength, ANumber, AWidth, ADepth, TaskDescription " & _
                       "FROM THoleDetails INNER JOIN TTasks ON THoleDetails.TaskID = TTasks.TaskID where Rno='" & Criteria.Rno & "' order by HoleNo"
                .QOpen(sSQL, False)
                Dim table As DataTable = .Tab

                Dim ID As Integer = 0

                For Each row As DataRow In table.Rows
                    For i = 1 To 2
                        ID += 1
                        Dim measure As New JobMeasure
                        With measure
                            .ID = ID
                            .Rno = row("Rno")
                            .HoleNo = If(i = 1, row("HoleNo"), "Actuals")
                            .HoleRowID = row("HoleRowID")
                            Dim prefix As String = If(i = 1, "O", "A")
                            .Length = Round(SafeDouble(row(prefix & "Length")), 2)
                            .Number = Round(SafeDouble(row(prefix & "Number")), 2)
                            .Width = Round(row(prefix & "Width"), 2)
                            .Depth = Round(row(prefix & "Depth"), 2)
                        End With
                        Measures.Add(measure)
                    Next
                Next
                Return Measures

            End With
        End Using

    End Function

my JobMeasure class:

Public Class JobMeasure
    Public Property ID As Integer
    Public Property Rno As String
    Public Property HoleRowID As String
    Public Property HoleNo As String
    Public Property Task As String
    Public Property Number As Double
    Public Property Length As Double
    Public Property Depth As Double
    Public Property Width As Double
End Class

and my criteria class ...

Public Class JobCriteria
    Public Property Rno As String
End Class

I believe that something is going wrong in the DataTables ajax call.

jON

bindrid

I will make the appropriate changes in my test bed when I get home tonight and see what I come up with.

bindrid

I am lost. I am not having issues.
I created a vb web project.
Created aspx page.
Added <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="True">
</asp:ScriptManager> to the page.

Made this change in my global ASA

    Sub Application_Start(sender As Object, e As EventArgs)
        ' Fires when the application is started
        '    RouteConfig.RegisterRoutes(RouteTable.Routes)
        BundleConfig.RegisterBundles(BundleTable.Bundles)
    End Sub

Added this to the code behind

    <WebMethod()>
    Public Shared Function GetDtData(ClientParameters As String) As Object
        Dim list As New List(Of dtData)
        list = getDataTableData()
        Return list
    End Function

and ran this on my html page

   

            $.ajax( {
                contentType: "application/json; charset=utf-8",
                url: "pageWithService.aspx/GetDtData",
                type: "Post",
                dataType:"json",
                data:
                    // I send it as a string to reduce the chance of an error before it reaches the server.
                    // Note that "ClientParmeters" exactly matches the name used by the web method.
                     JSON.stringify({ ClientParameters: "fake data"})

                ,

                success: function (res) {
                    debugger;
                    justDataTable(res.d);
                },
                error: function (x, y) {
                    debugger;
                    console.log(x);

                }
            }
            );


        function justDataTable(data) {
            $('#example').DataTable({
                "processing": false,
                "serverSide": false,
                paging: true,
                pageLength: 5,
                data:data,
                "columns": [
                { "data": "name" },
                  { "data": "position" },
                { "data": "office" },
                { "data": "extn" },
                { "data": "start_date" },
                { "data": "salary" }
                ]
            });
        }

jonr

Hi Bindrid,

thats more than I had to do.

I don't have a scriptmanager on the page and I have made no changes to global.asa. In fact I don't even have global.asa page.

I use this method to collect data in most of my pages, I don't like the cludgy way that ASP code behind populates and works, I almost exclusively use javascript/JQUery and have Bootstrap bolted in too.

I suspect that I have a clash of .js

Are datatables derived from JQuery data tables, it looks like they are and I am wondering whether I may have a clash between functions in JQuery and Bootstrap. That seems to be what most of my issues are these days.

jON

allan

There is a difference between the two Ajax requests that are being made:

   $.ajax({
        type: 'POST',
        url: 'PlanF.aspx/GetMeasures',
        contentType: "application/json; charset=utf-8",
        dataType: 'json',
        data: JSON.stringify({ 'Criteria': Criteria })
    });

and

        ajax: {
            url: "PlanF.aspx/getMeasures",
            type: "POST",
            dataType: 'json',
            data: JSON.stringify({ 'Criteria': Criteria })
        },

The first (successful one) is setting the contentType, while the second is not. Does the server start responding with JSON if you include contentType: "application/json; charset=utf-8", in the DataTables ajax configuration?

It sounds rather like the server is responding differently based on the content type - something I've seen .NET do before, but normally it would send back XML rather than the original document - although that might be something to do with how the routing it setup.

Allan

jonr

I had tried that previously Allan but removed when I saw a post that said "why are you using contentType" and forgot to put it back in again when I realised it didn't make any difference.

The problem still persists with this code:

    function getActuals(job) {

        var Criteria = { Rno: job.Rno };

        $.ajax({
            type: 'POST',
            url: 'PlanF.aspx/GetMeasures',
            contentType: "application/json; charset=utf-8",
            dataType: 'json',
            data: JSON.stringify({ 'Criteria': Criteria })
        });


        $('#actuals').DataTable({
            processing: true,
            destroy: true,
            ajax: {
                url: "PlanF.aspx/GetMeasures",
                type: "POST",
                contentType: "application/json; charset=utf-8",
                dataType: 'json',
                data: JSON.stringify({ 'Criteria': Criteria })
            },
            columns: [
                { "data": "HoleNo" },
                { "data": "Task" },
                { "data": "Length" },
                { "data": "Number" },
                { "data": "Width" },
                { "data": "Depth" }
            ]
        });

    }

jON

allan

Try adding cache: true to the Ajax configuration object. That is the only other thing that DataTables will set. It basically tells jQuery to add a timestamp as a query string parameter to make sure that the browser doesn't cache the response.

Its possible that the routing being used in .NET here doesn't like that extra parameter.

Allan

jonr

Sorry Allan, no dice I am afraid.

cache of true or false ... neither works.

jON

jonr

Can I refer you to my comment above Allan:

https://datatables.net/forums/discussion/comment/109565/#Comment_109565

after I have assigned the DataTable to a variable called table and passed it parameters (keys, ordering, sorting etc) if I put a breakpoint on line 64 in the above code, the values af these fields/methods/properties are all undefined.

That is why I suspect that my js libraries are clashing with each other.

Shouldn't the table variable reflect the properties passed to DataTable?

It could be my understanding of javascript objects and properties that are at fault here.

jON

allan

table.ordering
table.searching
table.keys

are all undefined.

Assuming that the table here is the result of the $('#actuals').DataTable() and you are just doing console.log( table.ordering ) (etc), then the only one that should be defined is table.keys which is an object for the KeyTable methods - assuming KeyTable is loaded. There is no ordering() or searching() methods (the full list of methods is here).

Shouldn't the table variable reflect the properties passed to DataTable?

No, its a DataTables API instance with its own methods defined.

Can you show me the full headers for the two requests from the "Header" section of the request information please?

It might also be useful if you could run the debugger on the page and let me know the debug code.

Allan

bindrid

Have you used the debugger command as shown in this code fragment to stop JavaScript execute so you can examine all of the variable to see if you have what you think you have?

 success: function (res) {
            debugger;
            justDataTable(res.d);
        },

        error: function (x, y) {
            debugger;
            console.log(x);

        }

In case you do not know it, if your console is open, code execution will stop on the debugger command until you click on the console resume button.

bindrid

In desperation, have you tried creating an ASMX page for your web methods instead of using the ASPX page?

jonr

Goes a little against the grain BIndrid.

I do everything with ajax method calls in my aspx and it works everywhere else.

it works fine in the call that I run just before the DataTables call.

something else is going on here.

I will try and get my code into a separate page with only the js files listed in the examples. I may be able to use a separate page as an overlay using Bootstrap Modal.

jON

kthorngren

Not sure if this will work in your environment but can you assign the result of the external ajax call to a variable, data for example. Remove the ajax call within Datatables and use data: data to apply your data?

Just throwing ideas out

Kevin

bindrid

Should not be "against the grain". My pages have multiple tabs that do not load until the user clicks on the tab. Server side is all web methods. Front end is all ajax (and no, not the Microsoft Ajax Toolkit). I have a number of different configurations.
My home page (its inside a firewall so you cant see it) is serverSide:true with the main search, individual column searches and even a form of the pipeline caching you see in the DataTables website.

In most location, the data is more static so I use an ajax call outside of the table then set up the table.

These are all going against web methods on my asmx page.