Unable to get JSON Data to Jquery Datatable.

Tushit · March 2017

I have some Json data which I got from sql server by querying the database. The data was serialized to json using java script serializer. But Im unable to bind the Json Data to jQuery datatables.
Here is my java script code

<script src="Scripts/jquery-3.1.1.js"></script>
    <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
    <script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $.ajax({
                url: 'UserService.asmx/UserDetails',
                method: 'post',
                datatype: 'json',
                contentType: "application/json; charset=utf-8",
                success: function (data) {
                    $('#tbl').dataTable({
                        data: data,
                        "columns": [
                        { "data": "UserId" },
                        { "data": "UserName" },
                        { "data": "UserPassword" },
                        { "data": "RoleId" },
                        { "data": "Gender" },
                        { "data": "EmailId" },
                        { "data": "DateOfBirth" },
                        { "data": "Address" },
                        ]
                    });
                },
                error: function (xhr, ajaxOptions, thrownError)
                {
                    alert(xhr.responseText);
            }
            });
        });
    </script>

The error section alert is giving me few rows from Json Data. And if I remove that content type parameter from the ajax, its throwing an error mention on this link https://datatables.net/manual/tech-notes/4 Any help is highly appriciated.

allan · March 2017

Can you show me what is in the data property please?

Allan

Tushit · March 2017

[{'UserId':'XYZ','UserName':'XYX','UserPassword':'XYX@1234','RoleId':1,'Gender':'F','EmailId':'XYX@email.com','DateOfBirth':'\/Date(623874600000)\/','Address':'ABC Str. 57'}, {'UserId':'ANATR','UserName':'ANT','UserPassword':'ABC@1234','RoleId':1,'Gender':'F','EmailId':'ANT@email.com','DateOfBirth':'\/Date(-301815000000)\/','Address':'XYZ. 2222 {'UserId':'WOLZA','UserName':'DIS','UserPassword':'AB@1234','RoleId':2,'Gender':'M','EmailId':'DIV@email.com','DateOfBirth':'\/Date(379362600000)\/','Address':'ul. JUMP 68'}]

This is the Json Data that I'm getting from web service

kthorngren · March 2017

That JSON is not formatted correctly. You can test it yourself at:
http://jsonlint.com/

The end of the second line is missing '},. If this is not a copy / paste error then the web service is sending incorrectly formatted data.

If its a copy / paste problem then what is the specific error you are getting?

Do you have 8 columns defined in your table?

Kevin

Tushit · March 2017

Sorry, my bad. It was just a copy paste error. The problem here is if i dont use contentType attribute Im getting the Error mentioned at this link https://datatables.net/manual/tech-notes/4 but if i use that attribute, Im getting few rows of my data. Yes, I do have 8 columns specified in my table. Here is my code
<script type="text/javascript"> $(document).ready(function () { $.ajax({ url: 'UserService.asmx/UserDetails', method: "POST", contentType: "application/json; charset=utf-8", dataType: "json", success: function (data) { var d = JSON.parse(data.d); $('#tbl').dataTable({data:d, paging: true, sort: true, searching: true, scrollY: 200, data: d, aoColumns: [ { 'data': 'UserId' }, { 'data': 'UserName' }, { 'data': 'UserPassword' }, { 'data': 'RoleId' }, { 'data': 'Gender' }, { 'data': 'EmailId' }, { 'data': 'DateOfBirth' }, { 'data': 'Address' }, ] }); }, error: function (xhr, ajaxOptions, thrownError) { alert(xhr.responseText); } }); }); </script>
And I have also tested the Json data at the link you mentioned. It said, its a valid json, so im not sure what seems to be the problem.
Here is my html part.

<body>
    <form id="form1" runat="server">
        <table id="tbl">
            <thead>
                <tr>
                    <th>UserId</th>
                    <th>UserName</th>
                    <th>UserPassword</th>
                    <th>RoleId</th>
                    <th>Gender</th>
                    <th>EmailId</th>
                    <th>DateOfBirth</th>
                    <th>Address</th>
                </tr>
            </thead>
        </table>
    </form>
</body>

kthorngren · March 2017

To make sure I understand if you have contentType then Datatables loads just a few of the rows. When you removed contentType then you get the Unknown parameter error. Is this correct?

Can you provide a link to the page so it can be debugged?

If not then please use the debugger to get information for both cases.

Kevin

Tushit · March 2017

oqulef- this is the unique code that I got from debugger. Not sure how to proceed further with it.

kthorngren · March 2017

Nothing is showing in the debugger output but that you are using Datatables 1.10.7. That version is old and probably should be upgraded. The current version is 1.10.13. But the version is probably not the problem with getting the data loaded.

I think the problem may be that the json being returned needs to be parsed. Try changing the first part of your success function to this:

                success: function (data) {
                    var data = JSON.parse(data);
                    $('#tbl').dataTable({
                        data: data,
                        "columns": [
                             ........

Kevin

Tushit · March 2017

I have already parsed it. Not sure what is happening.

<script type="text/javascript">
        $(document).ready(function () {
            $.ajax({
                url: 'UserService.asmx/UserDetails',
                method: "POST",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (data) {
                    var d = JSON.parse(data);
                    $('#tbl').dataTable({
                        data: d,
                        paging: true,
                        sort: true,
                        searching: true,
                        scrollY: 200,
                        Columns: [
                        { 'data': 'UserId' },
                        { 'data': 'UserName' },
                        { 'data': 'UserPassword' },
                        { 'data': 'RoleId' },
                        { 'data': 'Gender' },
                        { 'data': 'EmailId' },
                        { 'data': 'DateOfBirth' },
                        { 'data': 'Address' },
                        ]
                    });
                },
                error: function (xhr, ajaxOptions, thrownError)
                {
                    alert(xhr.responseText);
            }
            });
        });
    </script>

kthorngren · March 2017

What is the result of console.log(d) if you place the command right after var d = JSON.parse(data);?

Is it a data object with an array of objects containing your data?

Kevin

allan · March 2017

Columns: [

Should be lower case. That's probably the the issue.

Allan

Tushit · March 2017

I believe so, the web service is resulting in JSON. Console.log(d) is not writing anything on console. neither does document.write(d).
@allan I have tried with lower case as well, still no luck

I guess since its not going inside success function, its not printing anything. But I tried the same thing in error part as well, still no luck. But the alert doesnt come up.
Im fairly new to Jquery, so please dont mind me asking silly questions.

kthorngren · March 2017

Next I would try console.log(data) before theJSON.parse` statement. Do you get anything?

You can also try something like console.log('success') as the first line in your success function to see if it is executing.

Kevin

Tushit · March 2017

I have tried that, but it seems like its not executing success function.Its going to error only. success: function (data) { console.log(data); alert('success'); var d = JSON.parse(data); $('#tbl').dataTable({ data: d, columns: [ { 'data': 'UserId' }, { 'data': 'UserName' }, { 'data': 'UserPassword' }, { 'data': 'RoleId' }, { 'data': 'Gender' }, { 'data': 'EmailId' }, { 'data': 'DateOfBirth' }, { 'data': 'Address' }, ] }); },

Tushit · March 2017

One thing I dont understand is that, when the web service is returning the data, why is it always going to error:.

allan · March 2017

If its always dropping into the error function it means either:

The JSON is not valid
The response from the server has an error code in the HTTP status

A combination of the network tools in your browser's developer console and the error message in the error handler will tell you what is happening.

Allan

Tushit · March 2017

Protocol Method Result Type Received Taken Initiator Wait‎‎ Start‎‎ Request‎‎ Response‎‎ Cache read‎‎ Gap‎‎
HTTP GET 200 application/json 235 B 6.08 s XMLHttpRequest 147904 16 6068 0 0 165283
HTTP GET 200 application/json 195 B 6.08 s XMLHttpRequest 153988 0 6084 0 0 159199
HTTP GET 200 application/json 235 B 6.06 s XMLHttpRequest 160088 0 6069 0 0 153114
HTTP GET 200 application/json 235 B 6.06 s XMLHttpRequest 166172 0 6069 0 0 147030
HTTP GET 200 application/json 235 B 6.08 s XMLHttpRequest 172241 0 6084 0 0 140946
HTTP GET 200 application/json 235 B 6.25 s XMLHttpRequest 178325 0 6084 171 0 134691
HTTP GET 200 application/json 235 B 6.94 s XMLHttpRequest 184580 16 6926 0 0 127749
HTTP GET 200 application/json 195 B 6.06 s XMLHttpRequest 191522 0 6069 0 0 121680
HTTP GET 200 application/json 235 B 6.08 s XMLHttpRequest 197591 0 6084 0 0 115596
HTTP GET 200 application/json 235 B 6.08 s XMLHttpRequest 203690 0 6069 15 0 109497
HTTP GET 200 application/json 235 B 6.06 s XMLHttpRequest 209774 0 6069 0 0 103428
HTTP GET 200 application/json 235 B 6.08 s XMLHttpRequest 215858 0 6084 0 0 97329
HTTP GET 200 application/json 235 B 6.06 s XMLHttpRequest 221942 0 6069 0 0 91260
HTTP GET 200 application/json 235 B 6.08 s XMLHttpRequest 228027 0 6084 0 0 85160

This is the response I got from debugging. all the status code are 200 only. And the Json data I tested, is valid json. I tested it on jsonlint.com to be sure

Tushit · March 2017

While Debugging, the response is coming in this format which is giving a syntax error, should it be adding those backslashes
response "\"[{UserId:ALFKI,UserName:Accorti,UserPassword:ABCD@1234,RoleId:1,Gender:F,EmailId:Accorti@mail.com,DateOfBirth:1985 - 03 - 16,Address:Obere Str. 57}]\"{\"d\":null}"

and what is \"{\"d\":null}" at the end of the response. I checked the result with one row of data and found this. This is resulting in statusText part to parsererror. Not sure what that means

Tushit · March 2017

Finally, I was able to solve it. There was no issue with either server side processing or my javascript code. I just had to define HTTPGET and HTTPPOST requests in my web. config file. Thank you all for your time and help. Got to learn a lot of new things. Much appriciated.

allan · March 2017

Thanks for posting back. Good to hear you have it working now.

Allan

Caroline2120 · November 2017

Good day,
Please i am having this same problem. my webservice contains record but not dispalying in my datatable. please any solution

Caroline2120 · November 2017

this is my webservice

[WebMethod]
public void GetInstitutions()
{
DataSet ds = new DataSet();
DatabaseFunctions db = new DatabaseFunctions();
List<InstitutionList> institutions = new List<InstitutionList>();
ds = db.getInstitutions();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
InstitutionList institution = new InstitutionList();
institution.Id = Convert.ToInt32(ds.Tables[0].Rows[i]["ID"].ToString());
institution.Code = ds.Tables[0].Rows[i]["SchCode"].ToString();
institution.Name = ds.Tables[0].Rows[i]["Name"].ToString();
institution.State = ds.Tables[0].Rows[i]["State"].ToString();
institution.Type = ds.Tables[0].Rows[i]["SchType"].ToString();

                institutions.Add(institution);
            }
        }
        JavaScriptSerializer js = new JavaScriptSerializer();
        Context.Response.Write(js.Serialize(institutions));
    }

this is the script

$(function () {
$.ajax({
type: "POST",
url: "MyWebServices.asmx/GetInstitutions",
dataType: "json",
success: function (data) {

                 $('#datatable').dataTable({
                   data: data,
                   "columns": [
                        { 'data': 'Id' },
                        { 'data': 'Code' },
                        { 'data': 'Name' },
                        { 'data': 'State' },
                        { 'data': 'Type' }
                    ]
                });
            },
            error: function (error) {
                alert('error');
                alert(error.toString());
            }

        });
        return false;
    });

allan · November 2017

What is the MyWebServices.asmx/GetInstitutions script returning please? See this tech note if you aren't sure how to get that information.

Allan

Unable to get JSON Data to Jquery Datatable.

Unable to get JSON Data to Jquery Datatable.

Answers

Howdy, Stranger!

Categories

DataTables

Unable to get JSON Data to Jquery Datatable.

Unable to get JSON Data to Jquery Datatable.

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables