Datatable Jquery Plugin implementation

Question

I keep getting "invalid json response" from datatable Jq plugin, I m posting my webmethod and HTML structure along with AJAX call, I suspect that my web method is not serializing to JSON format properly, but when I tested the format I found it valid (http://jsonlint.com/). I can't figure out the problem here

  <WebMethod()> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json, UseHttpGet:=False, XmlSerializeString:=False)> _
Public Function SrcTblRegx()
    Dim constr As String = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("TblRegSearchx", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = con
            Dim ds As New DataSet()
            Using sda As New SqlDataAdapter(cmd)
                sda.Fill(ds)
            End Using
            Dim jsondata As String = JsonConvert.SerializeObject(ds)
            Return jsondata
        End Using
    End Using
End Function

The Webmethod Output is

<anyType xmlns:q1="http://www.w3.org/2001/XMLSchema" xmlns:d1p1="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/" d1p1:type="q1:string">
{"Table":[{"Filenum":15112777,"FullName":"marwam saleh moh saleem","DOB":"2015-11-26T00:00:00"}]}
</anyType>

<table id="RegSrc2" class="table table-bordered table-striped">
                            <thead>
                                <tr>
                                    <th><b>File Number</b></th>
                                    <th><b>Patient Name</b></th>
                                    <th><b>DOB</b></th>
                                </tr>

                            </thead>
                            <tbody></tbody>
                            <tfoot>
                                <tr>
                                    <th><b>File Number</b></th>
                                    <th><b>Patient Name</b></th>
                                    <th><b>DOB</b></th>
                                </tr>
                            </tfoot>
                        </table>

 $(document).ready(function () {
                    var $table = $('#RegSrc2');
                    $table.dataTable({
                        bProcessing: true,
                        "serverSide": true,
                        "sAjaxSource": $table.data('../CONFIG/WebSerTblsSearch.asmx/SrcTblRegx'),
                    })
                });

I m adding my web browser error too

jquery.dataTables.js:3929 Uncaught TypeError: Cannot set property 'data' of null
    at _fnBuildAjax (jquery.dataTables.js:3929)
    at _fnAjaxUpdate (jquery.dataTables.js:3946)
    at _fnDraw (jquery.dataTables.js:3409)
    at _fnReDraw (jquery.dataTables.js:3528)
    at _fnInitialise (jquery.dataTables.js:4710)
    at loadedInit (jquery.dataTables.js:1320)
    at HTMLTableElement.<anonymous> (jquery.dataTables.js:1332)
    at Function.each (jquery-2.1.4.js:374)
    at jQuery.fn.init.each (jquery-2.1.4.js:139)
    at jQuery.fn.init.DataTable [as dataTable] (jquery.dataTables.js:869)

Answer 1

I made changes from the code above, i m using datatable instead of dataset.

this code for serializing datatable

Public Function DataTableToJSONWithJavaScriptSerializer(table As DataTable) As String
    Dim jsSerializer As New JavaScriptSerializer()
    Dim parentRow As New List(Of Dictionary(Of String, Object))()
    Dim childRow As Dictionary(Of String, Object)
    For Each row As DataRow In table.Rows
        childRow = New Dictionary(Of String, Object)()
        For Each col As DataColumn In table.Columns
            childRow.Add(col.ColumnName, row(col))
        Next
        parentRow.Add(childRow)
    Next
    Return jsSerializer.Serialize(parentRow)
End Function

Ref: http://www.c-sharpcorner.com/UploadFile/9bff34/3-ways-to-convert-datatable-to-json-string-in-Asp-Net-C-Sharp/

Server side Processing, webmethod (stored procedure)

 <WebMethod()> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
       Public Function SrcTblReg(ByVal SrchTxt As String)
        
        Dim constr As String = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("TblRegSearch", con)
            cmd.CommandType = CommandType.StoredProcedure
            If (String.IsNullOrEmpty(SrchTxt)) Then
                cmd.Parameters.Add("@Searchtxt", SqlDbType.NVarChar).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@Searchtxt", SqlDbType.NVarChar).Value = SrchTxt.Trim()
            End If
                cmd.Connection = con
                Using sda As New SqlDataAdapter(cmd)
                    Dim dt As New DataTable()
                    sda.Fill(dt)
                    Dim sJSON = DataTableToJSONWithJavaScriptSerializer(dt)
                    Return sJSON
                End Using
                'Dim jsondata As String = JsonConvert.SerializeObject(ds)
                'Return jsondata
            End Using
        End Using
    End Function

Finally Client side as provided by Bindrid.

$(document).ready(function () {

    $('#example').DataTable({

        "processing": false,
        // since you are getting all the data at once set serverSide to
        // false, otherwise using the built in search and paging will trigger
        // more ajax calls to get the same data.
        "serverSide": false,
        "ajax": {

            // returning the data from the server as json data
            contentType: "application/json; charset=utf-8",
            // assuming your url is right....
            url: "../CONFIG/WebSerTblsSearch.asmx/SrcTblRegx",
            type: "Post",

         // You are not sending parameters to the server so next line commented out
            data: function (dtParms) { 
                return  JSON.stringify({ SrchTxt: "your search string" }); 
            },
            dataFilter: function (res) {

                // do what you need to the data before it loads to the table
                // first deserialization
                var parsed = JSON.parse(res);
               // now you can access the data in d and deserialize that
                var morp = JSON.parse(parsed.d);

                // reserialize to what datatables expect.
                return JSON.stringify( { data: morp });
            },
            error: function (x, y) {

                console.log(x);

            }
        },
        columns:[
            {data:"Filenum"},
            {data: "FullName"},
            {data:"DOB"}]

    });

});

Answer 2

Here is the way I do it. I have had more success using a post instead of a get with web services. Also, because your web service is serializing (I do it the same way) the json object ends up getting serialized twice. Also, data tables is expecting the data to be in the form of {data: [your data] } and web services returns {d:[your data serialized]} you have to make adjustments client side. Here is how I do it.

    $(document).ready(function () {

        $('#example').DataTable({

            "processing": false,
            // since you are getting all the data at once set serverSide to
            // false, otherwise using the built in search and paging will trigger
            // more ajax calls to get the same data.
            "serverSide": false,
            "ajax": {

                // returning the data from the server as json data
                contentType: "application/json; charset=utf-8",
                // assuming your url is right....
                url: "../CONFIG/WebSerTblsSearch.asmx/SrcTblRegx",
                type: "Post",

             // You are not sending parameters to the server so next line commented out
                data: function (dtParms) { 
                    return  JSON.stringify({ SrchTxt: "your search string" }); 
                },
                dataFilter: function (res) {

                    // do what you need to the data before it loads to the table
                    // first deserialization
                    var parsed = JSON.parse(res);
                   // now you can access the data in d and deserialize that
                    var morp = JSON.parse(parsed.d);

                    // reserialize to what datatables expect.
                    return JSON.stringify( { data: morp });
                },
                error: function (x, y) {

                    console.log(x);

                }
            },
            columns:[
                {data:"Filenum"},
                {data: "FullName"},
                {data:"DOB"}]

        });

    });