Datatables Ajax Pagination Not Working

Posted onby
  1. Jquery Datatable Server Side Pagination
  2. Datatable Pagination From Server
  3. Wordpress Pagination Not Working
  4. Pagination Not Working
  1. GitHub is home to over 28 million developers working together to host and review code, manage projects, and build software together. Bootstrap pagination looks.
  2. My Pagination is not working. The Bootstrap works but the records are all dumped into one page. = 5 means that I'm forcing the datatables to only display 5.

DataTables is a powerful jQuery plugin for creating table listings and adding interactions to them. It provides searching, sorting and pagination without any configuration. In this article we’ll go through the basics of DataTable and how to use some of the advanced features.

I am not a JS expert, but I tried to build the frontend myself but it's not working as expected. Currently the first call of the page loads the first set of data, when I scroll down, the next page get loaded (I can see that in Chrome debugger), but the data ist not displayed in UI. I think i am not too far from solving this issue. I am having some problems with pagination in my Ajax code. I can get the search and sort part to work, but for some reason, the pagination is not working. One thing that I noticed is that the start position is not updating. Here is the JavaScript code [code] $('#eventSearchResultsTable').dataTable({'bProcessing': true, 'bServerSide': true.

Setting up DataTables

The first step is to obtain the library from the DataTables website. 1.9.4 is the current stable version and 1.10 is in development now. If you don’t want to download the files, you can also make use of the Microsoft CDN. As it is a jQuery plugin you should include the jQuery library too, preferably the latest version.

Naming conventions

Before we move on, it’s useful to understand the conventions used in the library. DataTables uses Hungarian notation for naming variables, which basically adds certain prefix to its name that helps to understand type of data held by the variable.

n – Variable represents a node.
o – Variable represents an object.
a – Variable represents an array.
s – variable is a string.
b – Boolean
f – Float
i – Variable holds an integer value.
fn – Variable represents a function.

Sometimes you will see multiple prefixes used together, like ao, which represents an array of objects.

Getting started

DataTables can work with data from a verity of sources. It can directly work on an HTML table or we can specify data as an array while initialization. Or it can work on data coming from an Ajax source.

In this article we’ll create a listing of websites in the SitePoint network. We’ll start with the listing of a few site names, and then we’ll add more columns and features. Here we have an HTML table with one column that lists the names of just three sites. Let’s see what DataTables can do with a minimal setup.

Copy-paste this code to an html page and open it up in a browser. You will see a nice looking table as shown below.

Now you know the power of DataTable and what we can accomplish with it. With this most basic setup, we can search for site names from the top search box and sort them by clicking on the column name. How difficult would be if you were trying to implement those features on your own?

Here we’ve first included the DataTables CSS file in the top and Js in the bottom. $('#example').dataTable() will instantiate the DataTable on the table with the id example.

Now we need to list more sites with more details about each site. Let’s say we have a list in our hand, with details like site name, URL, type and last updated but in a JavaScript array instead of an HTML table. Type can be blog, forum or marketplace and the last updated column will have null value for sites that are not blogs.

Here we want to show N/A instead of null values in the last updated column and make URLs a clickable link.

We’ll first create an HTML table, with the required column headings and an empty table body.

Now let’s apply DataTables to this table.

Now our table will be as shown below. We have a clickable link in the URL column of all records and the last modified column looks pretty neat.

We have given the json array using the aaData option of DataTables. If you look at the code, you’ll see a “aoColumnDefs” option passed to the DataTables, which have an array of some objects. aoColumnDefs property basically decides how each column should be rendered in the table. With this property, you can modify any arbitrary column in the table, specified by the aTargets property of the object.

The value in aTargets array can be the class name specified in the column heading, an index of the column (starting from 0 for left to right or negative index for right to left) or “_all” to match all columns of the table. “sTitle“:”Site name” for the first column will override the column heading of the first column.

Note that the sorting icon is missing in the second column, because sorting based on the URLs doesn’t make much sense. I’ve removed it by setting bSortable:false for the corresponding column. mRender is a powerful option that can be used to alter the data displayed. This attribute can be given in a number of ways ways. When used an integer value, it will use that as the index to the data array. When a string is given, it will use the property of the json object with that name.

These are useful especially when we are displaying json data returned from the server. mRender can also be a function, which will be called when rendering each cell of that column. The function will get three parameters, value, type and the entire row and it should return what we want render in its place. Our function just created a link to that URL and returned it.

Likewise for the last updated column, we are checking the value in the third column of each row. If it is a Blog, function will return the formatted date else it will return “N/A”.

The “sType” attribute helps us to specify the expected type of that column, which decides how the values will be sorted for that column. By default, DataTables supports four types: string, numeric, date and HTML. You can extend it by defining custom types. Here we’ve specified the type as “date” and hence it will be using a date based sorting. Otherwise it will consider the column as just string and sorting may not make any sense.

Server side processing

In the above examples we have worked with a plain HTML table and a json array directly given in the client side. Sometimes the database will have too many records and it may not be wise to fetch them all at once and apply DataTables to it. Instead we can use the server-side processing feature provided by the library and fetch the only records that are currently displayed in the front-end.

Each time the table is rendered, DataTables will send a request with number of parameters to the server, including the starting point, display length, search data, sorting column etc. The server can use them in an SQL query to filter the data and send them back to the client. DataTables expect a few attributes in the returned json data to display them correctly in the front-end. They are:

iTotalRecords – Number of total records in the table, before applying the filters.
iTotalDisplayRecords – Number of records returned after applying the filters.
sEcho – An un-altered copy of the sEcho sent from the client.
aaData – Array of data from the server.

And the json response from server may look something like:

In order to render this, we’ll first create an html table with the corresponding column headings.

Here we’ve just told DataTables to load the data from server by setting bServerSide to true. Once it’s enabled, we should also provide a data source, using sAjaxSource property. In the json data returned from the server, by default DataTables will look for the aaData to get the data to be displayed in the table. You can override this by specifying the attribute name in sAjaxDataProp property.

In this case we are getting an array of json objects, so we should map which key should be displayed in which column. We have used aoColumns instead of aoColumnDefs. Both do the same thing, but in a different way. When using aoColumns the length of array should be equal to number of columns in HTML table so that we should provide a mapping for each column.

In the json response editor field is an object with name, email and phone fields. “mData“: “editor.name” tells DataTables to take the name property of the editor object. Note that phone and email are arrays, which will be automatically concatenated using commas, or we can write a custom mRender function to make it look better.

There is more…

Apart from the above-mentioned features, DataTables provides lots of really useful functionalities and we can extend them by using or writing our own plugins. It also provides a simple API to manipulate the table once it’s created.

This code will log value data in a row when we click on it. fnGetData basically accepts a TR/TD node or the index of row and return the value in that row or cell.

Sometimes it will be useful to save the current state of a table when we switch from one page to another page. You can enable state saving by setting “bStateSave” : false, during initialization. By default the state will be saved in a cookie. DataTables allows us to define callback function to override this, so that we can store it in Localstorage or in the server itself. Here is an example that’ll save the state in Localstorage.a

Here fnStateSave will be called to save the state as soon as the table is rendered. Next time before rendering the table, it will call fnStateLoad and load the previously stored state. Note: in version 1.10, DataTables will be using localStorage as the default store to save the state.

Summary

DataTables is a highly flexible and feature rich library to work with tables and listing. The library provides a lot of configuration options and a simple API. It is impossible to discuss all the functionalities in a single article. So we have briefly discussed some of the most useful features and how to use them.

Please try out them and let me know if you have any issues. I’ll try my level best to help.

Take your jQuery skills to the next level with SitePoint’s premium subscription service. You’ll find dozens of books and courses on JavaScript and jQuery — and more added every month!

31 Jan 2018CPOL
In this post, I will be addressing this problem and we will learn that how can implement CRUD using JQuery DataTables in one page without any full page reloads.

Introduction

I frequently come across questions on online forums like StackOverflow in which the questioners are able to create CRUD operations for their entity which is a normal form post, but they struggle implementing the CRUD via ajax so that page does not reloads completely for better User Experience.

In this post, I will be addressing this problem and we will learn that how can implement CRUD using JQuery DataTables in one page without any full page reloads.

Background

I had been writing in last few posts about the usage of JQuery DataTables and it can be leveraged to build a GridView like functionality in asp.net mvc, we saw how we can install it using NuGet Package Manager and implement a simple gird using the plugin which provides us the essential features like Sorting, Searching and Pagination, then we saw how we can implement the paging, filtering and Ordering, then we proceeded to see how advanced search can be added for more better user experience.

If someone wants to have refresher or interested to read the previous posts related, they can be found here:

  • Grid View with AJAX based CRUD Operations in ASP.NET MVC 5

In this post we will learn how we can add create, update and delete operations support in the grid that we already implemented using JQuery DataTables. The end result will look something like:

Database Creation using Script

Pagination

First of all we will run the script for database and tables creation that would be essential for this post. Following is the script for that:

You can find the script in attached source code in file name dbScript.sql which also contains sample data to get started quickly, running it will create the DB and will add some sample data in the tables as well.

Create/Insert Operation

For the insertion part, first of all we need to create a partial view in Views >> Asset by right clicking the Asset folder and navigate to Add >> MVC 5 Partial Page (Razor) like the below screen shot:

Open the container view in which asset rows are being rendered which _AssetsPartial.cshtml located in Views >> Asset directory and add the html for the Add Asset button which will open up a popup for inserting a new asset row in database:

Now, open the Index View of Asset which is Views >> Asset >> index.csthtml and in scripts section add the following jQuery code for the button click event which we just created above, it will call the create get action and will display the partial view in bootstrap modal to user, the code for which is:

We also need to add bootstrap modal with container div in which we will be loading create partial view of Asset, so we will add the following html at the end of Index.cshtml view:

Implementing Add GET Action

Add action method for Asset Creation get request in the Asset controller file located at Controllers >> AssetController.cs which would be called by the above jQuery code, inside the action method we are populating the ViewModel and passing the instance back to PartialView as normally we do in mvc based application:

Add Partial View Creation:

Add a new Partial view in project in Asset Views for Create form, for that right click the Assetfolder under Viewsin the Solution Explorer:

Enter the name of partial view to be _CreatePartial in textbox or whatever name you think would be better in your case.

Implementing Add Partial View

Now we will write the partial view that we created in previous step _CreatePatial, add the following code in that View:

Implementing Add/Create Post Action

Jquery Datatable Server Side Pagination

The important thing to note in the view code is the Ajax.BeginForm helper portion as the class Ajax should be enough to understand that it will post the model back to controller action via Ajax, which means that whole will not refresh i.e. no full postback will happen in terms on asp.net web forms.

The post action is simply mapping ViewModel object to the Model and then saving it in the repository:

We are trying to save the newly entered asset in the database and tracking if it is saved successfully or not and if it saves successfully we are returning a string message success back to client side and in the success call back of Ajax Form we will be checking if operation was successful or not to do UI changes according to that.

Implementing Ajax Callback Function

We can see that in BeginFormhelper method parameters we are specifying JavaScript function to be called when the Ajax Form successfully returns back from server i.e. OnSuccess = <span>'CreateAssetSuccess'</span>

Now lets go to the index.cshtml view of Asset and define the success callback implementation:

What we are doing here is if the operation is not successful we are updating the client side html to notify the user about the failure of creation and if the insert goes successful we are closing the modal popup and refreshing the Grid to display the up to date information.

Edit/Update Operation:

Until now we should be able to run the application and successfully add new assets in the database via Ajax using the bootstrap modal which we created above, now lets move to the update part of the Asset.

We will be adding another column in the datatable columns collection which will contain hyper link that will navigate to Edit View, but as we are using Ajax and bootstrap modal, we will be doing updates as well same way, so no redirect will be involved. Lets get started.

Open the Index.csthml file and add a new column in the columns collection in jQuery datatable initialization, after updating the columns array our code would look like:

We are setting the header of new column to display Actions as title and we would need to disable the searching and sorting for this column, as it is for edit operation and it does not makes sense to enable sorting and searching on this column. Next we are defining the render method of the column and we are generating anchor link html which could call the Edit action of Asset controller and will pass the current asset id to pull the information of it and display in the Edit View.

Defining Edit/Update GET Action

After doing the datatable js changes, now we need to create a get action method which will pull the asset record from the database and will display it for editing to the user in a popup.

Lets implement the Edit get action of it:

The action simply retrieves the row from the database and after converting it to ViewModel passes it to back to partial view to rendered or returned back to the client side for processing, as no post backs would happen it will generate the html and will send the html back in response of ajax call which client side will handle and decide where to put that html.

Handling Action Links Events on Client Side using JQuery

From the column render function you can see that there is class applied on anchor link called EditAsset, it is defined because jQuery event handler will be applied to the anchor link and Ajax call will be sent to server, lets define the event handler for that in Index View:

Addition of Bootstrap Modal

Now add the bootstrap modal html in the Index View which will be placeholder for loading the Edit View, define it just after create bootstrap modal html:

Edit/Update Partial View Creation:

Create another new partial view following the same steps which we did for adding _CreatePartial.cshtml which was for Create Asset, So add a new Partial View in the Asset folder in Viewswith name _EditPartial.cshtml and add the following code in it:

The Edit View is also pretty same as we had for Insert except it would be posting to different action which would be responsible for handling the updates of a particular Asset row.

Implementing Edit/Update Post Action

Now lets implement the post action of Edit:

Handling Update Ajax Success CallBack

In Index.cshtml implement the OnSuccesscallback function which will be called when the asset gets updated successfully, in the call back function modal would be closed and the form will be cleared so that if user opens for editing again the fresh html would be fetched and updated information will be displayed in View and of course datatable will also be refreshed to display the latest updates:

The same approach will be followed for the details and delete action, lets see the delete portion, first of all open the Index view and lets add the details and delete action hyperlinks in the render function where we defined the edit link above:

Now the action column will contain three hyperlinks for Edit, Details and Delete of Asset.

Retrieve and Delete Operation:

At this stage we should be able to see the insert and update functionality working correctly, now we will move to the deletion part to see how we implement the Deletion part for Assets. For doing that, we will need to add class to the hyperlinks which we are generated in the render function for the column in which links will be appearing, lets do that first, we need to define the renderproperty for the last column in columnsarray of the DataTables initialization code, and we will define the how the column value should be rendered:

We have added the detailsAssetand deleteAssetclasses to the respective anchor tags so that we can bind the events using jQuery and do some logic to display details or delete particular asset.

After doing the above step, now we will be writing the events to handle the click of these two hyperlinks. We will have to write the following code to achieve it :

Now as the handler for details tag is placed, lets move to the view part and write the needed razor code in the respective view. So, Create a new partial view named _detailsPartial which will be responsible for displaying the details of the particular asset selected. For that again Right click the Asset folder in Views directory in Solution Explorer and do the same steps as previously and create the partial with the name mentioned above and add the following code in it.

Details Get Action Implementation

Now its the time to define the controller action code which will retrieve the asset information from the data source and will pass it back to view to be displayed to the user. Here is the code for controller action:

Implementing Delete Operation

Datatable Pagination From Server

We will add the GET action method for delete which will get the particular asset from the repository and will display the details in popup, where user would be able to delete it or cancel it. Here is the code for the action method:

Wordpress Pagination Not Working

Delete Partial View Addition

We will now add another partial view in the solution for delete part, so navigate to the Views >> Asset folder in the Solution Explorer and from the context menu which appears by right clicking the Assetfolder, add a new View using the Option Add >> MVC 5 Partial Page (Razor) and name the partial view to _DeletePartial and add the following code to it:

Now we need to again a container div for delete popup as well which will be holding the html returned by the partial view _DeletePartial as we did for other three operations, so add the following html in the Index.cshtml view of Asset:

Handling Delete Link Event with JQuery:

We also need to implement the delete button click event which will be responsible for calling the action method asynchronously and will add the response html to the popup container and the popup container will be displayed to user, the code for which would be:

Handling DELETE Ajax POST Success Callback

If you notice here as well we are using Ajax.BeginForm helper method to post the GUID of Asset row that needs to be deleted, and we have specified a JavaScript success callback, but we havent yet defined the function in the Indexview, so lets do that as well, here is the function definition:

This is also quite same as we did for other actions, we are making sure that if deletion was successful or not, we update the UI accordingly, thats what the above code is doing, it closes the popup, clears the html of the container div and refreshes the datatable ViewModel to reflect the latest changes in the grid.

Implementing Delete POST Action:

Lastly, we need to define the POST action for delete which will be responsible for deleting the row from the database table and will return the status of the action back to view in either case i.e. success or failure, lets do that, here is the code for Deletepost action:

Pagination Not Working

Now run the application and you should be able to add update and delete rows via Ajax without navigating between the pages.