Bind TextBox Controls From Autcomplete TextBox Using ASP.Net C#

Vithal Wadje
9y
74.7k
0
1

Article

Background

Sometimes there is a need to bind the TextBox Controls from an autocomplete TextBox using ASP.Net C#. Consider a scenario of retail stores that require a product to be auto-populated from the database in a text box when typing and after selecting the product bind other Text Boxes with the product details. In my previous article one of the readers asked me how to fill in textboxes from databases using an auto-complete TextBox Extender, so by considering the preceding requirement I decided to write this article.

Please refer to my previous articles to understand this article:

I hope you have read the preceding articles. Let us start creating an application so beginners can also understand.

First create the table named ProductsMaster using the following script:

CREATE TABLE [dbo].[ProdcutMaster](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](50) NULL,
[BrandName] [varchar](50) NULL,
[warranty] [int] NULL,
[Price] [numeric](18, 2) NULL,
CONSTRAINT [PK_ProdcutsSold] PRIMARY KEY CLUSTERED
(
[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Then the design view of the table will look such as follows:

Now Insert some Records into the Table as in the following:

Now let us create the sample WebApplication:

"Start" - "All Programs" - "Microsoft Visual Studio 2010".
"File" - "New WebSite" - "C#" - "Empty WebSite" (to avoid adding a master page).
Provide the web site a name such as "FillControlUsingAutoComplete" or another as you wish and specify the location.
Then right-click on Solution Explorer - "Add New Item" - Add Web Form.
Drag and drop four textBoxes and ScriptManager onto the <form> section of the Default.aspx page.
Add Ajax AutoComplete Extender from Ajax control Toolkit.

Now the default.aspx page source code will look such as follows.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Article for C#Corner</title>
</head>
<body style="background-color: #0000FF">
<form id="form1" runat="server">
<h4 style="color: White;">
Article by Vithal Wadje
</h4>
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true">
</asp:ScriptManager>
<table style="margin-top: 40px; color: White">
<tr>
<td>
Product Name
</td>
<td>
Brand Name
</td>
<td>
warranty
</td>
<td>
Price
</td>
</tr>
<tr>
<td>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>
<asp:AutoCompleteExtender ServiceMethod="GetCompletionList" MinimumPrefixLength="1"
CompletionInterval="10" EnableCaching="false" CompletionSetCount="1" TargetControlID="TextBox1"
ID="AutoCompleteExtender1" runat="server" FirstRowSelected="false">
</asp:AutoCompleteExtender>
</td>
<td>
<asp:TextBox ID="txtbrandName" runat="server"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtwarranty" runat="server"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtPrice" runat="server"></asp:TextBox>
</td>
</tr>
</table>
</form>
</body>
</html>

Now open Default.aspx.cs page and write the following code to auto-populate the product names from the database as in the following:

[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> GetCompletionList(string prefixText, int count)
{
return AutoFillProducts(prefixText);
}
private static List<string> AutoFillProducts(string prefixText)
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
using (SqlCommand com = new SqlCommand())
{
com.CommandText = "select ProductName from ProdcutMaster where " + "ProductName like @Search + '%'";
com.Parameters.AddWithValue("@Search", prefixText);
com.Connection = con;
con.Open();
List<string> countryNames = new List<string>();
using (SqlDataReader sdr = com.ExecuteReader())
{
while (sdr.Read())
{
countryNames.Add(sdr["ProductName"].ToString());
}
}
con.Close();
return countryNames;
}
}
}

The preceding code will auto-populate the Product Names from the database. Now create the method that binds the Text Boxes with the Product Details depending on the selected product from the AutoComplete TextBox.

Create a Stored Procedure that fetches the Product details depending on the selected product from the AutoComplete Text Box.

Create Procedure GetProductDet
(
@ProductName varchar(50)
)
as
begin
Select BrandName,warranty,Price from ProdcutMaster where ProductName=@ProductName
End

Now create a function named GetProductMasterDet to get the product details from the database as:

private void GetProductMasterDet(string ProductName)
{
connection();
com = new SqlCommand("GetProductDet", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@ProductName", ProductName);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds=new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
con.Close();
//Binding TextBox From dataTable
txtbrandName.Text =dt.Rows[0]["BrandName"].ToString();
txtwarranty.Text = dt.Rows[0]["warranty"].ToString();
txtPrice.Text = dt.Rows[0]["Price"].ToString();
}

Set the TextBox1 AutoPostback Property to true as:

<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>

Now call the preceding function GetProductMasterDet onTextChnaged event of the TextBox as:

protected void TextBox1_TextChanged(object sender, EventArgs e)
{
//calling method and ing Values
GetProductMasterDet(TextBox1.Text);
}

The entire code of the default.aspx.cs page will look as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
public SqlConnection con;
public SqlCommand com;
string constr;
protected void Page_Load(object sender, EventArgs e)
{
}
private void connection()
{
constr = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
con = new SqlConnection(constr);
con.Open();
}
[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> GetCompletionList(string prefixText, int count)
{
return AutoFillProducts(prefixText);
}
private static List<string> AutoFillProducts(string prefixText)
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
using (SqlCommand com = new SqlCommand())
{
com.CommandText = "select ProductName from ProdcutMaster where " + "ProductName like @Search + '%'";
com.Parameters.AddWithValue("@Search", prefixText);
com.Connection = con;
con.Open();
List<string> countryNames = new List<string>();
using (SqlDataReader sdr = com.ExecuteReader())
{
while (sdr.Read())
{
countryNames.Add(sdr["ProductName"].ToString());
}
}
con.Close();
return countryNames;
}
}
}
private void GetProductMasterDet(string ProductName)
{
connection();
com = new SqlCommand("GetProductDet", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@ProductName", ProductName);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds=new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
con.Close();
//Binding TextBox From dataTable
txtbrandName.Text =dt.Rows[0]["BrandName"].ToString();
txtwarranty.Text = dt.Rows[0]["warranty"].ToString();
txtPrice.Text = dt.Rows[0]["Price"].ToString();
}
protected void TextBox1_TextChanged(object sender, EventArgs e)
{
//calling method and ing Values
GetProductMasterDet(TextBox1.Text);
}
}

Now run the application and the UI will look as follows:

Now enter the first letter of the product then the products will be populated in the TextBox as follows:

Select Laptop then the selected product details will be bound to the text boxes as follows:

Now you have seen how the controls are filled in with product details after selecting the Product Name. Now type in another product name as follows:

Now select the Mouse product name, then the details will be filled in as follows:

Now you have seen how the controls are filled in with product details after selecting the product name.

Notes

For detailed code please download the sample Zip file.
Do proper validation such as date input values when implementing.
Make the changes in the web.config file depending on your server details for the connection string.
Add the reference of Ajax Control Toolkit library, if it has not been been downloaded then download it from the ASP.Net site.
Don't forget to set the auto-postback property of the Product Name TextBox to true.

Summary

From all the preceding examples you have learned how to bind controls from an autocomplete TextBox extender. I hope this article is useful for all readers, if you have a suggestion then please contact me.

Up Next

Ebook Download

View all

ASP.NET MVC 5: A Beginner’s Guide

Read by 10.8k people

Download Now!

Learn

View all