locked
Datatables data filled from MySQL server RRS feed

  • Question

  • User1108996056 posted

    Hello,

    I am attempting to run a simple ASP.NET datatable using jQuery and ajax. I have a functioning table when I run it with dummy data, however when attempting to connect to a remote MySQL server the data does not get retrieved and the table gets stuck 'Processing'.

    My connection string in web.config

      <connectionStrings>
        <add name="DBConnection"
        connectionString="Server=studentserver.com.au;Database=admin_it_studies_dev;Uid=***;Pwd=***;"
        providerName="MySql.Data.MySqlClient" />
      </connectionStrings>

    Just using one row of data for now

    namespace CFI_Web_Application.Models
    {
        [Table("crn_detail")]
        public class Subjects
        {
                [Key]
                public int? CRN { get; set; }
          
        }
    }

    The ajax side of things

     <script>
            $(document).ready(function () {
                $("#subjectGrid").DataTable({
    
                    "processing": true, // for show progress bar
                    "serverSide": true, // for process server side
                    "filter": true, // this is for disable filter (search box)
                    "orderMulti": false, // for disable multiple column at once
                    "pageLength": 5,
    
                    ajax: {
                        url: "/Subjects/LoadData",
                        type: "POST",
                        datatype: "json"
                    },
    
                    columns: [
                        { "data": "CRN", "name": "CRN", "autoWidth": true },  
    
                    ]
    
                });
            });
        </script>

    Loading the data

     public ActionResult LoadData()
            {
                    //Creating instance of DatabaseContext class  
                    using (DatabaseContext _context = new DatabaseContext())
                    {
                        var draw = Request.Form.GetValues("draw").First();
                        var start = Request.Form.GetValues("start").First();
                        var length = Request.Form.GetValues("length").FirstOrDefault();
                        var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
                        var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
                        var searchValue = Request.Form.GetValues("search[value]").FirstOrDefault();
    
    
                        //Paging Size (10,20,50,100)    
                        int pageSize = length != null ? Convert.ToInt32(length) : 0;
                        int skip = start != null ? Convert.ToInt32(start) : 0;
                        int recordsTotal = 0;
    
                        // Getting all subject data    
                        var subjectData = (from tempsubject in _context.Subjects
                                           select tempsubject);
    
                        //Sorting    
                        if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
                        { 
                            subjectData = subjectData.OrderBy(sortColumn + " " + sortColumnDir);
                        }
                        //Search    
                        if (!string.IsNullOrEmpty(searchValue)) { 
                        
                            subjectData = subjectData.Where(m => m.CRN.ToString() == searchValue);
                        }
    
                        //total number of rows count     
                        recordsTotal = subjectData.Count();
                        //Paging     
                        var data = subjectData.Skip(skip).Take(pageSize).ToList();
                        //Returning Json Data    
                        return Json(new { draw, recordsFiltered = recordsTotal, recordsTotal});
                    }

    Any help would be greatly appreciated. Thanks!

    Sunday, May 19, 2019 5:18 AM

All replies

  • User1120430333 posted

    I don't know. Maybe, you should be using an Action method that uses  JsonResult as the return of the method instead of ActionResult. 

    Sunday, May 19, 2019 9:33 AM
  • User753101303 posted

    Hi,

    when attempting to connect to a remote MySQL server

    You tried F12 Network in your browser to see what happens for your Ajax request? Maybe a MySQL connection issue causing a server side exception in LoadData?

    Tuesday, May 21, 2019 4:36 PM
  • User61956409 posted

    Hi JesseJames11,

    JesseJames11

    the data does not get retrieved and the table gets stuck 'Processing'.

    JesseJames11

    //Returning Json Data
                        return Json(new { draw, recordsFiltered = recordsTotal, recordsTotal});

    Based on the code that you provided, I did a test with some testing data, I can reproduce same issue on my side. I checked the F12 developer tool Console tab, the error like below occured.

    And I checked the return data of requesting to endpoint for retrieving data, the data filed does not contains in returned json result.

    To solve the problem, you can modify your action to return json result that jQuery DataTable expect, like below.

     return Json(new { draw, recordsFiltered = recordsTotal, recordsTotal,data= subjectData });

    Test Result:

    With Regards,

    Fei Han

    Friday, May 24, 2019 6:30 AM