Search and Filter dynamic HTML Table on TextBox KeyPress using jQuery AJAX, C# and VB.Net in ASP.Net

Last Reply 3 months ago By dharmendr

Posted 3 months ago

 hi..i filed my grid table #example with data from database..now i want to search for row/rows matching productname/productdetails values in #example table and fill the same table with matching row/rows without checking in database..i.e. clientside searching..also, where is the best place to display the search result, in the same page or new page..reply plzz..thanks in advance 

<input type="text" name="txtsearch">
script type="text/javascript">
    $(function () {
        GetProduct();
    });
    function GetProduct() {
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "WebService.asmx/Products",
            data: {},
            dataType: "json",
            success: function (data) {
                var table = $('#example');
                var rows = "";
                for (var i = 0; i < data.d.length; i++) {
                    var name = data.d[i].ProductName;
                    var details = data.d[i].Proddetails;
                    
                    rows += "<tr><td>" + name + "</td><td>" + details + "</td>";
                    
                }
                table.append(rows);
            },
            error: function (response) {
                alert("Error while Showing update data");
            }
        });
    }
</script>

 

Posted 3 months ago

Hi jovceka,

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

<input type="text" name="txtSearch" />
<br />
<table id="example">
    <tr>
        <th>Id</th>
        <th>Name</th>
        <th>Details</th>
    </tr>
</table>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
        GetCategory("");
        $('input[name=txtSearch]').on('keyup', function () {
            var searchText = $(this).val();
            GetCategory(searchText);
        });
    });
    function GetCategory(text) {
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "WebService.asmx/GetCategories",
            data: "{searchText: '" + text + "'}",
            dataType: "json",
            success: function (data) {
                var table = $('#example');
                $(table).find("tr:gt(0)").remove();
                var rows = "";
                for (var i = 0; i < data.d.length; i++) {
                    var id = data.d[i].Id;
                    var name = data.d[i].Name;
                    var details = data.d[i].Description;
                    rows += "<tr><td>" + id + "</td><td>" + name + "</td><td>" + details + "</td></tr>";
                }
                table.append(rows);
            },
            error: function (response) {
                alert("Error while Showing update data");
            }
        });
    }
</script>

Web Service

C#

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

/// <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 List<Category> GetCategories(string searchText)
    {
        List<Category> categories = new List<Category>();
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = "SELECT CategoryID,CategoryName,Description FROM Categories WHERE (CategoryName LIKE @Name + '%' OR Description LIKE @Name + '%') OR @Name IS NULL";
        using (SqlConnection con = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand(query, con);
            if (!string.IsNullOrEmpty(searchText))
            {
                cmd.Parameters.AddWithValue("@Name", searchText);
            }
            else
            {
                cmd.Parameters.AddWithValue("@Name", (object)DBNull.Value);
            }
            con.Open();
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
                categories.Add(new Category
                {
                    Id = Convert.ToInt32(sdr["CategoryID"]),
                    Name = sdr["CategoryName"].ToString(),
                    Description = sdr["Description"].ToString()
                });
            }
            con.Close();
        }

        return categories;
    }

    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    }
}

VB.Net

Imports System
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Services

' 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 Function GetCategories(ByVal searchText As String) As List(Of Category)
        Dim categories As List(Of Category) = New List(Of Category)()
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "SELECT CategoryID,CategoryName,Description FROM Categories WHERE (CategoryName LIKE @Name + '%' OR Description LIKE @Name + '%') OR @Name IS NULL"
        Using con As SqlConnection = New SqlConnection(conString)
            Dim cmd As SqlCommand = New SqlCommand(query, con)
            If Not String.IsNullOrEmpty(searchText) Then
                cmd.Parameters.AddWithValue("@Name", searchText)
            Else
                cmd.Parameters.AddWithValue("@Name", CObj(DBNull.Value))
            End If
            con.Open()
            Dim sdr As SqlDataReader = cmd.ExecuteReader()
            While sdr.Read()
                categories.Add(New Category With {
                    .Id = Convert.ToInt32(sdr("CategoryID")),
                    .Name = sdr("CategoryName").ToString(),
                    .Description = sdr("Description").ToString()
                })
            End While
            con.Close()
        End Using

        Return categories
    End Function

    Public Class Category
        Public Property Id As Integer
        Public Property Name As String
        Public Property Description As String
    End Class

End Class

Screenshot