Datatables data filled from MySQL server

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!

Answer 1

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. 

Answer 2

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?

Answer 3

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