Search multi jQuery DataTables using WebService in ASP.Net

Last Reply 8 months ago By dharmendr

Posted 8 months ago

Hi,

i am stuck with two darables on my page and still have deadline for the project. On the first datatable search and select work perfectly, but on the second do not.  Please help. 

here is my code

<div style="border:1px solid black; padding:3px; width:1200px">
    <input id="CustomerJSON" type="hidden" runat="server"  />
    <input id="Hidden1" type="hidden" runat="server"  />
    <asp:Button ID="Button1" runat="server" Text="Save me" OnClientClick = "GetTableValues()" OnClick ="Submit" Visible="False"/>
    <table id="datatableNew" >                 
                  <thead >
                      <tr >
                          <th >TransID</th>
                          <th >Assignor</th>
                          <th >AssignorUP</th>
                          <th >Assignee</th>
                          <th >AssigneeUP</th>
                          <th >ExecutionDate</th>
                      </tr>
                  </thead>
                  <tfoot >
                         <tr >
                          <th >TransID</th>
                          <th >Assignor</th>
                          <th >AssignorUP</th>
                          <th >Assignee</th>
                          <th >AssigneeUP</th>
                          <th >ExecutionDate</th>
                      </tr>
                  </tfoot>
              </table>
              <br />
              <table id="datatable"  >                 
                  <thead>
                      <tr>
                          <th>TransID</th>
                          <th>Assignor</th>
                          <th>AssignorUP</th>
                          <th>Assignee</th>
                          <th>AssigneeUP</th>
                          <th>ExecutionDate</th>
                      </tr>
                  </thead>
                  <tfoot>
                         <tr>
                          <th>TransID</th>
                          <th>Assignor</th>
                          <th>AssignorUP</th>
                          <th>Assignee</th>
                          <th>AssigneeUP</th>
                          <th>ExecutionDate</th>
                      </tr>
                  </tfoot>
    </table>
</div>

 

<script type ="text/javascript">
     $(document).ready(function () {
         $.ajax({
             url: "BSIPServisi.asmx/GetSavedPastTransactionJava",
             type: 'post',
             dataType: 'json',
             success: function (data) {
                 var dataTableInstance = $('#datatable').DataTable({
    
                     'paging': true,
                     'sort': true,
                     'searching': true,
                     'fixedHeader':true,
                      //colReorder:true,
                      //  'scrollY': 400,
                     data: data,
                     columns: [
      
                         { 'data': 'TransID' },
                         { 'data': 'Assignor' },
                         { 'data': 'AssignorUP' },
                         { 'data': 'Assignee' },
                         { 'data': 'AssigneeUP' },
                         {
                             'data': 'ExecutionDate',
                             'render': function (jsonDate) {
                                 var date = new Date(parseInt(jsonDate.substr(6)));
                                 var month = date.getMonth() + 1;//pocinje od 0 u javi
                                 return month + "/"  + date.getDate() + "/" + date.getFullYear();
                             }
                         }
                     ],
                      select: {
                            style:    'os',
                            selector: 'td:first-child'
                        },
                  order: [[ 1, 'asc' ]]    
                 });

                  $('#datatable tfoot th').each(function () {
                     var title = $('#datatable thead th').eq($(this).index()).text();
                     $(this).html('<input type="text" placeholder ="Search ' + title + '"/>');
                 });

                 dataTableInstance.columns().every(function () {
                     var datatableColumn = this;

                     $(this.footer()).find('input').on('keyup change', function () {
                         datatableColumn.search(this.value).draw();
                     });
                 });

                 $('#datatable tbody').on( 'click', 'tr', function () {
                     $(this).toggleClass('selected');                 
                 });
             }
         });
     });
</script>

  <script type ="text/javascript">
     $(document).ready(function () {
       $.ajax({
             url: "BSIPServisi.asmx/GetSavedNewTransactionJava",
             type: 'post',
             dataType: 'json',
             success: function (data) {
                 var dataTableInstanceNew = $('#datatableNew').DataTable({
                     'paging': true,
                     'sort': true,
                     'searching': true,
                     'scrollY': 400,
                      data: data,
                      columns: [
                         { 'data': 'TransID' },
                         { 'data': 'Assignor' },
                         { 'data': 'AssignorUP' },
                         { 'data': 'Assignee' },
                         { 'data': 'AssigneeUP' },
                         {
                             'data': 'ExecutionDate',
                             'render': function (jsonDate) {
                                 var date = new Date(parseInt(jsonDate.substr(6)));
                                 var month = date.getMonth() + 1;//pocinje od 0 u javi
                                 return month + "/"  + date.getDate() + "/" + date.getFullYear();
                             }
                         }
                     ],
                 });

                  $('#datatableNew tfoot th').each(function () {
                     var title = $('#datatableNew thead th').eq($(this).index()).text();
                     $(this).html('<input id="column_search" type="text" placeholder ="Search ' + title + '"/>');
                 });        

                 $('#datatableNew').columns().every(function () {
                     var datatableColumnNew = this;           
                      $("#column_search input").on('keyup click', function() {
                        dataTableInstanceNew.draw();
                    });
   
                    $("#column_search").on('keyup click', function() {
                        dataTableInstanceNew.column().search($(this).val()).draw();
                    });
                 })   
        
                 $('#datatableNew tbody').on('click', 'tr', function () {
                       $(this).toggleClass('selected');
                     var table = $('#datatableNew').DataTable();
                     if ( $(this).hasClass('selected') ) {
                            $(this).removeClass('selected');
                        }
                        else {
                            table.$('tr.selected').removeClass('selected');
                         
                            $(this).addClass('selected');
                        }                 
                 });
             }
         });
     });   
 </script>

 

Posted 8 months ago Modified on 8 months ago

Hi Eni,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<div style="border: 1px solid black; padding: 3px; width: 1200px">
    <input id="CustomerJSON" type="hidden" runat="server" />
    <input id="Hidden1" type="hidden" runat="server" />
    <asp:Button ID="Button1" runat="server" Text="Save me" OnClientClick="GetTableValues()"
        OnClick="Submit" />
    <table id="datatableNew">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>City</th>
                <th>Country</th>
                <th>Date Of Birth</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>City</th>
                <th>Country</th>
                <th>Date Of Birth</th>
            </tr>
        </tfoot>
    </table>
    <hr />
    <table id="datatable">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>City</th>
                <th>Country</th>
                <th>Date Of Birth</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>City</th>
                <th>Country</th>
                <th>Date Of Birth</th>
            </tr>
        </tfoot>
    </table>
</div>

JavaScript

<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<script type="text/javascript" src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        /*----------------1st Table.----------------*/
        $.ajax({
            url: "WebService.asmx/GetEmployees",
            type: 'post',
            dataType: 'json',
            success: function (data) {
                var dataTableInstanceNew = $('#datatableNew').DataTable({
                    'paging': true,
                    'pageLength': 5,
                    'sort': true,
                    'searching': true,
                    //'scrollY': 400,
                    data: data,
                    columns: [
                        { 'data': 'Id' },
                        { 'data': 'Name' },
                        { 'data': 'City' },
                        { 'data': 'Country' },
                        {
                            'data': 'BirthDate',
                            'render': function (jsonDate) {
                                var date = new Date(parseInt(jsonDate.substr(6)));
                                var month = date.getMonth() + 1;
                                return month + "/" + date.getDate() + "/" + date.getFullYear();
                            }
                        }]
                });

                $('#datatableNew tfoot th').each(function () {
                    var title = $('#datatableNew thead th').eq($(this).index()).text();
                    $(this).html('<input id="column_search" type="text" placeholder ="Search ' + title.trim() + '"/>');
                });

                dataTableInstanceNew.columns().every(function () {
                    var datatableColumnNew = this;
                    $(this.footer()).find('input').on('keyup click', function () {
                        datatableColumnNew.search($(this).val()).draw();
                    });
                })

                $('#datatableNew tbody').on('click', 'tr', function () {
                    $(this).toggleClass('selected');
                    var table = $('#datatableNew').DataTable();
                    if ($(this).hasClass('selected')) {
                        $(this).removeClass('selected');
                    } else {
                        table.$('tr.selected').removeClass('selected');
                        $(this).addClass('selected');
                    }
                });
            }
        });


        /*----------------2nd Table.----------------*/
        $.ajax({
            url: "WebService.asmx/GetEmployees",
            type: 'post',
            dataType: 'json',
            success: function (data) {
                var dataTableInstance = $('#datatable').DataTable({
                    'paging': true,
                    'pageLength': 5,
                    'sort': true,
                    'searching': true,
                    'fixedHeader': true,
                    //colReorder:true,
                    //  'scrollY': 400,
                    data: data,
                    columns: [
                                    { 'data': 'Id' },
                                    { 'data': 'Name' },
                                    { 'data': 'City' },
                                    { 'data': 'Country' },
                                    {
                                        'data': 'BirthDate',
                                        'render': function (jsonDate) {
                                            var date = new Date(parseInt(jsonDate.substr(6)));
                                            var month = date.getMonth() + 1;
                                            return month + "/" + date.getDate() + "/" + date.getFullYear();
                                        }
                                    }
                                ], select: {
                                    style: 'os',
                                    selector: 'td:first-child'
                                }, order: [[1, 'asc']]
                });

                $('#datatable tfoot th').each(function () {
                    var title = $('#datatable thead th').eq($(this).index()).text();
                    $(this).html('<input type="text" placeholder ="Search ' + title.trim() + '"/>');
                });

                dataTableInstance.columns().every(function () {
                    var datatableColumn = this;
                    $(this.footer()).find('input').on('keyup change', function () {
                        datatableColumn.search(this.value).draw();
                    });
                });

                $('#datatable tbody').on('click', 'tr', function () {
                    $(this).toggleClass('selected');
                });
            }
        });
    });
</script>

WebService

CS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Script.Serialization;

/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    [WebMethod()]
    public void GetEmployees()
    {
        List<Employee> customers = new List<Employee>();
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                con.Open();
                cmd.CommandText = "SELECT EmployeeID,FirstName+' '+LastName Name,BirthDate,City,Country FROM Employees";
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee customer = new Employee()
                    {
                        Id = Convert.ToInt32(rdr["EmployeeID"]),
                        Name = rdr["Name"].ToString(),
                        BirthDate = Convert.ToDateTime(rdr["BirthDate"]),
                        City = rdr["City"].ToString(),
                        Country = rdr["Country"].ToString()
                    };
                    customers.Add(customer);
                }
                con.Close();
            }
        }

        JavaScriptSerializer js = new JavaScriptSerializer();
        Context.Response.Write(js.Serialize(customers));
    }

    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime BirthDate { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
    }
}

VB.Net

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.Services
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService
    Inherits System.Web.Services.WebService

    <WebMethod()>
    Public Sub GetEmployees()
        Dim customers As List(Of Employee) = New List(Of Employee)()
        Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
            Using cmd As SqlCommand = New SqlCommand()
                cmd.Connection = con
                con.Open()
                cmd.CommandText = "SELECT EmployeeID,FirstName+' '+LastName Name,BirthDate,City,Country FROM Employees"
                Dim rdr As SqlDataReader = cmd.ExecuteReader()
                While rdr.Read()
                    Dim customer As Employee = New Employee() With {
                        .Id = Convert.ToInt32(rdr("EmployeeID")),
                        .Name = rdr("Name").ToString(),
                        .BirthDate = Convert.ToDateTime(rdr("BirthDate")),
                        .City = rdr("City").ToString(),
                        .Country = rdr("Country").ToString()
                    }
                    customers.Add(customer)
                End While

                con.Close()
            End Using
        End Using

        Dim js As JavaScriptSerializer = New JavaScriptSerializer()
        Context.Response.Write(js.Serialize(customers))
    End Sub

    Public Class Employee
        Public Property Id As Integer
        Public Property Name As String
        Public Property BirthDate As DateTime
        Public Property City As String
        Public Property Country As String
    End Class
End Class

Screenshot