Search (Filter) records with multiple field in ASP.Net using C# and VB.Net

Qasim515...

SOLVED CLOSED ACTIVE ANSWERED
User: Qasim5151
Posted: on Nov 18, 2021 10:45 PM
Forum: ASP.Net Web Forms
Answer: 1
Views: 7720
Sample Code: Download

I want search in multiple tables with multiple field according to the mentioned field.

I have a problem I am making a matrimony website.

i am making a profile searching page. there has a multiple options like name, profile id, age from, age to, religion, caste, city, etc.

dear I have an issue cannot find multi fields.

if any field empties then search another filled field.

i am using textbox and combobox.

HTML code

<asp:Repeater ID="searchrepter" runat="server">
    <ItemTemplate>
        <div id="wb_Card3">
           <div id="Card3-card-body">
               <asp:HiddenField ID="imagestatus" runat="server" Value='<%#Eval("premission") %>' />
              <div id="Card3-card-overlay"><img class="Card3-card-item0" id="userimageshowID" src='<%#String.Concat("Membersphoto/", Eval("userimage")) %>' runat="server"  alt="" title=""></div>
              <div id="Card3-card-item1"><i class="fa fa-heart"></i> Free User</div>
              <div id="Card3-card-item2"><%#Eval("Username")%></div>
              <div id="Card3-card-item3"><%#Eval("ProfileID")%> | Profile Created By <%#Eval("profilecreated")%></div>
              <div id="Card3-card-item4"><%#Eval("age")%> Years | <%#Eval("heightft")%> <%#Eval("heightinc")%> | <%#Eval("face")%> </div>
              <hr id="Card3-card-item5">
                <div id="Card3-card-item10"><%#Eval("gender")%> | <%#Eval("mstatus")%></div>
              <hr id="Card3-card-item11">
              <div id="Card3-card-item6">M.B.A | University of Punjab</div>
              <hr id="Card3-card-item7">
              <div id="Card3-card-item8"><%#Eval("prescity")%> | <%#Eval("presprovince")%> | <%#Eval("prescountry")%></div>
              <hr id="Card3-card-item9">
              
              <div id="Card3-card-item12"><%#Eval("religion")%> | <%#Eval("sect")%> | <%#Eval("cast")%></div>
              <hr id="Card3-card-item13">
              <div id="Card3-card-item14">More Detail</div>
           </div>
        </div>
     </ItemTemplate>
 </asp:Repeater>

Private Sub searchuser()
    Dim constr As String = ConfigurationManager.ConnectionStrings("Constring").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        If genderlbl.Text = "Male" Then
            Using cmd As SqlCommand = New SqlCommand("SELECT dbo.Tbl_Personalinfo.Username, dbo.Tbl_Personalinfo.gender, dbo.Tbl_Personalinfo.age, dbo.Tbl_Personalinfo.religion, dbo.Tbl_Personalinfo.ProfileID, dbo.Tbl_userimage.userimage, dbo.Tbl_Personalinfo.mstatus, dbo.Tbl_Personalinfo.cast, dbo.Tbl_Personalinfo.sect, dbo.Tbl_Personalinfo.heightft, dbo.Tbl_Personalinfo.heightinc, dbo.Tbl_Personalinfo.face, dbo.Tbl_Personalinfo.profilecreated, dbo.Tbl_Personalinfo.profilestatus, dbo.Tbl_userimage.premission, dbo.Tbl_address.prescity, dbo.Tbl_address.presprovince, dbo.Tbl_address.prescountry,dbo.Tbl_address.status, dbo.Tbl_education.userwork,dbo.Tbl_education.status, dbo.Tbl_userimage.status FROM dbo.Tbl_Personalinfo INNER JOIN dbo.Tbl_userimage ON dbo.Tbl_Personalinfo.ProfileID = dbo.Tbl_userimage.ProfileID INNER JOIN dbo.Tbl_address ON dbo.Tbl_Personalinfo.ProfileID = dbo.Tbl_address.ProfileID INNER JOIN dbo.Tbl_education ON dbo.Tbl_Personalinfo.ProfileID = dbo.Tbl_education.ProfileID where dbo.Tbl_Personalinfo.gender='Female' and dbo.Tbl_userimage.status='Verified' and dbo.Tbl_Personalinfo.profilestatus='Verified' and dbo.Tbl_address.status='Verified' and dbo.Tbl_education.status='Verified' and and dbo.Tbl_education.status='Verified' and dbo.Tbl_Personalinfo.Username like '%" + fullnametxt.Value + "%' or dbo.Tbl_Personalinfo.age between '" + agefromcombo.Value + "' and '" + agetocombo.Value + "'", con)
                Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable()
                        sda.Fill(dt)
                        searchrepter.DataSource = dt
                        searchrepter.DataBind()
                        For Each item As RepeaterItem In searchrepter.Items
                            Dim img As HtmlImage = TryCast(item.FindControl("userimageshowID"), HtmlImage)
                            Dim imagestatus As HiddenField = TryCast(item.FindControl("imagestatus"), HiddenField)
                            If imagestatus.Value = "Hide" Then
                                img.Src = "images/femaleiconlock.png"
                            End If
                        Next
                    End Using
                End Using
            End Using
 
        End If
 
        If genderlbl.Text = "Female" Then
            Using cmd As SqlCommand = New SqlCommand("SELECT dbo.Tbl_Personalinfo.Username, dbo.Tbl_Personalinfo.gender, dbo.Tbl_Personalinfo.age, dbo.Tbl_Personalinfo.religion, dbo.Tbl_Personalinfo.ProfileID, dbo.Tbl_userimage.userimage, dbo.Tbl_Personalinfo.mstatus, dbo.Tbl_Personalinfo.cast, dbo.Tbl_Personalinfo.sect, dbo.Tbl_Personalinfo.heightft, dbo.Tbl_Personalinfo.heightinc, dbo.Tbl_Personalinfo.face, dbo.Tbl_Personalinfo.profilecreated, dbo.Tbl_Personalinfo.profilestatus, dbo.Tbl_userimage.premission, dbo.Tbl_address.prescity, dbo.Tbl_address.presprovince, dbo.Tbl_address.prescountry,dbo.Tbl_address.status, dbo.Tbl_education.userwork,dbo.Tbl_education.status, dbo.Tbl_userimage.status FROM dbo.Tbl_Personalinfo INNER JOIN dbo.Tbl_userimage ON dbo.Tbl_Personalinfo.ProfileID = dbo.Tbl_userimage.ProfileID INNER JOIN dbo.Tbl_address ON dbo.Tbl_Personalinfo.ProfileID = dbo.Tbl_address.ProfileID INNER JOIN dbo.Tbl_education ON dbo.Tbl_Personalinfo.ProfileID = dbo.Tbl_education.ProfileID where dbo.Tbl_Personalinfo.gender='Male' and dbo.Tbl_userimage.status='Verified' and dbo.Tbl_Personalinfo.profilestatus='Verified' and dbo.Tbl_address.status='Verified' and dbo.Tbl_education.status='Verified' and dbo.Tbl_Personalinfo.Username like '%" + fullnametxt.Value + "%' or dbo.Tbl_Personalinfo.age between '" + agefromcombo.Value + "' and '" + agetocombo.Value + "' ", con)                  
                Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable()
                        sda.Fill(dt)
                        searchrepter.DataSource = dt
                        searchrepter.DataBind()
                        For Each item As RepeaterItem In searchrepter.Items
                            Dim img As HtmlImage = TryCast(item.FindControl("userimageshowID"), HtmlImage)
                            Dim imagestatus As HiddenField = TryCast(item.FindControl("imagestatus"), HiddenField)
                            If imagestatus.Value = "Hide" Then
                                img.Src = "images/maniconlock.png"
                            End If
                        Next
                    End Using
                End Using
            End Using
        End If
    End Using
End Sub

Download FREE API for Word, Excel and PDF in ASP.Net

arjunv

0 5

Reply
ANSWER
User: arjunv
Replied: on Nov 20, 2021 01:33 AM
Report

Hi Quasim,

Please refer below sample.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:ScriptManager runat="server">
</asp:ScriptManager>
Name:<asp:TextBox runat="server" ID="txtName" />
<br />
Country:<ajaxToolkit:ComboBox ID="cbCountries" runat="server" AutoCompleteMode="SuggestAppend">
</ajaxToolkit:ComboBox>
<br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="OnSubmit" />
<hr />
<asp:Repeater ID="searchrepter" runat="server">
    <ItemTemplate>
        <div id="wb_Card3">
            <div id="Card3-card-body">
                <div id="Card3-card-overlay">
                    <div id="Card3-card-item2"><%#Eval("CustomerId")%></div>
                    <div id="Card3-card-item3"><%#Eval("ContactName")%> </div>
                    <div id="Card3-card-item4"><%#Eval("Country")%></div>
                </div>
            </div>
        </div>
        <hr />
    </ItemTemplate>
</asp:Repeater>

Namespaces

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindCombobox();
        this.BindRepeater();
    }
}

private void BindRepeater(string name = "", string country = "")
{
    string query = "SELECT CustomerId, ContactName, Country FROM Customers WHERE ContactName LIKE @Name + '%' AND (Country = @Country OR @Country = '')";
    string cs = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cs))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Country", country);
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    searchrepter.DataSource = dt;
                    searchrepter.DataBind();
                }
            }
        }
    }
}

private void BindCombobox()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT Country FROM Customers"))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            cbCountries.DataSource = cmd.ExecuteReader();
            cbCountries.DataTextField = "Country";
            cbCountries.DataValueField = "Country";
            cbCountries.DataBind();
            con.Close();
        }
    }
}

protected void OnSubmit(object sender, EventArgs e)
{
    this.BindCombobox();
    this.BindRepeater(txtName.Text, cbCountries.SelectedValue);
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindCombobox()
        Me.BindRepeater()
    End If
End Sub

Private Sub BindRepeater(ByVal Optional name As String = "", ByVal Optional country As String = "")
    Dim query As String = "SELECT CustomerId, ContactName, Country FROM Customers WHERE ContactName LIKE @Name + '%' AND (Country = @Country OR @Country = '')"
    Dim cs As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(cs)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.Connection = con
            cmd.CommandText = query
            cmd.Parameters.AddWithValue("@Name", name)
            cmd.Parameters.AddWithValue("@Country", country)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    searchrepter.DataSource = dt
                    searchrepter.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub

Private Sub BindCombobox()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT DISTINCT Country FROM Customers")
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            cbCountries.DataSource = cmd.ExecuteReader()
            cbCountries.DataTextField = "Country"
            cbCountries.DataValueField = "Country"
            cbCountries.DataBind()
            con.Close()
        End Using
    End Using
End Sub

Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
    Me.BindCombobox()
    Me.BindRepeater(txtName.Text, cbCountries.SelectedValue)
End Sub

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Search (Filter) records with multiple field in ASP.Net using C# and VB.Net

Company

Explore

Follow us

Contact Us