How to populate the row.child using ".net" datatable as datasource?

alexle

I am a newbie to "jQuery DataTable Plugin" and I have also searched the forum to find a solution to my problem but no avail. Please forgive me if my question is redundant.

Basically, I have a web form that displays the search result using jQuery DataTable plugin. I want to be able to click on a (+) to expand the detail information for the selected row. To populate the data for the master table, I used code-behind to extract the data from the database. For the detail table, I used "web method" in the code-behind with 2 input parameters (primary key and environment). I made sure my web method indeed to the result from the database via unit-test. For the life of me, I cannot get the client (web browser) to display the data when I clicked on the ( + ) sign. I noticed that the responsJson.Message = Authentication failed. I attached my code so everyone can see what I am doing wrong.

These are the errors: SearchCustomer:1685
Uncaught TypeError: Cannot read property 'show' of undefined
* at HTMLTableCellElement.<anonymous> (SearchCustomer:1685)
* at HTMLTableSectionElement.dispatch (jquery-1.12.4.js:5226)
* at HTMLTableSectionElement.elemData.handle (jquery-1.12.4.js:4878)
* (anonymous)

POST http://localhost:1882/SearchCustomer.aspx/GetLoanHistory 401 (Unauthorized)
* send @ jquery-1.12.4.js:10254
* ajax @ jquery-1.12.4.js:9738
* format @ SearchCustomer:1632
* (anonymous)

allan

If this line:

row.child(format(row.data())).show();

Is causing Cannot read property 'show' of undefined, it suggests that format(...) is actually returning undefined (and thus child() would be used as a getter rather than a setter).

Looking at the format function, that is indeed correct.

Have a read over this blog post which describes how to do what you want to do.

Allan

alexle

Thank you for replying. I will look at the blog you recommended.

alexle

Hi Allan, I am very inexperienced with the DataTable plugin so I am not clear to use Row.Child() API as a getter. I looked at the example it basically showed me what I have already done.

When the user clicks on the + plus, I pass "a key" to the web method. The web method returns a JSON serialized string. How do you call a web method in code-behind from jQuery? I have attached all my code to the original post.

allan

My point was that you don't really want to use it as a getter here.

If you look at the blog post again, you'll see that the format function used there returns something (return div;). At the moment your function isn't returning anything which is causing the issue.

If you return the div, then you can address that, as shown in the blog post's code, in the Ajax success handler to update it with the content returned from the server.

Allan

alexle

Using the example you recommended as a guide, I manage to get some data returned. However, my ajax calling the web method kept bombing out during "404 Not Found" error. Yes, I and my co-workers verified the URL for correct syntax. I also used "resolverURL" .

The response error show "UnAuthorized" and then "404 Not Found". Being new to jQuery and JavaScript, I spent the entire day on this bug.

kthorngren

"UnAuthorized" and then "404 Not Found"

Did you look at your server logs to determine why its responding with unauthorized?

Maybe you need to change your ajax type to POST. Its hard to say without seeing your code and know why the server is responding with the 404 unauthorized error.

Kevin

alexle

kthorngren,

Here is my jQuery Code. The $.ajax never hit.

            // Basic jQuery DataTable
            var table = $('#dtCustomerSearchResult').DataTable({
                "scrollY": 450,
                data: jsonData,
                columns: [
                    {
                        "className": 'details-control',
                        "orderable": false,
                        "data": null,
                        "defaultContent": ''
                    },
                    { 'data': "CUSTOMER_KEY" },
                    { 'data': "LASTNAME" },
                    { 'data': "FIRSTNAME" },
                    { 'data': "ADDRESS" },
                    { 'data': "CITY" },
                    { 'data': "STATE" },
                    { 'data': "DOB" },
                    { 'data': "EMAIL" },
                    { 'data': "VISITOR_KEY" }
                ]
            });


            // Add event listener for opening and closing details
            $('#dtCustomerSearchResult tbody').on('click', 'td.details-control', function () {
                var tr = $(this).closest('tr');
                var row = table.row(tr);

                if (row.child.isShown()) {
                    // This row is already open - close it
                    row.child.hide();
                    tr.removeClass('shown');
                }
                else {
                    // Open this row
                    row.child(format(row.data())).show();
                    tr.addClass('shown');
                }
            });

function format(d) {
      var parameters = {};
          parameters.customerKey = d.CUSTOMER_KEY;
            parameters.environment = '<%= SelectedEnvironment %>';
                  $.ajax({
                        type: "POST",
                             url: "SearchCustomer.aspx/GetLoanHistory",
                             data: JSON.stringify(parameters),
                              contentType: "application/json; charset=utf-8",
                              dataType: "json",
                               dataFilter: function (result) {
                                   // For debug purpose
                                   var parsed = JSON.parse(result);
                                r  eturn parsed.d;
                                 }
 };

Code Behind in C#

 [System.Web.Services.WebMethod]
 public static string GetLoanHistory(int customerKey, string environment)
 {
           DataTable dtLoanHistory = new DataTable();
            string jsonLoanHistory = string.Empty;

           dtLoanHistory = SearchService.GetCustomerLoanHistory(customerKey, environment);

            if (dtLoanHistory != null && dtLoanHistory.Rows.Count > 0)
                jsonLoanHistory = JsonConvert.SerializeObject(dtLoanHistory, Formatting.Indented);


            return jsonLoanHistory;
 }

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Your format function still isn't returning anything. Therefore it is actually returning undefined. That means that you will still get your Javascript error. As I noted above you must return something so that the row().child() method has something to show!

If $.ajax isn't being executed in the above code, you'd need to give us a link to a page showing the issue as I don't see any reason why it wouldn't be.

I don't know where the 404 is coming from in the server-side script. It might be that you have a HTTP authentication required and those parameters aren't being set, or there is some some authentication code missing in the headers and the server's router is returning the 404. Its impossible to say without seeing the system. However, I don't understand why it would get getting a 404 if the $.ajax isn't being hit as you say above, since there wouldn't be an Ajax return to return a 404!

Allan

alexle

Allan,

Finally, I got it to work. These are the 3 things that I did to get around all the errors that I experienced since posting the question.

1. Turned off the auto redirect in RouteConfig ( settings.AutoRedirectMode = RedirectMode.Off).
2. Move my SearchCustomer.aspx to root of the project (outside the Views folder).

3. Followed what you have recommended by getting the result instead of setting it.

               $.ajax({
                   type: 'POST',
                   url: pageUrl,
                   data: params,
                   contentType: 'application/json',
                   dataType: 'json',
                   success: function (result) {
   
                       console.log(result.d);
                       var loans = jQuery.parseJSON(result.d);
   
                       var htmlLoanHistory = '';
                       htmlLoanHistory += '<table id="loanHistory" class="cell-border">';
                       htmlLoanHistory += '<thead>';
                       htmlLoanHistory += '<tr> <th>Loan Key</th> <th>Location</th> <th>Date Entered</th> <th>Due Date</th> <th>Status</th> <th>Product Type</th> <th>Loan Type</th> <th>Pending ACH</th> <th>Loan Amount</th> <th>Product Key</th> <th>Config Key</th> </tr>';
                       htmlLoanHistory += '</thead>';
                      $.each(loans, function () {
                           htmlLoanHistory += '<tr>';
                           htmlLoanHistory += '<td> ' + this.LOAN_KEY + '</td>';
                           htmlLoanHistory += '<td> ' + this.LOCATION + '</td> ';
                           htmlLoanHistory += '<td> ' + this.DATE_ENTERED + '</td> ';
                           htmlLoanHistory += '<td> ' + this.DUE_DATE + '</td> ';
                           htmlLoanHistory += '<td> ' + this.CURRENT_STATUS + '</td> ';
                           htmlLoanHistory += '<td> ' + this.PRODUCT_TYPE + '</td> ';
                           htmlLoanHistory += '<td> ' + this.LOAN_TYPE + '</td> ';
                           htmlLoanHistory += '<td> ' + this.PENDING_ACH + '</td> ';
                           htmlLoanHistory += '<td> $' + this.LOAN_AMT + '</td> ';
                           htmlLoanHistory += '<td> ' + this.PRODUCT_KEY + '</td>';
                           htmlLoanHistory += '<td> ' + this.CONFIG_KEY + '</td>';
                          htmlLoanHistory += '</tr>';
                       });
   
                       htmlLoanHistory += '</table>';
   
                       row.child(htmlLoanHistory).show();
                       tr.addClass('shown');
                   },
                   failure: function (result) {
                       alert("Error : " + result.d);
                   },
                   error: function (result) {
                       alert("Error : " + result.d);
                   }
               });