Search filter ASP.Net ListBox on TextBox change using C# and VB.Net

micah
 
on Oct 25, 2021 10:48 PM
3156 Views

How do I search first and second letter of of an item and have it displayed on Listbox?

I have a Listbox that display item names from the databasebut I want the user to search the item in the textbox by typing the first and the second letter of the item and it will show on the Listbox.

HTML

<div class="form-group-inner">
    <div class="row">
        <div class="">
            <div class="input-group mg-b-pro-edt">
                <span class="input-group-addon"><i class="fa fa-sticky-note-o" 
                    aria-hidden="true"></i></span>
                <asp:TextBox ID="txitemname" runat="server" CssClass="form-control" 
                    placeholder="Search Item Names"></asp:TextBox>
            </div>
        </div>
    </div>
</div>

<asp:UpdatePanel>
    <ContentTemplate>
        <asp:ListBox ID="lbCategories" CssClass="form-control dual_select" runat="server" multiple=""
            AutoPostBack="true" OnSelectedIndexChanged="OnSelectedIndexChanged" Font-Size="Large"></asp:ListBox>
        <hr />
        <div>
            <table>
                <tr>
                    <td>
                        <asp:PlaceHolder ID="ph1" runat="server" />
                        <br />
                        <asp:Button ID="btnAdd" runat="server" Text="Add" CssClass="btn btn-primary"  />
                    </td>
                </tr>
            </table>
        </div>
    </ContentTemplate>
</asp:UpdatePanel>

CODE

        protected void OnSave(object sender, EventArgs e)
        {
            if (Page.IsValid)
            {
                if (txtrecipt.Text == "")
                {
                    ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "<script>alert('Enter Recipt ...!!')</script>", false);
                }
                else
                {
                    DataTable dt = new DataTable();
                    dt.Columns.AddRange(new DataColumn[] { new DataColumn("Item"), new DataColumn("Price"), new DataColumn("Quantity") });
                    foreach (Control c in ph1.Controls)
                    {
                        if (c.GetType().Name.ToLower() == "usercontrol_ascx")
                        {
                            UserControl uc = (UserControl)c;

                            TextBox tbItem = uc.FindControl("txtItem") as TextBox;
                            TextBox tbPrice = uc.FindControl("txtPrice") as TextBox;
                            TextBox tqty = uc.FindControl("txtQuantity") as TextBox;
                            if (!string.IsNullOrEmpty(tbItem.Text.Trim()) && !string.IsNullOrEmpty(tbPrice.Text.Trim()) && !string.IsNullOrEmpty(tqty.Text.Trim()))
                            {
                                dt.Rows.Add(tbItem.Text.Trim(), tbPrice.Text.Trim(), tqty.Text.Trim());
                                if (Convert.ToInt32(tqty.Text.Trim()) > 0)
                                {
                                    int availableQuantity = Convert.ToInt32(GetAvailableQuantity(tbItem.Text.Trim()));
                                    if (Convert.ToInt32(tqty.Text.Trim()) > availableQuantity)
                                    {
                                        ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('No of Quantity entered is not available in Stock')", true);
                                    }
                                    else
                                    {
                                        int inserted = Insert(tbItem.Text.Trim(), tbPrice.Text.Trim(), tqty.Text.Trim());
                                        if (inserted > 0)
                                        {
                                            UpdateStock(tbItem.Text.Trim(), tqty.Text.Trim());
                                            ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('Data Submitted Successfully ... !!')", true);
                                        }
                                    }
                                }
                            }
                        }
                    }
                    // BindGridID();
                    gvInsertedRecords.DataSource = dt;
                    gvInsertedRecords.DataBind();
                }
            }
        }

        private string GetAvailableQuantity(string item)
        {
            string availableQuantity = "";
            string constr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                string query = "SELECT Qty_Remaining FROM Store_Table WHERE Item = @Item AND Store=@Store";
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@Store", Department.SelectedItem.Text);
                    cmd.Parameters.AddWithValue("@Item", item);
                    con.Open();
                    availableQuantity = Convert.ToString(cmd.ExecuteScalar());
                    con.Close();
                }
            }

            return availableQuantity;
        }
        private int Insert(string item, string price, string qty)
        {
            int i = 0;
            string constr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                string query = "INSERT INTO ItemOrdered (Store,SellsPerson,Receipt,Item,Price,Quantity,Sum) VALUES (@Store,@SellsPerson,@Receipt,@Item, @Price, @Quantity,@Sum)";


                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@Store", Department.SelectedItem.Text);
                    cmd.Parameters.AddWithValue("@SellsPerson", HttpContext.Current.User.Identity.Name);
                    cmd.Parameters.AddWithValue("@Receipt", txtrecipt.Text);
                    cmd.Parameters.AddWithValue("@Item", item);
                    cmd.Parameters.AddWithValue("@Price", price);
                    cmd.Parameters.AddWithValue("@Quantity", qty);
                    cmd.Parameters.AddWithValue("@Sum", Convert.ToDecimal(price) * Convert.ToDecimal(qty));
                    con.Open();
                    i = cmd.ExecuteNonQuery();
                    con.Close();

                }
            }

            return i;
        }

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Oct 26, 2021 12:43 AM
on Oct 26, 2021 11:15 PM

Hi Micah,

   kindly refer below Sample.

HTML

<div class="form-group-inner">
    <div class="row">
        <div class="">
            <div class="input-group mg-b-pro-edt">
                <span class="input-group-addon"><i class="fa fa-sticky-note-o"
                    aria-hidden="true"></i></span>
                <asp:TextBox ID="txName" runat="server" CssClass="form-control"
                    placeholder="Search Names" AutoPostBack="true" OnTextChanged="OnSearch"></asp:TextBox>
            </div>
        </div>
    </div>
</div>
<hr />
<asp:ListBox runat="server" ID="lstCustomers"></asp:ListBox>

Namespaces

C#

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

VB.Net

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

Code

C#

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

protected void OnSearch(object sender, EventArgs e)
{
    this.BindListBox();
}

private void BindListBox()
{
    string conn = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conn))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name FROM Customers WHERE Name LIKE @Name + '%'", con))
        {
            cmd.Parameters.AddWithValue("@Name", txName.Text);
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    lstCustomers.DataSource = dt;
                    lstCustomers.DataTextField = "Name";
                    lstCustomers.DataValueField = "CustomerId";
                    lstCustomers.DataBind();
                }
            }
        }
    }
}

VB.Net

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

Protected Sub OnSearch(ByVal sender As Object, ByVal e As EventArgs)
    Me.BindListBox()
End Sub

Private Sub BindListBox()
    Dim conn As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conn)
        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId, Name FROM Customers WHERE Name LIKE @Name + '%'", con)
            cmd.Parameters.AddWithValue("@Name", txName.Text)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    lstCustomers.DataSource = dt
                    lstCustomers.DataTextField = "Name"
                    lstCustomers.DataValueField = "CustomerId"
                    lstCustomers.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub

Screenshot