Search records between two ComboBox selected values using C# and VB.Net in ASP.Net

Qasim5151
 
on Nov 21, 2021 10:02 PM
Sample_168960.zip
557 Views

I got solve 90% problem. but still not solve is age like 18 to 35 years old find age.

my age table field is (age). on HTML page have two combo box (age from and age to). but in table only 1 field age.

please solve this age issue

Protected Sub searchbtn_Click(sender As Object, e As EventArgs)
    Me.BindRepeater(fullnametxt.Value, maritalcombo.Value, castcombo.Value, religioncombo.Value, sectcombo.Value, citycombo.Value, countrycombo.Value, profileid.Value) 
End Sub 
Private Sub BindRepeater(ByVal Optional name As String = "", ByVal Optional mstatus As String = "", ByVal Optional cast As String = "", ByVal Optional Religion As String = "", ByVal Optional sect As String = "", ByVal Optional prescity As String = "", ByVal Optional prescountry As String = "", ByVal Optional ProfileID As String = "")
    Try 
        Dim constr As String = ConfigurationManager.ConnectionStrings("Constring").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr) 
            If genderlbl.Text = "Female" Then 
                Dim query As String = "SELECT * from Vw_searchprofile WHERE Username LIKE @Name + '%' AND (mstatus = @mstatus OR @mstatus = '') and (cast = @cast OR @cast = '') and (religion = @religion OR @religion = '') and (sect = @sect OR @sect = '') and (prescity = @prescity OR @prescity = '') and (prescountry = @prescountry OR @prescountry = '') and (ProfileID = @ProfileID OR @ProfileID = '') and gender='Male' and imgstatus='Verified' and profilestatus='Verified' and addrstatus='Verified' and edustatus='Verified'" 
                Using cmd As SqlCommand = New SqlCommand(query, con)
                    cmd.Connection = con
                    cmd.CommandText = query
                    cmd.Parameters.AddWithValue("@Name", name)
                    cmd.Parameters.AddWithValue("@mstatus", mstatus)
                    cmd.Parameters.AddWithValue("@cast", cast)
                    cmd.Parameters.AddWithValue("@religion", Religion)
                    cmd.Parameters.AddWithValue("@sect", sect)
                    cmd.Parameters.AddWithValue("@prescity", prescity)
                    cmd.Parameters.AddWithValue("@prescountry", prescountry)
                    cmd.Parameters.AddWithValue("@ProfileID", ProfileID) 
                    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 
                            Label2.InnerText = "" 
                            If dt.Rows.Count = 0 Then 
                                Label2.InnerText = "Your Search Record Not Found. Please Try again"
                            End If 
                            If dt.Rows.Count > 5 Then 
                                seemoreBtn.Visible = True                            End If 
                        End Using
                    End Using
                End Using 
            End If 
            If genderlbl.Text = "Male" Then 
                Dim query As String = "SELECT * from Vw_searchprofile WHERE Username LIKE @Name + '%' AND (mstatus = @mstatus OR @mstatus = '') and (cast = @cast OR @cast = '') and (religion = @religion OR @religion = '') and (sect = @sect OR @sect = '') and (prescity = @prescity OR @prescity = '') and (prescountry = @prescountry OR @prescountry = '') and (ProfileID = @ProfileID OR @ProfileID = '') and gender='Female' and imgstatus='Verified' and profilestatus='Verified' and addrstatus='Verified' and edustatus='Verified'" 
                Using cmd As SqlCommand = New SqlCommand(query, con)
                    cmd.Connection = con
                    cmd.CommandText = query
                    cmd.Parameters.AddWithValue("@Name", name)
                    cmd.Parameters.AddWithValue("@mstatus", mstatus)
                    cmd.Parameters.AddWithValue("@cast", cast)
                    cmd.Parameters.AddWithValue("@religion", Religion)
                    cmd.Parameters.AddWithValue("@sect", sect)
                    cmd.Parameters.AddWithValue("@prescity", prescity)
                    cmd.Parameters.AddWithValue("@prescountry", prescountry)
                    cmd.Parameters.AddWithValue("@ProfileID", ProfileID) 
                    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 
                            Label2.InnerText = "" 
                            If dt.Rows.Count = 0 Then 
                                Label2.InnerText = "Your Search Record Not Found. Please Try again"
                            End If 
                            If dt.Rows.Count > 5 Then 
                                seemoreBtn.Visible = True
                            End If 
                        End Using
                    End Using
                End Using 
            End If 
        End Using
    Catch ex As Exception
        msglbl.InnerText = "Please Try Again"
    End Try 
End Sub

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Nov 22, 2021 02:14 AM

Hi Quasim,

 Please refer below Sample.

Database

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

Download Northwind Database

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 />
Age From:<ajaxToolkit:ComboBox runat="server" ID="cbAgeFrom" AutoCompleteMode="SuggestAppend">
</ajaxToolkit:ComboBox>
<br />
Age To:<ajaxToolkit:ComboBox runat="server" ID="cbAgeTo" 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("EmployeeId")%></div>
                    <div id="Card3-card-item3"><%#Eval("FirstName")%> <%#Eval("LastName")%></div>
                    <div id="Card3-card-item4"><%#Eval("Country")%></div>
                </div>
            </div>
        </div>
        <hr />
    </ItemTemplate>
</asp:Repeater>

Namespaces

C#

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

VB.Net

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

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindCountryCombobox();
        this.BindAgeFromCombobox();
        this.BindAgeToCombobox();
        this.BindRepeater("", "");
    }
}

private void BindRepeater(string ageFrom, string ageTo, string name = "", string country = "")
{
    string query = "SELECT EmployeeId,FirstName,LastName,Country FROM Employees WHERE FirstName LIKE '%' + @Name + '%' ";
    query += " AND (Country = @Country OR @Country = '')";
    if (!string.IsNullOrEmpty(ageFrom) || !string.IsNullOrEmpty(ageTo))
    {
        query += " AND (EmployeeId BETWEEN @From AND @To)";
    }
    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);
            if (!string.IsNullOrEmpty(ageFrom) || !string.IsNullOrEmpty(ageTo))
            {
                cmd.Parameters.AddWithValue("@From", ageFrom);
                cmd.Parameters.AddWithValue("@To", ageTo);
            }

            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    searchrepter.DataSource = dt;
                    searchrepter.DataBind();
                }
            }
        }
    }
}

protected void OnSubmit(object sender, EventArgs e)
{
    this.BindCountryCombobox();
    this.BindAgeFromCombobox();
    this.BindAgeToCombobox();
    this.BindRepeater(cbAgeFrom.SelectedValue, cbAgeTo.SelectedValue, txtName.Text, cbCountries.SelectedValue);
}

private void BindCountryCombobox()
{
    BindComboBox(cbCountries, "SELECT DISTINCT Country FROM Employees", "Country", "Country");
}

private void BindAgeFromCombobox()
{
    BindComboBox(cbAgeFrom, "SELECT DISTINCT EmployeeID FROM Employees ORDER BY EmployeeID", "EmployeeID", "EmployeeID");
}

private void BindAgeToCombobox()
{
    BindComboBox(cbAgeTo, "SELECT DISTINCT EmployeeID FROM Employees ORDER BY EmployeeID", "EmployeeID", "EmployeeID");
}

private void BindComboBox(ComboBox cb, string query, string dataTextField, string dataValueField)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            cb.DataSource = cmd.ExecuteReader();
            cb.DataTextField = dataTextField;
            cb.DataValueField = dataValueField;
            cb.DataBind();
            con.Close();
        }
    }
}

VB.Net

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

Private Sub BindRepeater(ByVal ageFrom As String, ByVal ageTo As String, ByVal Optional name As String = "", ByVal Optional country As String = "")
    Dim query As String = "SELECT EmployeeId,FirstName,LastName,Country FROM Employees WHERE FirstName LIKE '%' + @Name + '%' "
    query += " AND (Country = @Country OR @Country = '')"

    If Not String.IsNullOrEmpty(ageFrom) OrElse Not String.IsNullOrEmpty(ageTo) Then
        query += " AND (EmployeeId BETWEEN @From AND @To)"
    End If
    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)
            If Not String.IsNullOrEmpty(ageFrom) OrElse Not String.IsNullOrEmpty(ageTo) Then
                cmd.Parameters.AddWithValue("@From", ageFrom)
                cmd.Parameters.AddWithValue("@To", ageTo)
            End If
            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

Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
    Me.BindCountryCombobox()
    Me.BindAgeFromCombobox()
    Me.BindAgeToCombobox()
    Me.BindRepeater(cbAgeFrom.SelectedValue, cbAgeTo.SelectedValue, txtName.Text, cbCountries.SelectedValue)
End Sub

Private Sub BindCountryCombobox()
    BindComboBox(cbCountries, "SELECT DISTINCT Country FROM Employees", "Country", "Country")
End Sub

Private Sub BindAgeFromCombobox()
    BindComboBox(cbAgeFrom, "SELECT DISTINCT EmployeeID FROM Employees ORDER BY EmployeeID", "EmployeeID", "EmployeeID")
End Sub

Private Sub BindAgeToCombobox()
    BindComboBox(cbAgeTo, "SELECT DISTINCT EmployeeID FROM Employees ORDER BY EmployeeID", "EmployeeID", "EmployeeID")
End Sub

Private Sub BindComboBox(ByVal cb As ComboBox, ByVal query As String, ByVal dataTextField As String, ByVal dataValueField As String)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.CommandText = query
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            cb.DataSource = cmd.ExecuteReader()
            cb.DataTextField = dataTextField
            cb.DataValueField = dataValueField
            cb.DataBind()
            con.Close()
        End Using
    End Using
End Sub

Screenshot