CODEWITHSUNDEEP
c#asp.netajaxdatatables
saf21
saf21

Reputation: 844

jQuery Datatable: pagination and filter not display correctly

I dont know how to solve this, try a whole day but didn't success to fix the pagination. I'm using jQuery datatable, and to display my huge data, I'm using server side.

As a testing, only call 10 row of data to the table. Then before pass to table, I restructured the data inside dataSrc, using this solution . The table display successfully but the pagination and filter not display correctly.

Can anyone help this.

Below is my code.

AJAX

$('#example').DataTable({
  "processing": true,
  "serverSide": true,
  "ajax": {
     type: "POST",
     contentType: "application/json; charset=utf-8",
     url: "datatables.aspx/GetData",
     'data': function (data) {
        return JSON.stringify(data);
     },
     "dataSrc": function (data) {
        var json = $.parseJSON(data.d);

        var myData = {};
        myData.draw = parseInt(json.otherData[0].draw);
        myData.recordsTotal = parseInt(json.otherData[0].recordsTotal);
        myData.recordsFiltered = parseInt(json.otherData[0].recordsFiltered);
        myData.data = json.searchData;

        return myData.data;
     }
   },
   "columns": [
      { "data": "Username" }
   ]
  }
});

C#

[WebMethod]
[ScriptMethod(UseHttpGet = false)]
public static string GetData(int draw, object columns, object order, int start, int length, object search)
{
    string constr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        string mySql = "SELECT TOP 10 username AS Username FROM user_lookup";
        using (SqlCommand cmd = new SqlCommand(mySql, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                con.Open();
                DataSet ds = new DataSet();
                sda.Fill(ds, "searchData");

                DataTable newDT = new DataTable("otherData");

                //Add columns to DataTable.
                newDT.Columns.AddRange(new DataColumn[4] {
                    new DataColumn("draw"),
                    new DataColumn("recordsTotal"),
                    new DataColumn("recordsFiltered"),
                    new DataColumn("userRole")
                });

                //Add rows to DataTable.
                newDT.Rows.Add(draw, length, start, "myrole");
                ds.Tables.Add(newDT);

                string JSONString = string.Empty;
                JSONString = JsonConvert.SerializeObject(ds);
                return JSONString;
            }
        }
    }
}

This is the data I return back to datatable to structured the table. enter image description here

Data filter and pagination not correct, should be only 1 page of pagination. enter image description here

Upvotes: 3

Views: 3380

Answers (2)

Yusril Maulidan Raji
Yusril Maulidan Raji

Reputation: 1932

I have a solution for our case. You should modify the object of your response directly.

"dataSrc": function (data) {
        var json = $.parseJSON(data.d);

        data.draw = parseInt(json.otherData[0].draw);
        data.recordsTotal = parseInt(json.otherData[0].recordsTotal);
        data.recordsFiltered = parseInt(json.otherData[0].recordsFiltered);
        data.data = json.searchData;

        return data.data;
     }

Hopefully, it works in your case.

Upvotes: 5

Mairaj Ahmad
Mairaj Ahmad

Reputation: 14604

Your response doesn't contain property otherData and searchData and you are accessing data by these names.

Your response is a simple object which has following properties.

  • data
  • recordsTotal
  • recordsFiltered
  • draw

So you need to access them directly from your response like this

var myData = {};
myData.draw = parseInt(json.draw);
myData.recordsTotal = parseInt(json.recordsTotal);
myData.recordsFiltered = parseInt(json.recordsFiltered);
myData.data = json.data;

Also recordsTotal is should be total number of records right now you are returning 10 and records shown are also 10.

You are getting NaN because there is no property json.otherData[0].recordsFiltered so when you will change it to json.recordsFiltered it will show numbers.

Upvotes: 0

Related Questions