javascript - Bootstrap table sorting, filtering, pagination using dataTable.js

I am working on asp 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_mand = new SqlCommand(query, con);

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

con.Open();
SqlDataReader reader_events = event_mand.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

<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_mand = new SqlCommand(query, con);

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

con.Open();
SqlDataReader reader_events = event_mand.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

Share Improve this question edited Sep 19, 2016 at 6:48 Hitesh Misro 3,4511 gold badge24 silver badges54 bronze badges asked Sep 19, 2016 at 6:26 user6789930

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 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 Commented Sep 19, 2016 at 6:33
@parkash: I have downloaded from this link datatables/download also i have changed .dataTable(); to DataTable(); nothing happens – 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 Commented Sep 19, 2016 at 6:36
@all: i am getting this error $(...).DataTable is not a function – user6789930 Commented Sep 19, 2016 at 6:39

Add a ment |

2 Answers 2

Sorted by: Reset to default 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>

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".

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

javascript - Bootstrap table sorting, filtering, pagination using dataTable.js - Stack Overflow

2 Answers 2

与本文相关的文章

评论列表(0)