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

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