locked
Unable to bind Json data in jquery datatable with correct data RRS feed

  • Question

  • User411771660 posted

    I am learning jquery tables for a report.I am getting api response all fine.But i am unable to bind it to the jquery datatable.Looked many questions/examples similar to this.I am getting json data from ajax call.In my case i am passing three params to my web service.

    Web Service

     [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public void GetDepositAccountStatement(long accountNumberId,
            string fromDate, string toDate)
        {
            var frmDate = DatabaseLayer.Core.DateConversion.GetEnglishDate(fromDate).ToDateTime();
            var tDate = DatabaseLayer.Core.DateConversion.GetEnglishDate(toDate).ToDateTime();
            var res = BusinessLayer.Deposit.DepositAccountStatement.GetDepositAccountStatementViews(accountNumberId,
                frmDate, 
           JavaScriptSerializer js = new JavaScriptSerializer();
           Context.Response.Write(js.Serialize(res));            
        }
    

    This WebService returns this Json

    [{"TranId":null,"TranDate":"\/Date(1492107300000)\/","ValueDate":"\/Date(1492107300000)\/","StatementReference":"Balance B/d.","Collector":null,"Debit":null,"Credit":17252.08,"Balance":17252.08,"CheckNumber":null},{"TranId":null,"TranDate":null,"ValueDate":null,"StatementReference":"Total","Collector":null,"Debit":0,"Credit":17252.08,"Balance":null,"CheckNumber":null}]

    In my case i want to bind the data to the datatable on button click.Js code

     $('#ShowInGridButton').on('click', function () {
                var accountNumberId = $('#DepositAccountNumberIdHiddenField').val();
                var fromDate = $("input[name*='FromDateControl']").val();     
                var toDate = $("input[name*='ToDateControl']").val();
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    url: "/WebMethods/Deposit.asmx/GetDepositAccountStatement",
                    data: JSON.stringify({ accountNumberId: accountNumberId, fromDate: fromDate, toDate: toDate }),
                    success: function (data) {
                        $('#reportTable').DataTable({
                            data: data,
                            sort: true,
                            searching: true,
                            columns: [
                                { 'data': 'tran_id' },
                                { 'data': 'tran_date' },
                                { 'data': 'value_date' },
                                { 'data':'statement_reference'},
                                { 'data': 'collector' },
                                { 'data': 'debit' },
                                { 'data': 'credit' },
                                { 'data': 'balance' },
                                { 'data': 'check_number' }
                            ]
                        });
                    }
                });
            });

    And the html

    <table id="reportTable" class="display table table-responsive table-hover">  
                    <thead>  
                        <tr>  
                            <th>TranId</th>  
                            <th>TranDate</th>  
                            <th>ValueDate</th>  
                            <th>StatementReference</th>  
                            <th>Collector</th>  
                            <th>Debit</th>  
                            <th>Credit</th>  
                            <th>Balance</th>
                            <th>CheckNumber</th>                        
                        </tr>  
                    </thead>  
                </table>

    On button click i am unable to get the data and bind to the datatable. Help Please.

    Tuesday, July 24, 2018 9:46 AM

Answers

All replies

  • User475983607 posted

    Rather than serializing the result manually just return the type directly from GetDepositAccountStatementViews like a normal method.  This is a very common mistake for some reason...

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public TheType GetDepositAccountStatement(long accountNumberId, string fromDate, string toDate)
    {
    	var frmDate = DatabaseLayer.Core.DateConversion.GetEnglishDate(fromDate).ToDateTime();
    	var tDate = DatabaseLayer.Core.DateConversion.GetEnglishDate(toDate).ToDateTime();
    	return BusinessLayer.Deposit.DepositAccountStatement.GetDepositAccountStatementViews(accountNumberId, frmDate, toDate);          
    }

    Other than that, the columns returned do not match up.  There is a case issues and one column, statement_reference, does not match.   Take a moment to review your code.

    Tuesday, July 24, 2018 11:04 AM
  • User411771660 posted
    I wan the result in json.whats the problem sir?
    Tuesday, July 24, 2018 12:22 PM
  • User475983607 posted

    I wan the result in json.whats the problem sir?

    The framework automatically serializes service responses.   Simply return the appropriate type.  

    Can you explain why you are not following standard and openly documented practices?

    Tuesday, July 24, 2018 12:56 PM
  • User411771660 posted
    My lame approach perhaps.The normal gives me the response in xml whereas i want it in json for jquery datatable.
    Tuesday, July 24, 2018 1:21 PM
  • User475983607 posted

    tarun02

    My lame approach perhaps.The normal gives me the response in xml whereas i want it in json for jquery datatable.

    In my experience the default ASMX behavior returns JSON when the content-type is JSON.  How are you verifying XML results? Can you provide sample code that reproduces this behavior as I'm unable to on my side.

    Another option is implementing an Web API service as Web API is RESTful and handles JSON out-of-the-box.

    Tuesday, July 24, 2018 1:30 PM
  • User411771660 posted
    Sir, this is legacy app ( asp.net webforms) , i understand for this particular module i could use webapi.but that would be a lot of work,when this approach working already.
    "ASMX behavior returns JSON when the content-type is JSON" .So with ajax call with content type json.this will work?
    Tuesday, July 24, 2018 1:38 PM
  • User475983607 posted

    "ASMX behavior returns JSON when the content-type is JSON" .So with ajax call with content type json.this will work?

    As stated, that is my experience.  If you have different results then post code that reproduces the unwanted results so we can see what you are doing.  I can't reproduce your stated results.

    Tuesday, July 24, 2018 1:51 PM
  • User411771660 posted
    Will get back to you shortly.sir.
    Tuesday, July 24, 2018 2:01 PM
  • User-82109136 posted

    I don't use jQuery DataTables, so this may be off base, but the json returned by the web service doesn't seem to match the column names you are using.

    Example: The json value of "TranId". The DataTable column of "tran_id"

    Have you tried matching the names

    Tuesday, July 24, 2018 2:02 PM
  • User411771660 posted
    I did tried..didnt worked.
    Tuesday, July 24, 2018 3:31 PM
  • User475983607 posted

    I did tried..didnt worked.

    What did you try?  What does "didn't work" mean?  

    The WebMethod returns XML?  The jquery table did not update?   Did you fix the column names mentioned in my  first post and mentioned by toddlf?    

    Tuesday, July 24, 2018 3:42 PM
  • User411771660 posted
    Yes sir.i did.
    Tuesday, July 24, 2018 4:12 PM
  • User36583972 posted


    Hi tarun02,

    You can refer the following link's suggestion. They share some different solutions.

    Json Output of a C# WebMethod Does Not Affect a JQuery's DataTable
    https://stackoverflow.com/questions/28176136/json-output-of-a-c-sharp-webmethod-does-not-affect-a-jquerys-datatable

    How can I use AJAX with web methods to send JSON objects to a jQuery DataTable using asp.net?
    https://stackoverflow.com/questions/38211053/how-can-i-use-ajax-with-web-methods-to-send-json-objects-to-a-jquery-datatable-u

    DataTables with WebMethod
    http://code.datatables.net/forums/discussion/42032/datatables-with-webmethod

    If your issue still exists, please go to the JQuery dataTables forum for getting suitable help.

    Best Regards,

    Yong Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 25, 2018 6:46 AM