show only filter record data in asp.net

RAVI 1,056 Reputation points
2024-06-26T18:08:46.73+00:00

Hello

This is my MS SQl Data

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[F1] [varchar](50) NULL,
	[F2] [varchar](50) NULL,
	[F3] [varchar](50) NULL,
	[F4] [varchar](50) NULL,
	[F5] [varchar](50) NULL,
 CONSTRAINT [PK_Table1_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Table1] ON
INSERT [dbo].[Table1] ([ID], [F1], [F2], [F3], [F4], [F5]) VALUES (1, N'A', N'K', N'W', N'P', N'S')
INSERT [dbo].[Table1] ([ID], [F1], [F2], [F3], [F4], [F5]) VALUES (2, N'Z', N'L', N'Z', N'L', N'D')
INSERT [dbo].[Table1] ([ID], [F1], [F2], [F3], [F4], [F5]) VALUES (3, N'X', N'D', N'A', N'M', N'V')
INSERT [dbo].[Table1] ([ID], [F1], [F2], [F3], [F4], [F5]) VALUES (4, N'Y', N'C', N'Q', N'B', N'C')
INSERT [dbo].[Table1] ([ID], [F1], [F2], [F3], [F4], [F5]) VALUES (5, N'M', N'M', N'T', N'V', N'X')
SET IDENTITY_INSERT [dbo].[Table1] OFF

This is my aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="A.aspx.cs" Inherits="Default3" %> 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>demo</title>
<script type="text/javascript" src="jquery-3.2.1.min.js"></script> 
<script type="text/javascript" src="dt.js"></script>
<link rel="stylesheet" type="text/css" href=dt.css />
<link rel="stylesheet" type="text/css" href=select.css />
     <script type="text/javascript" src="select.js"></script>
      <script type="text/javascript" src="button.js"></script>
       <script type="text/javascript" src="buttona.js"></script>
        <script type="text/javascript" src="buttonab.js"></script>
     
 
   
    <script type="text/javascript">
        $(document).ready(function () {
            var thArray = [];
            $('#list > thead > tr > th').each(function () {
                thArray.push($(this).text())
            })
            var rowCount = $('table#list tbody tr').length;
            sessionStorage.setItem("rowCount", rowCount);
            // Remove the formatting to get integer data for summation
            var intVal = function (i) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '') * 1 :
                    typeof i === 'number' ?
                        i : 0;
            };
             $('#list').DataTable({
                "ordering": false,
                dom: 'Bfrtip',
                "buttons": [{
                    extend: 'excel',
                    footer: true,
                   // title: "Test",
                    title: "CHEMICAL ARRIVAL - ISSUE DETIALS",
                    className:'btn-success',
                    exportOptions: {
                       // columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16],
                        format: {
                            header: function (data, index, column) {
                                return thArray[index]
                            }
                        }
                    }
                }
                ],
                 
    lengthMenu: [
            [2000, 1500, 1000, 500, -1],
            [2000, 1500, 1000, 500, 'All'],
        ],
                
                initComplete: function () {
                    this.api().columns([1, 2, 3]).every(function () {
                        var title = this.header();
                        //replace spaces with dashes
                        title = $(title).html().replace(/[\W]/g, '-');
                        var column = this;
                        var select = $('<select id="' + title + '" class="select2" ></select>')
                            .appendTo($(column.header()).empty())
                            .on('change', function () {
                                //Get the "text" property from each selected data 
                                //regex escape the value and store in array
                                var data = $.map($(this).select2('data'), function (value, key) {
                                    return value.text ? '^' + $.fn.dataTable.util.escapeRegex(value.text) + '$' : null;
                                });
                                //if no data selected use ""
                                if (data.length === 0) {
                                    data = [""];
                                }
                                //join array into string with regex or (|)
                                var val = data.join('|');
                                //search for the option(s) selected
                                column
                                    .search(val ? val : '', true, false)
                                    .draw();
                            });
                        column.data().unique().sort().each(function (d, j) {
                            select.append('<option value="' + d + '">' + d + '</option>');
                        });
                        //use column title as selector and placeholder
                        $('#' + title).select2({
                            multiple: true,
                            closeOnSelect: false,
                            width: '100%',
                            placeholder: "" + title
                        });
                        //initially clear select otherwise first option is selected
                        $('.select2').val(null).trigger('change');
                    });
                },
                footerCallback: function (tfoot, data, start, end, display) {
                    let api = this.api();
                    api.column(2).footer().innerHTML = "GRAND TOTAL";
                    // Total over all pages
                    total = api
                        .column(3, { search: 'applied' })
                        .data()
                        .reduce((a, b) => intVal(a) + intVal(b), 0);
                           
                    
                        
                        
                    // Update footer
                   api.column(3).footer().innerHTML = total.toFixed(0);
                     
                       
                }
            });
        });
    </script>
<style>
    .btn-success {
            width: 110px;
            height: 40px;
            background-image: url('images/excelnew.png');
            background-repeat: no-repeat;
            background-size: cover;
            position: relative;
            left:682px; 
            top: -34px;
            display: block;
            text-indent: -9999em;
        }
        
        .select2-results__options[aria-multiselectable="true"] li {
            padding-left: 30px;
            position: relative;           
        }
            .select2-results__options[aria-multiselectable="true"] li:before {
                position: absolute;
                left: 8px;
                opacity: .6;
                top: 6px;
                font-family: "FontAwesome";
                content: "\f0c8";
            }
            .select2-results__options[aria-multiselectable="true"] li[aria-selected="true"]:before {
                content: "\f14a";
            }
            
            table {
     margin: 0 auto;
     margin-top: 20px;
     width: 100%;
     position: relative;
     overflow: auto;
 }
 th, thead {
     position: sticky;
     top: 0;
     border: 1px solid #dddddd;
     background-color: #ABEBC6;
     text-align: center;
     table-layout: fixed;
    
     height: 25px;
 }
    </style>
         
         
      
     
</head>
<body>
    <form id="form1" runat="server">
    <div>
       <div id="c1" runat="server">
        <table cellspacing="0" class="myClass" id="list" style="width: 1400px; font-family: Calibri; font-size: 11px; border-collapse: collapse; border: 1px solid black; z-index: 102;">
            <thead>
                <tr>
                
                  <th style="width:60px;text-align:center;">F1</th>
                    <th style="width:110px;text-align:center;">F2</th>
                    <th style="width:150px;text-align:center;">F3</th> 
                    <th style="width:40px;text-align:center;">F4</th>
                    <th style="width:40px;text-align:center;">F5</th>
                     
                    
                </tr>
            </thead>
            <tbody style="border-collapse: collapse; border: 1px solid black;">
                <asp:Repeater ID="Repeater1" runat="server">
                    <ItemTemplate>
                        <tr onmouseover="this.style.backgroundColor='#DFE7E5'" onmouseout="this.style.backgroundColor=''">
                           
                         <td style="text-align:center;border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F1")%></td>
                           
                            <td style="text-align:center;border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F2")%></td>
                            <td style="text-align:center;border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F3")%></td>
                          
                            <td style="text-align:right;border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F4")%></td>
                              <td style="text-align:right;border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F5")%></td> 
                                        
                        
                        </tr>
                    </ItemTemplate>
                </asp:Repeater>
            </tbody>
            <tfoot>
                <tr>
                                
             <td style="background: sandybrown; border: 1px solid black;"></td> 
                             
             <td style="background: sandybrown; border: 1px solid black;"></td>
                         
             <td style="text-align:right;border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;font-size:20px;"></td>
                         
             <td style="text-align:right;border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;font-size:20px;"></td> 
             
              
              
               <td style="background: sandybrown; border: 1px solid black;"></td>            
                          
               </tr>
            </tfoot>
        </table>
    </div>
    </div>
    </form>
</body>
</html>

This is my aspx code behind

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
using System.Drawing;
using System.IO;
using System.Net;
using System.Net.Mail;
using System.Net.Configuration;
public partial class Default3 : System.Web.UI.Page
{

    

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {


            DataTable dt = new DataTable();
            SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["CHEMIMSConnectionString"].ConnectionString);
            con1.Open();
            SqlCommand cmd1 = new SqlCommand("select * from Table1", con1);
            SqlDataAdapter ada1 = new SqlDataAdapter(cmd1);
            ada1.Fill(dt);
            con1.Close();
            con1.Dispose();
            Repeater1.DataSource = dt;
            Repeater1.DataBind();


        }

    }
    
    
}

It works perfect but when i filter F2 cloumn with K & L Data

In F3 search its showing all like this A Q T W Z

qqq

I Want In F3 Filter It Has to show only W Z

I Want In F4 Filter It Has to show only P L

how to do so thanking you

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,410 questions
{count} votes

Accepted answer
  1. Lan Huang-MSFT 28,831 Reputation points Microsoft Vendor
    2024-06-27T06:32:53.29+00:00

    Hi @RAVI,

    You can try to use Yet Another DataTables Column Filter - (yadcf) - (yadcf), which provides one feature: cumulative filtering.

    https://github.com/vedmack/yadcf?tab=readme-ov-file

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <script src="https://code.jquery.com/jquery-1.11.3.min.js"></script>
        <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
         <link href="https://cdnjs.cloudflare.com/ajax/libs/yadcf/0.9.4-beta.13/jquery.dataTables.yadcf.css" rel="stylesheet" type="text/css" />
        <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>
        <!-- Select2 plugin -->
        <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/css/select2.min.css" />
        <!-- Select2 plugin -->
        <script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/js/select2.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/yadcf/0.9.4-beta.13/jquery.dataTables.yadcf.min.js"></script>
        <script>
            $(document).ready(function () {
                var oTable;
                oTable = $('#list').DataTable();
                yadcf.init(oTable,
                    [
                        {
                            column_number: 1,
                            filter_type: "multi_select",
                            select_type: 'select2',
                            cumulative_filtering: false
                        },
                        {
                            column_number: 2,
                            filter_type: "multi_select",
                            select_type: 'select2'
                        },
                        {
                            column_number: 3,
                            filter_type: "multi_select",
                            select_type: 'select2',
                        }
                    ],
                    {
                        cumulative_filtering: true
                    }
                );
            });
        </script>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <div id="c1" runat="server">
                    <table cellspacing="0" class="myClass" id="list" style="width: 1400px; font-family: Calibri; font-size: 11px; border-collapse: collapse; border: 1px solid black; z-index: 102;">
                        <thead>
                            <tr>
                                <th style="width: 60px; text-align: center;">F1</th>
                                <th style="width: 110px; text-align: center;">F2</th>
                                <th style="width: 150px; text-align: center;">F3</th>
                                <th style="width: 40px; text-align: center;">F4</th>
                                <th style="width: 40px; text-align: center;">F5</th>
                            </tr>
                        </thead>
                        <tbody style="border-collapse: collapse; border: 1px solid black;">
                            <asp:Repeater ID="Repeater1" runat="server">
                                <ItemTemplate>
                                    <tr onmouseover="this.style.backgroundColor='#DFE7E5'" onmouseout="this.style.backgroundColor=''">
                                        <td style="text-align: center; border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F1")%></td>
                                        <td style="text-align: center; border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F2")%></td>
                                        <td style="text-align: center; border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F3")%></td>
                                        <td style="text-align: right; border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F4")%></td>
                                        <td style="text-align: right; border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("F5")%></td>
                                    </tr>
                                </ItemTemplate>
                            </asp:Repeater>
                        </tbody>
                        <tfoot>
                            <tr>
                                <td style="background: sandybrown; border: 1px solid black;"></td>
                                <td style="background: sandybrown; border: 1px solid black;"></td>
                                <td style="text-align: right; border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold; font-size: 20px;"></td>
                                <td style="text-align: right; border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold; font-size: 20px;"></td>
                                <td style="background: sandybrown; border: 1px solid black;"></td>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
        </form>
    </body>
    </html>
    

    User's image

    Best regards,
    Lan Huang


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful