1

I'm trying to load values from my database to a DataTable object, to an array. I'm then trying to pass that array to jQuery DataTables plug in, however, I can't get jQuery to hit my method using $.getJSON.

I just tried this for another plugin and it worked, except not an array.

    public ActionResult GetAssociateFromDb()
    { // HomeController.cs
        DataTable dt = new DataTable();
        string jsonData;
        string connString = ConfigurationManager.ConnectionStrings["DEFCOMP"].ConnectionString;
        using (SqlConnection connection = new SqlConnection())
        {
            connection.ConnectionString = connString;
            using (var cmd = new SqlCommand("SELECT * FROM FUND", connection))
            {
                connection.Open();
                var myAdapter = new SqlDataAdapter(cmd);
                myAdapter.Fill(dt);
                var arr = new ArrayList();
                foreach ( DataRow dr in dt.Rows)
                {
                    arr.Add(dr);
                }
                return Json(arr, JsonRequestBehavior.AllowGet);
               //Debugging, arr has Count = 20.
            }
        }
    }

This is the code in my view.

    $(document).ready(function() {

        $.getJSON('@Url.Action(actionName: "GetAssociateFromDb", controllerName: "Home")', function (data) {

            alert(JSON.stringify(data)); // I never get an alert
        //    $('#example').dataTable();


        });
});
2
  • Please share (via Chrome Inspector or similar), the JSON being passed from the ActionResult. In my experience with datatables it's almost always that the data is not in the right JSON format. It's very fussy. Commented May 29, 2014 at 19:34
  • Can you explain where I find the JSON being passed in the Chrome Inspector? I guess it doesn't help I'm getting Failed to load resource 404 and 500 errors. Commented May 29, 2014 at 19:40

1 Answer 1

1

Firstly, get the JSON being returned by the ActionResult. You do this by going to the Network Tab, finding the AJAX request and looking at the packet. It should show you via preview precisely what is being returned.

Secondly, I'll pretty much guarantee that it won't be in the right format. Here is an example of one way that gives Datatables what it needs:

https://datatables.net/examples/data_sources/ajax.html

^^ Take a look here, specifically at the Ajax tab halfway down the page

{
  "data": [
    [
      "Tiger Nixon",
      "System Architect",
      "Edinburgh",
      "5421",
      "2011/04/25",
      "$320,800"
    ],
    [
      "Garrett Winters",
      "Accountant",
      "Tokyo",
      "8422",
      "2011/07/25",
      "$170,750"
    ]
]}

Basic, but you get the picture. What I have done in the past is to create a Model or Interface in the project that represents the structure that datatables.net is looking for, so that I can render data to datatables consistently and easily.

Good luck with your app!

Sign up to request clarification or add additional context in comments.

3 Comments

Awesome. Thanks for your answer! I figured it out just before you posted your answer, although there seems to be some CSS issues. I'll still accept and keep what you said in mind! Without getting in too deep of a conversation, have you ever had problems with DataTables CSS?
Great. Yes, a variety of things, but I'd have to have a look at what you have. If you want to create a new question, I'll endeavour to keep an eye out for it.
Sure thing! I'll give a little background, also, so you can see how I did it.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.