0

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)

3
  • Since you are using web services from an asmx page, it will not work the way have it. Commented Apr 27, 2017 at 2:26
  • so I have to use ashx handlers? Commented Apr 27, 2017 at 2:27
  • no, you just need to change your table deleration. Give me a few minutes Commented Apr 27, 2017 at 2:28

2 Answers 2

1

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"}]

        });

    });
Sign up to request clarification or add additional context in comments.

10 Comments

Thanks, I received this error,,, DataTables warning: table id=RegSrc2 - Requested unknown parameter '0' for row 0, column 0. For more information about this error
I changed the data type from array of arrays to array of objects by adding the columns section
you also referred to my data table declaration, I m posting the final codes below,,,,,, Thanks a lot
binderi, sorry but i cant send data through this code function (dtParms),,,,,,, I used to send data by defining var obj = {}; then add params to it like obj.searchtxt and then in AJAX call data section I use JSON.stringify(obj) ,,,, I tried many other ways i keep getting bad webservice call,,, could please advise
Because DataTables documentation tells you that using success can have unintended side effects since they are using it to populate the table with data. dataFilter is called before success so you can accomplish the same things without messing with DataTables functionality.
|
0

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"}]

    });

});

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.