Filter dynamic HTML Table on DropDownList change using jQuery AJAX in ASP.Net

Last Reply 3 months ago By pandeyism

Posted 3 months ago

how to filter html table on clientside using dropdownlist change event in jquery ajax json 

You are viewing reply posted by: pandeyism 3 months ago.
Posted 3 months ago Modified on 3 months ago

Hi jovceka,

Refer below sample.

Database

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

Download Northwind Database

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            var selectedText = '';
            MakeAjaxCall(selectedText);

            $("#ddlCountry").change(function () {
                var selectedText = $(this).find("option:selected").text();
                MakeAjaxCall(selectedText);
            });
        });

        function MakeAjaxCall(selectedText) {
            $.ajax({
                type: "POST",
                url: "CS.aspx/GetData",
                data: '{search:"' + selectedText + '"}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    var table = $('#tblCustomers');
                    $(table).find("tr:gt(0)").remove();
                    var rows = "";
                    for (var i = 0; i < response.d.length; i++) {
                        var CustomerID = response.d[i].CustomerID;
                        var CompanyName = response.d[i].CompanyName;
                        var City = response.d[i].City;
                        var Country = response.d[i].Country;
                        rows += "<tr><td>" + CustomerID +
                                "</td><td>" + CompanyName +
                                "</td><td>" + City +
                                "</td><td>" + Country +
                                "</td></tr>";
                    }
                    table.append(rows);
                }, failure: function (response) {
                    alert(response.responseText);
                }, error: function (response) {
                    alert(response.responseText);
                }
            });
        }        
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <asp:DropDownList runat="server" ID="ddlCountry">
        <asp:ListItem Value="Select" Text="Select"></asp:ListItem>
    </asp:DropDownList>
    <br />
    <table id="tblCustomers" class="tblCustomers" cellpadding="2" cellspacing="0" border="1">
        <tr>
            <th>
                <b>CustomerId </b>
            </th>
            <th>
                <b>CompanyName </b>
            </th>
            <th>
                <b>City </b>
            </th>
            <th>
                <b>Country </b>
            </th>
        </tr>
    </table>
    </form>
</body>
</html>

Namespaces

C#

using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

VB.Net

Imports System.Data.SqlClient
Imports System.Web.Services
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT CustomerId,Country FROM Customers", con))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    ddlCountry.DataSource = dt;
                    ddlCountry.DataTextField = "Country";
                    ddlCountry.DataValueField = "CustomerId";
                    ddlCountry.DataBind();
                }
            }
        }
    }
}

[WebMethod]
public static List<Customer> GetData(string search)
{
    List<Customer> customers = new List<Customer>();
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT TOP 5 * FROM Customers WHERE Country = @Country OR @Country IS NULL";
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        if (!string.IsNullOrEmpty(search))
        {
            cmd.Parameters.AddWithValue("@Country", search);
        }
        else
        {
            cmd.Parameters.AddWithValue("@Country", (object)DBNull.Value);
        }
        cmd.Connection = con;
        con.Open();
        SqlDataReader sdr = cmd.ExecuteReader();
        while (sdr.Read())
        {
            customers.Add(new Customer
            {
                CustomerID = sdr["CustomerId"].ToString(),
                CompanyName = sdr["CompanyName"].ToString(),
                City = sdr["City"].ToString(),
                Country = sdr["Country"].ToString(),
            });
        }
        con.Close();
    }
    return customers;
}

public class Customer
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId,Country FROM Customers", con)
                Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable()
                    da.Fill(dt)
                    ddlCountry.DataSource = dt
                    ddlCountry.DataTextField = "Country"
                    ddlCountry.DataValueField = "CustomerId"
                    ddlCountry.DataBind()
                End Using
            End Using
        End Using
    End If
End Sub

<WebMethod()>
Public Shared Function GetData(ByVal search As String) As List(Of Customer)
    Dim customers As List(Of Customer) = New List(Of Customer)()
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT TOP 5 * FROM Customers WHERE Country = @Country OR @Country IS NULL"
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        If Not String.IsNullOrEmpty(search) Then
            cmd.Parameters.AddWithValue("@Country", search)
        Else
            cmd.Parameters.AddWithValue("@Country", CObj(DBNull.Value))
        End If
        cmd.Connection = con
        con.Open()
        Dim sdr As SqlDataReader = cmd.ExecuteReader()
        While sdr.Read()
            customers.Add(New Customer With {
                .CustomerID = sdr("CustomerId").ToString(),
                .CompanyName = sdr("CompanyName").ToString(),
                .City = sdr("City").ToString(),
                .Country = sdr("Country").ToString()
            })
        End While
        con.Close()
    End Using
    Return customers
End Function

Public Class Customer
    Public Property CustomerID As String
    Public Property CompanyName As String
    Public Property City As String
    Public Property Country As String
End Class

Screenshot