How to load data JSON to DataTable?

headshot9x

Hello guys. I use DataTable load data from webservice. I debug and get result at http://debug.datatables.net/ixacav (please see it).
And this is WebMethod

 [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string ListLocation()
        {
            string json = "";
            using (QLTDEntities db = new QLTDEntities())
            {
                var list = db.LOCATIONs.Where(e => e.FLAG == true).ToList().Select(e => new LOCATION()
                {
                    LOCATION_ID = e.LOCATION_ID,
                    AREA_ID = e.AREA_ID,
                    LOCATION_NAME = e.LOCATION_NAME,
                    LOCATION_DES = e.LOCATION_DES,
                    EDIT_DATE=e.EDIT_DATE,
                    EDIT_BY=e.EDIT_BY,
                    FLAG=e.FLAG
                }).ToList();
                foreach (var item in list)
                {
                    item.TOPICs.ToList().ForEach(i => i.LOCATION = null);
                    item.USERS_PROFILE.ToList().ForEach(i => i.LOCATION = null);
                }
                return  json = JsonConvert.SerializeObject(list);
            }
        }

Page.aspx not load data . Please give me your advice. Thank you .(as allan)

allan

Your Ajax response is not valid JSON - that needs to be corrected (how exactly, I don't know. I would suggest asking in a C# forum if you don't know yourself).

Also remove serverSide: true. You have not implemented server-side processing.

Allan

headshot9x

I have remove follow your advice

 "bProcessing": true,
                "bServerSide": false,

My Ajax Json is right , I test webservice and get result

<string xmlns="http://tempuri.org/">
[{"LOCATION_ID":"L0001","AREA_ID":"A0001","LOCATION_NAME":"Ninh Bình","LOCATION_DES":"NB","EDIT_DATE":"2014-11-05T00:00:00","EDIT_BY":"user 1","FLAG":true,"AREA":null,"TOPICs":[],"USERS_PROFILE":[]},{"LOCATION_ID":"L0002","AREA_ID":"A0002","LOCATION_NAME":"Đồng Nai","LOCATION_DES":"ĐN","EDIT_DATE":"2014-11-05T00:00:00","EDIT_BY":"user 1","FLAG":true,"AREA":null,"TOPICs":[],"USERS_PROFILE":[]},{"LOCATION_ID":"L0003","AREA_ID":"A0003","LOCATION_NAME":"Quảng Ngãi","LOCATION_DES":"QN","EDIT_DATE":"2014-11-05T00:00:00","EDIT_BY":"user 1","FLAG":true,"AREA":null,"TOPICs":[],"USERS_PROFILE":[]},{"LOCATION_ID":"L0004","AREA_ID":"A0001","LOCATION_NAME":"Nam Định","LOCATION_DES":"NĐ","EDIT_DATE":"2014-11-05T00:00:00","EDIT_BY":"user 1","FLAG":true,"AREA":null,"TOPICs":[],"USERS_PROFILE":[]},{"LOCATION_ID":"L0005","AREA_ID":"A0002","LOCATION_NAME":"Bình Dương","LOCATION_DES":"BD","EDIT_DATE":"2014-11-05T00:00:00","EDIT_BY":"user 1","FLAG":true,"AREA":null,"TOPICs":[],"USERS_PROFILE":[]},{"LOCATION_ID":"L0006","AREA_ID":"A0003","LOCATION_NAME":"Huế","LOCATION_DES":"H","EDIT_DATE":"2014-11-05T00:00:00","EDIT_BY":"user 1","FLAG":true,"AREA":null,"TOPICs":[],"USERS_PROFILE":[]}]
</string>

Tell me about problem in here ? thank you so much.

tangerine

That is not valid JSON. You need to lose the <string></string> tags.

allan

You can use JSONLint to test your JSON. As tangerine says, it is not valid.

Allan

headshot9x

Sorry I did not answer. I confirm that this is the JSON string returned when running Webservice, because when I run Webservice returns the result as such. I've searched and tested a lot through google, JSON string using Webservice to run and the results are the same.

<string xmlns="http://tempuri.org/">
[{"LOCATION_ID":"L0001","AREA_ID":"A0001","LOCATION_NAME":"Ninh Bình","LOCATION_DES":"NB","EDIT_DATE":"2014-11-05T00:00:00","EDIT_BY":"user 1","FLAG":true,"AREA":null,"TOPICs":[],"USERS_PROFILE":[]}]
</string>

I just do not understand when running Datatables the back of the results is

<? xml version = "1.0" encoding = "utf-8"?> <string xmlns = "http://tempuri.org/"> .json string </ string>

(I've used DevTool of GoogleChorme to see: Network> XHR and see my JSON string is added to the

<? xml version = "1.0" encoding = "utf-8"?>)

Please give me some advice. Thank you.

allan

It might be that you need to use contentType in the Ajax request to force the .NET web service to return JSON rather than XML.

As we noted above, the current return is not valid JSON. You need to modify something (be it in the request or the server-side) to have it return valid JSON.

Allan

headshot9x

Dear allan. Thank for your reply. I will show all code in my page which is try . I think problem at Datatable. I apologize because I have to write so much so, I thought to write specific to you and other people can understand the problem I was suffering.If I write a detailed and specific so you can understand all the issues I was faulty.
Ok, First in my Webservice , I have a function ListLocation as return JSON

[WebMethod]
       [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
       public string ListLocation()
       {
           string json = "";
           using (QLTDEntities db = new QLTDEntities())
           {
               var list = db.LOCATIONs.Where(e => e.FLAG == true).ToList().Select(e => new LOCATION()
               {
                   LOCATION_ID = e.LOCATION_ID,
                   AREA_ID = e.AREA_ID,
                   LOCATION_NAME = e.LOCATION_NAME,
                   LOCATION_DES = e.LOCATION_DES,
                   EDIT_DATE=e.EDIT_DATE,
                   EDIT_BY=e.EDIT_BY,
                   FLAG=e.FLAG
               }).ToList();
               foreach (var item in list)
               {
                   item.TOPICs.ToList().ForEach(i => i.LOCATION = null);
                   item.USERS_PROFILE.ToList().ForEach(i => i.LOCATION = null);
               }
               return  json = JsonConvert.SerializeObject(list);
           }
       }

I run it and get Result as JSON

<string xmlns="http://tempuri.org/">
[{"LOCATION_ID":"L0001","AREA_ID":"A0001","LOCATION_NAME":"Ninh Bình","LOCATION_DES":"NB","EDIT_DATE":"2014-11-05T00:00:00","EDIT_BY":"user 1","FLAG":true,"AREA":null,"TOPICs":[],"USERS_PROFILE":[]}]
</string>

In next step , I use with test.aspx , I use DdaTable is lasted.

 <script src="theme/datatable/dataTables.bootstrap.js"></script>
    <link href="theme/datatable/datatable1.6/media/css/jquery.dataTables.css" rel="stylesheet" />
    <script src="theme/datatable/datatable1.6/media/js/jquery.js"></script>
    <script src="theme/datatable/datatable1.6/media/js/jquery.dataTables.js"></script>
    <script type="text/javascript" charset="utf-8">
        $(document).ready(function () {
            $('#example').DataTable({
                "processing": false,
                "serverSide": false,
                "ajax": {
                    "url": "../BUS/WebService.asmx/ListLocation",
                    "dataSrc": "d",
                    "dataType": "json",
                    "contentType": "application/json; charset=utf-8",
                    "type": "POST"
                },
                "aoColumns": [
                    { "mData": "LOCATION_ID" },
                    { "mData": "AREA_ID" },
                    { "mData": "LOCATION_NAME" },
                    { "mData": "LOCATION_DES" },
                    { "mData": "EDIT_DATE" },
                    { "mData": "EDIT_BY" },
                    { "mData": "FLAG" },
                    { "mData": "AREA" },
                    { "mData": "TOPICs[]" },
                    { "mData": "USERS_PROFILE[]"}
                ]           
            });
        });

Ok,. this time i get info from DevTool Chrome

Remote Address:[::1]:30040
Request URL:http://localhost:30040/BUS/WebService.asmx/ListLocation
Request Method:POST
Status Code:200 OK
Response Headers
view source
Cache-Control:private, max-age=0
Connection:Close
Content-Length:1399
Content-Type:application/json; charset=utf-8
Date:Mon, 06 Apr 2015 14:11:31 GMT
Server:ASP.NET Development Server/11.0.0.0
X-AspNet-Version:4.0.30319
Request Headers
view source
Accept:application/json, text/javascript, */*; q=0.01
Accept-Encoding:gzip, deflate
Accept-Language:en-US,en;q=0.8
Cache-Control:max-age=0
Connection:keep-alive
Content-Length:0
Content-Type:application/json; charset=utf-8
Host:localhost:30040
Origin:http://localhost:30040
Referer:http://localhost:30040/UI/WebForm5.aspx
User-Agent:Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.118 Safari/537.36
X-Requested-With:XMLHttpRequest

It return Json as follow in Respone

{"d":"[{\"LOCATION_ID\":\"L0001\",\"AREA_ID\":\"A0001\",\"LOCATION_NAME\":\"Ninh Bình\",\"LOCATION_DES\":\"NB\",\"EDIT_DATE\":\"2014-11-05T00:00:00\",\"EDIT_BY\":\"user 1\",\"FLAG\":true,\"AREA\":null,\"TOPICs\":[],\"USERS_PROFILE\":[]}]"}

And it is get error at

Uncaught TypeError: Cannot read property 'length' of undefined         jquery.dataTables.js:1260

Well , can you give me some advice . Thank you so much.

allan

Your "JSON" is being returned with a single parameter (d) which is a string. You need to JSON decode the rest. You could use ajax.dataSrc for that:

ajax: {
  url: '...',
  dataSrc: function ( json ) {
    return $.parseJSON( json.d );
  }
}

Allan

headshot9x

Dear allan.
It's good to know my JSON string is always right. The error here is how I declare to the DataTable ajax. I am very sorry about this issue, I will refer to many articles about issues . Again thank you.