Bootstrap table sorting, filtering, pagination using dataTable.js

Question

I am working on asp.net MVC-5 and created a Bootstrap table to show data Now i want to apply pagination, filtering and sorting, for this i searched may articles and found this link, the technique is very simple in this link and it's same to what i am doing in my project. Bellow i have included my .js and .css files

<link href="~/Content/bootstrap.css" rel="stylesheet" />
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<script src="~/Scripts/jquery-3.1.0.js"></script>
<link href="~/Content/jquery.dataTables.min.css" rel="stylesheet" />
<script type="text/javascript" src="~/Scripts/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="~/Scripts/bootstrap.min.js"></script>
<script src="~/Scripts/bootstrap-toggle.js"></script>
<link href="~/Content/bootstrap-toggle.css" rel="stylesheet" />

<link rel="shortcut icon" href="favicon.ico" type="image/x-icon" />
<link rel="icon" href="favicon.ico" type="image/ico" />
<link rel="shortcut icon" href="~/favicon.ico" />

Bellow is my razor syntax for table

<table id="myTable" class="table table-bordered table-responsive table-hover">
    <tr>
        <th style="color:#006bff; font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif" >
            Name
        </th>
        <th style="color:#006bff; font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif">
            Ocurrence Time
        </th>
        <th style="color:#006bff; font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif">
            Recover Time
        </th>
    </tr>
    <tbody>
        @{
        if (ViewData["events"] != null)
        {
        if (ViewData.Values != null && ViewData.Values.Count() > 0)
        {
        foreach (System.Data.DataRow dr in (ViewData["events"] as System.Data.DataTable).Rows)
        {
        <tr>
            <td style="border:1px solid black; color:green;font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif">
                <span style="font-size:12px;">@dr[0]</span>
            </td>
            <td style="border:1px solid black; color:green;font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif">
                <span style="font-size:12px;">@(string.Format("{0:dd MMMM yyyy hh:mm tt}", dr[1]))</span>
            </td>
            <td style="border:1px solid black; color:green;font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif">
                <span style="font-size:12px;"> @(string.Format("{0:dd MMMM yyyy hh:mm tt}", dr[2]))</span>
            </td>
        </tr>
        }
        }
        }
        }
    </tbody>
</table>

At the end i have placed my script

<script type="text/javascript">
$(document).ready(function () {
    $('#myTable').dataTable();
});

Bellow is the controller code

//Getting Events from Database
//string query = "SELECT Distinct DE.Occurrence_Time,DE.Recovery_Time FROM Device_Events DE INNER JOIN ADS_Device_Data AD ON AD.Device_ID=DE.Device_ID WHERE 1=1 ";
string query = "SELECT Distinct  e.Event_Name, de.Occurrence_Time, de.Recovery_Time from Device_Events DE inner join Events e on de.Event_ID = e.Event_ID inner join ADS_Device_Data ad on de.Device_ID = ad.Device_ID where 1=1 ";

if (search != "") {
    query += " AND ad.Device_Serial_Number= '" + search + "'";
}

if (time.ToString() != " ") {
    query += " AND de.Occurrence_Time >= '" + time.ToString() + "'";
}


SqlCommand event_command = new SqlCommand(query, con);

//SqlCommand event_command = new SqlCommand("Select Device_Event_ID,Device_ID,Event_ID,Occurrence_Time,Recovery_Time from [Device_Events] where Device_ID=@device_id", con);
//event_command.Parameters.AddWithValue("@device_id", device_id);

con.Open();
SqlDataReader reader_events = event_command.ExecuteReader();

while (reader_events.Read()) {
    //events.Add(Convert.ToString(reader_events["Event_Name"]));
    //events.Add(Convert.ToString(reader_events["Occurrence_Time"]));
    //events.Add(Convert.ToString(reader_events["Recovery_Time"]));
    events.Rows.Add(Convert.ToString(reader_events["Event_Name"]),
        Convert.ToString(reader_events["Occurrence_Time"]),
        Convert.ToString(reader_events["Recovery_Time"]));



    //events.Add(string.Concat(Convert.ToString(reader_events["Event_Name"]) + " - " +" Occur " + Convert.ToString(reader_events["Occurrence_Time"]) + " - " + " Recover " + Convert.ToString(reader_events["Recovery_Time"]).Replace("\n", Environment.NewLine)));

    //events.Add(string.Concat(" Power Failure " + " Event ID # " + Convert.ToString(reader_events["Event_ID"]) + " Device ID # " + Convert.ToString(reader_events["Device_ID"]) + " Occur at " + Convert.ToString(reader_events["Occurrence_Time"]) + " Recover at " + Convert.ToString(reader_events["Recovery_Time"]).Replace("\n", Environment.NewLine)));
    //events.Add(string.Concat(" Power Failure " + " Event ID # " + Convert.ToString(reader_events["Event_ID"]) + ", Device ID # " + Convert.ToString(reader_events["Device_ID"]) + ", Occured " + Convert.ToString(reader_events["Occurrence_Time"]) + ", Recover " + Convert.ToString(reader_events["Recovery_Time"]).ToList().ToPagedList(page ?? 1, 5)));
}
con.Close();

After that i have placed the events in viewdata ViewData["events"] = events;

After doing all this i got bellow result

No paging, sorting and filtering is enabled

I must be missing something

Any help will be highly appreciated

What is version of your Datatable plugin ? Also if you are using version > 1.10 then use $('#myTable').DataTable(); instead of $('#myTable').dataTable(); — Prakash Thete
– Prakash Thete, Commented Sep 19, 2016 at 6:30
Also, Try enabling the sorting for few columns explicitly and see if it works, Try something like, $('#myTable').DataTable({order: [[ 3, 'desc' ], [ 0, 'asc' ]]} ); — David R
– David R, Commented Sep 19, 2016 at 6:33
@parkash: I have downloaded from this link datatables.net/download also i have changed .dataTable(); to DataTable(); nothing happens — user6789930
– user6789930, Commented Sep 19, 2016 at 6:35
@faisal, Please add the headers as stated by @Mannam in table definition. If it does not solve the issue please create the JSFiddle of your code. — Prakash Thete
– Prakash Thete, Commented Sep 19, 2016 at 6:36
@all: i am getting this error $(...).DataTable is not a function — user6789930
– user6789930, Commented Sep 19, 2016 at 6:39

Marcello B. · Accepted Answer · 2019-08-21 23:48:14Z

2

use <thead> tag in table

<thead>
    <tr>
        <th style="color:#006bff; font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif" >
            Name
        </th>
        <th style="color:#006bff; font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif">
            Ocurrence Time
        </th>
        <th style="color:#006bff; font-family:'Segoe UI', Tahoma, Geneva, Verdana, sans-serif">
            Recover Time
        </th>
    </tr>
</thead>

edited Aug 21, 2019 at 23:48

Marcello B.

4,47011 gold badges48 silver badges72 bronze badges

answered Sep 19, 2016 at 6:32

Mannan Bahelim

1,3651 gold badge12 silver badges34 bronze badges

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

7 Comments

user6789930 Over a year ago

i am getting this error $(...).DataTable is not a function

Mannan Bahelim Over a year ago

did you put the thead tag ?

Divyang Desai Over a year ago

@faisal Please check, this issue also occurred when referenced jQuery twice

user6789930 Over a year ago

@Div: jQuery is added on time only

Mannan Bahelim Over a year ago

@faisal create JSFiddle

|

Michael Romrell · Accepted Answer · 2020-04-07 07:47:16Z

1

I know this is an old thread but the solution that worked for me was not listed here, and this still appears at the top of the results in Google. Try adding defer to your datatable.js import

<script defer type="text/javascript" src="~/Scripts/jquery.dataTables.min.js"></script>

if you open developer tools (in chrome) and click on "Network" you can see which order your files are loading. And even though Jquery.js by appear before DataTables.js in your html file, Jquery may not have finished loading by the time DataTables does. adding defer ensures that the datatables.js file loads last.

Additionally if you want to know if you are somehow loading two instances of jquery (another possible cause of this problem) you can see that in Developer tools under "Network".

answered Apr 7, 2020 at 7:47

Michael Romrell

1,0155 gold badges16 silver badges32 bronze badges

1 Comment

KriiV Over a year ago

Was having this issue for quite sometime. Was importing jquery before datatables js but wasn't working. Adding defer fixed it! Thanks

Collectives™ on Stack Overflow

Bootstrap table sorting, filtering, pagination using dataTable.js

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related