Search GridView with paging using jQuery DataTable Search TextBox in ASP.Net using Ajax in C# and VB.Net

lingers
 
on Sep 01, 2021 08:58 AM
Sample_150079.zip
765 Views

I have a GridView, the search textbox only searches the first page.

If the item is not present in the first page it will display no data. That is if i have a product S121 in row 121 and my paging is 100 per page, so 121 will be in the second page,

Whenever i use the search not data pops up except i click the 2 page and use the search.

What i want is for the search to search the entire paging not the current page.

Please help

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src=" https://code.jquery.com/jquery-3.5.1.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>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
<script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetCustomers(1);
        var checked = [];
        $('[id*=chkSelect]').on('click', function () {
            if ($(this).is(":checked")) {
                checked.push($(this).closest('tr').find('td').eq(1).html());
            } else {
                checked.pop($(this).closest('tr').find('td').eq(1).html());
            }

            $('[id*=hfCheckedIds]').val(checked.join(','));
        });
    });

    $(document).on("click", '.Pager .page', function () {
        GetCustomers(parseInt($(this).attr('page')));
    });

    $(document).on('click', '.view', function () {
        $('[id*=hfId]').val($(this).closest('tr').find('td').eq(1).html());
    });
    var i = 0;
    function GetCustomers(pageIndex) {
        $.ajax({
            type: "POST",
            url: "UseableCyl.aspx/GetCustomers",
            data: '{pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                $('[id$=gvDetails]').prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first"))).DataTable().destroy();
                var xmlDoc = $.parseXML(response.d);
                var xml = $(xmlDoc);
                var customers = xml.find("job_cylinder");
                var row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
                $("[id$=gvDetails] tbody tr").not($("[id$=gvDetails] tbody tr:first-child")).remove();
                $.each(customers, function () {
                    $("td", row).eq(1).html($(this).find("id").text());
                    $("td", row).eq(2).html($(this).find("no").text());
                    $("td", row).eq(3).html($(this).find("pid").text());
                    $("td", row).eq(4).html($(this).find("posino").text());
                    $("td", row).eq(5).html($(this).find("description").text());
                    $("td", row).eq(6).html($(this).find("colour").text());
                    $("td", row).eq(7).html($(this).find("orderid").text());
                    $("td", row).eq(8).html($(this).find("deliverydate").text());
                    $("td", row).eq(9).html($(this).find("purchaseorderno").text());
                    $("td", row).eq(10).html($(this).find("supplier").text());
                    $("td", row).eq(11).html($(this).find("waybillno").text());
                    $("td", row).eq(12).html($(this).find("totalrev").text());

                    // Set color based on condition.
                    if (parseInt($(this).find("totalrev").text()) > 100) {
                        $("td", row).eq(12).css("background-color", "green")
                    } else {
                        $("td", row).eq(12).css("background-color", "")
                    }                   
                   
                    $("td", row).eq(13).html($(this).find("comment").text());

                    $("[id$=gvDetails]").append(row);
                    row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
                });
                $("[id$=gvDetails] tbody tr:first-child").remove();
                if (i != 0) {
                    $('[id$=gvDetails]').DataTable({
                        "paging": false,
                        "info": false
                    });
                } else {
                    $('[id$=gvDetails]')
                        .prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first")))
                        .DataTable({
                            "paging": false,
                            "info": false
                        });
                }
                i++;
                var pager = xml.find("Pager");
                $(".Pager").ASPSnippets_Pager({
                    ActiveCssClass: "current",
                    PagerCssClass: "pager",
                    PageIndex: parseInt(pager.find("PageIndex").text()),
                    PageSize: parseInt(pager.find("PageSize").text()),
                    RecordCount: parseInt(pager.find("RecordCount").text())
                });
            },
            failure: function (response) {
                alert(response.responseText);
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    }
</script>

 

    private void BindDummyRow()
    {
        DataTable dummy = new DataTable();
        dummy.Columns.Add("id");
        dummy.Columns.Add("no");
        dummy.Columns.Add("pid");
        dummy.Columns.Add("posino");
        dummy.Columns.Add("description");
        dummy.Columns.Add("colour");
        dummy.Columns.Add("orderid");
        dummy.Columns.Add("deliverydate");
        dummy.Columns.Add("purchaseorderno");
        dummy.Columns.Add("supplier");
        dummy.Columns.Add("waybillno");
        dummy.Columns.Add("totalrev");
        dummy.Columns.Add("comment");

        dummy.Rows.Add();
        gvDetails.DataSource = dummy;
        gvDetails.DataBind();
        //gvDetails.UseAccessibleHeader = true;
        //gvDetails.HeaderRow.TableSection = TableRowSection.TableHeader;
    }

    [WebMethod]
    public static string GetCustomers(int pageIndex)
    {
        string query = "[cylin12]";
        SqlCommand cmd = new SqlCommand(query);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
        cmd.Parameters.AddWithValue("@PageSize", PageSize);
        cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;

        return GetData(cmd, pageIndex).GetXml();
    }

    private static DataSet GetData(SqlCommand cmd, int pageIndex)
    {
        string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds, "job_cylinder");
                    DataTable dt = new DataTable("Pager");
                    dt.Columns.Add("PageIndex");
                    dt.Columns.Add("PageSize");
                    dt.Columns.Add("RecordCount");
                    dt.Rows.Add();
                    dt.Rows[0]["PageIndex"] = pageIndex;
                    dt.Rows[0]["PageSize"] = PageSize;
                    dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
                    ds.Tables.Add(dt);
                    return ds;
                }
            }
        }
    }

 please help

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 14, 2021 12:07 AM

Hi lingers,

The code will not work for searching all pages record as uses paging, all the rows are not rendered. Only the requested page is rendered. So it will only search the record for the current page.

To resolve the issue you need to rebind the GridView using the data from database based on the search term.

For this you need to modify your procedure to pass the search term as parameter and apply where condition to the columns you want to search.

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

Stored Procedure

CREATE PROCEDURE [dbo].[Employees_GetEmployeesPageWise]
	@SearchTerm VARCHAR(100) = '',
	@PageIndex INT = 1,
	@PageSize INT = 20,
	@RecordCount INT OUTPUT
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT  (ROW_NUMBER() OVER(Order By EmployeeID)) AS RowNumber,
			*
	INTO #Results
	FROM [Employees]
	WHERE (FirstName LIKE @SearchTerm + '%' OR @SearchTerm = '')	
	OR (LastName LIKE @SearchTerm + '%' OR @SearchTerm = '')

	SELECT @RecordCount = Count(*) FROM #Results

	SELECT * FROM #Results WHERE 
	RowNumber BETWEEN  (@PageIndex-1)*@PageSize + 1 AND (((@PageIndex-1)*@PageSize + 1)+@PageSize)-1 OR @PageIndex = -1	

	DROP TABLE #Results
END

HTML

<script type="text/javascript" src=" https://code.jquery.com/jquery-3.5.1.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>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
<script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetEmployees(parseInt(1));
        var checked = [];
        $('[id*=chkSelect]').on('click', function () {
            if ($(this).is(":checked")) {
                checked.push($(this).closest('tr').find('td').eq(1).html());
            } else {
                checked.pop($(this).closest('tr').find('td').eq(1).html());
            }

            $('[id*=hfCheckedIds]').val(checked.join(','));
        });
    });

    $(document).on("click", '.Pager .page', function () {
        GetEmployees(parseInt($(this).attr('page')));
    });
    var term = '';
    $(document).on("keyup", 'input[type=search]', function () {
        term = $(this).val();
        GetEmployees(parseInt(1));
    });

    $(document).on('click', '.view', function () {
        $('[id*=hfId]').val($(this).closest('tr').find('td').eq(1).html());
    });

    function SearchTerm() {
        return jQuery.trim($("input[type=search]").val());
    };

    var i = 0;
    function GetEmployees(pageIndex) {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetEmployees",
            data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                $('[id$=gvEmployees]').prepend($("<thead></thead>").append($('[id$=gvEmployees]').find("tr:first"))).DataTable().destroy();
                var xmlDoc = $.parseXML(response.d);
                var xml = $(xmlDoc);
                var Employees = xml.find("Employees");
                var row = $("[id$=gvEmployees] tbody tr:last-child").eq(0).clone(true);
                $("[id$=gvEmployees] tbody tr").not($("[id$=gvEmployees] tbody tr:first-child")).remove();
                $.each(Employees, function () {
                    $("td", row).eq(1).html($(this).find("EmployeeID").text());
                    $("td", row).eq(2).html($(this).find("FirstName").text());
                    $("td", row).eq(3).html($(this).find("LastName").text());
                    $("td", row).eq(4).html($(this).find("Address").text());
                    $("td", row).eq(5).html($(this).find("City").text());
                    $("td", row).eq(6).html($(this).find("Country").text());
                    $("[id$=gvEmployees]").append(row);
                    row = $("[id$=gvEmployees] tbody tr:last-child").eq(0).clone(true);
                });
                $("[id$=gvEmployees] tbody tr:first-child").remove();
                if (i != 0) {
                    $('[id$=gvEmployees]').DataTable({
                        "paging": false,
                        "info": false
                    });
                } else {
                    $('[id$=gvEmployees]')
                        .prepend($("<thead></thead>").append($('[id$=gvEmployees]').find("tr:first")))
                        .DataTable({
                            "paging": false,
                            "info": false
                        });
                }
                if (term != '') {
                    $('input[type=search]').val(term);
                }
                $('input[type=search]').focus();
                i++;

                var pager = xml.find("Pager");
                $(".Pager").ASPSnippets_Pager({
                    ActiveCssClass: "current",
                    PagerCssClass: "pager",
                    PageIndex: parseInt(pager.find("PageIndex").text()),
                    PageSize: parseInt(pager.find("PageSize").text()),
                    RecordCount: parseInt(pager.find("RecordCount").text())
                });
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    }
</script>
<asp:HiddenField ID="hfCheckedIds" runat="server" />
<asp:HiddenField ID="hfId" runat="server" />
<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" Width="100%">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="EmployeeID" HeaderText="Id" />
        <asp:BoundField DataField="FirstName" HeaderText="First Name" />
        <asp:BoundField DataField="LastName" HeaderText="Last Name" />
        <asp:BoundField DataField="Address" HeaderText="Address" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button CssClass="view" Text="Unuseable" ID="Inkview" runat="server"
                    ForeColor="white" BackColor="#FF6600" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<br />
<div class="Pager" style="width: 100%"></div>

Namespaces

C#

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

VB.Net

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

Code

C#

private static int PageSize = 1;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDummyRow();
    }
}

private void BindDummyRow()
{
    DataTable dummy = new DataTable();
    dummy.Columns.Add("EmployeeID");
    dummy.Columns.Add("FirstName");
    dummy.Columns.Add("LastName");
    dummy.Columns.Add("Address");
    dummy.Columns.Add("City");
    dummy.Columns.Add("Country");
    dummy.Rows.Add();
    gvEmployees.DataSource = dummy;
    gvEmployees.DataBind();
}

[WebMethod]
public static string GetEmployees(int pageIndex, string searchTerm)
{
    string query = "[Employees_GetEmployeesPageWise]";
    SqlCommand cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    cmd.Parameters.AddWithValue("@PageSize", PageSize);
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;

    return GetData(cmd, pageIndex).GetXml();
}

private static DataSet GetData(SqlCommand cmd, int pageIndex)
{
    string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds, "Employees");
                DataTable dt = new DataTable("Pager");
                dt.Columns.Add("PageIndex");
                dt.Columns.Add("PageSize");
                dt.Columns.Add("RecordCount");
                dt.Rows.Add();
                dt.Rows[0]["PageIndex"] = pageIndex;
                dt.Rows[0]["PageSize"] = PageSize;
                dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
                ds.Tables.Add(dt);
                return ds;
            }
        }
    }
}

VB.Net

Private Shared PageSize As Integer = 1
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindDummyRow()
    End If
End Sub

Private Sub BindDummyRow()
    Dim dummy As DataTable = New DataTable()
    dummy.Columns.Add("EmployeeID")
    dummy.Columns.Add("FirstName")
    dummy.Columns.Add("LastName")
    dummy.Columns.Add("Address")
    dummy.Columns.Add("City")
    dummy.Columns.Add("Country")
    dummy.Rows.Add()
    gvEmployees.DataSource = dummy
    gvEmployees.DataBind()
End Sub

<WebMethod>
Public Shared Function GetEmployees(ByVal pageIndex As Integer, ByVal searchTerm As String) As String
    Dim query As String = "[Employees_GetEmployeesPageWise]"
    Dim cmd As SqlCommand = New SqlCommand(query)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm)
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
    cmd.Parameters.AddWithValue("@PageSize", PageSize)
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
    Return GetData(cmd, pageIndex).GetXml()
End Function

Private Shared Function GetData(ByVal cmd As SqlCommand, ByVal pageIndex As Integer) As DataSet
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Using con As SqlConnection = New SqlConnection(strConnString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using ds As DataSet = New DataSet()
                sda.Fill(ds, "Employees")
                Dim dt As DataTable = New DataTable("Pager")
                dt.Columns.Add("PageIndex")
                dt.Columns.Add("PageSize")
                dt.Columns.Add("RecordCount")
                dt.Rows.Add()
                dt.Rows(0)("PageIndex") = pageIndex
                dt.Rows(0)("PageSize") = PageSize
                dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
                ds.Tables.Add(dt)
                Return ds
            End Using
        End Using
    End Using
End Function

Screenshot