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:
  1. Save DataTable Into ViewState and Bind GridView Without DataBase Using ASP.Net.
  2. Insert Bulk Records Into DataBase Using ASP.Net C#.
  3. Creating AutoComplete Extender using ASP.NET.

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:
  1. CREATE TABLE [dbo].[ProdcutMaster](
  2. [ProductId] [int] IDENTITY(1,1) NOT NULL,
  3. [ProductName] [varchar](50) NULL,
  4. [BrandName] [varchar](50) NULL,
  5. [warranty] [int] NULL,
  6. [Price] [numeric](18, 2) NULL,
  7. CONSTRAINT [PK_ProdcutsSold] PRIMARY KEY CLUSTERED
  8. (
  9. [ProductId] ASC
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  11. ) 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:
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".

  2. "File" - "New WebSite" - "C#" - "Empty WebSite" (to avoid adding a master page).

  3. Provide the web site a name such as "FillControlUsingAutoComplete" or another as you wish and specify the location.

  4. Then right-click on Solution Explorer - "Add New Item" - Add Web Form.

  5. Drag and drop four textBoxes and ScriptManager onto the <form> section of the Default.aspx page.

  6. Add Ajax AutoComplete Extender from Ajax control Toolkit.

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

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
  3. <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
  4. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  5. <html xmlns="http://www.w3.org/1999/xhtml">
  6. <head id="Head1" runat="server">
  7. <title>Article for C#Corner</title>
  8. </head>
  9. <body style="background-color: #0000FF">
  10. <form id="form1" runat="server">
  11. <h4 style="color: White;">
  12. Article by Vithal Wadje
  13. </h4>
  14. <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true">
  15. </asp:ScriptManager>
  16. <table style="margin-top: 40px; color: White">
  17. <tr>
  18. <td>
  19. Product Name
  20. </td>
  21. <td>
  22. Brand Name
  23. </td>
  24. <td>
  25. warranty
  26. </td>
  27. <td>
  28. Price
  29. </td>
  30. </tr>
  31. <tr>
  32. <td>
  33. <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>
  34. <asp:AutoCompleteExtender ServiceMethod="GetCompletionList" MinimumPrefixLength="1"
  35. CompletionInterval="10" EnableCaching="false" CompletionSetCount="1" TargetControlID="TextBox1"
  36. ID="AutoCompleteExtender1" runat="server" FirstRowSelected="false">
  37. </asp:AutoCompleteExtender>
  38. </td>
  39. <td>
  40. <asp:TextBox ID="txtbrandName" runat="server"></asp:TextBox>
  41. </td>
  42. <td>
  43. <asp:TextBox ID="txtwarranty" runat="server"></asp:TextBox>
  44. </td>
  45. <td>
  46. <asp:TextBox ID="txtPrice" runat="server"></asp:TextBox>
  47. </td>
  48. </tr>
  49. </table>
  50. </form>
  51. </body>
  52. </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:
  1. [System.Web.Script.Services.ScriptMethod()]
  2. [System.Web.Services.WebMethod]
  3. public static List<string> GetCompletionList(string prefixText, int count)
  4. {
  5. return AutoFillProducts(prefixText);
  6. }
  7. private static List<string> AutoFillProducts(string prefixText)
  8. {
  9. using (SqlConnection con = new SqlConnection())
  10. {
  11. con.ConnectionString = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
  12. using (SqlCommand com = new SqlCommand())
  13. {
  14. com.CommandText = "select ProductName from ProdcutMaster where " + "ProductName like @Search + '%'";
  16. com.Parameters.AddWithValue("@Search", prefixText);
  17. com.Connection = con;
  18. con.Open();
  19. List<string> countryNames = new List<string>();
  20. using (SqlDataReader sdr = com.ExecuteReader())
  21. {
  22. while (sdr.Read())
  23. {
  24. countryNames.Add(sdr["ProductName"].ToString());
  25. }
  26. }
  27. con.Close();
  28. return countryNames;
  29. }
  30. }
  31. }
The preceding code will auto-populate the Product Names from the database. Now create the method that binds the TextBoxes 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.
  1. Create Procedure GetProductDet
  2. (
  3. @ProductName varchar(50)
  4. )
  5. as
  6. begin
  7. Select BrandName,warranty,Price from ProdcutMaster where ProductName=@ProductName
  8. End
Now create a function named GetProductMasterDet to get the product details from the database as:
  1. private void GetProductMasterDet(string ProductName)
  2. {
  3. connection();
  4. com = new SqlCommand("GetProductDet", con);
  5. com.CommandType = CommandType.StoredProcedure;
  6. com.Parameters.AddWithValue("@ProductName", ProductName);
  7. SqlDataAdapter da = new SqlDataAdapter(com);
  8. DataSet ds=new DataSet();
  9. da.Fill(ds);
  10. DataTable dt = ds.Tables[0];
  11. con.Close();
  12. //Binding TextBox From dataTable
  13. txtbrandName.Text =dt.Rows[0]["BrandName"].ToString();
  14. txtwarranty.Text = dt.Rows[0]["warranty"].ToString();
  15. txtPrice.Text = dt.Rows[0]["Price"].ToString();
  16. }
Set the TextBox1 AutoPostback Property to true as:
  1. <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:
  1. protected void TextBox1_TextChanged(object sender, EventArgs e)
  2. {
  3. //calling method and ing Values
  4. GetProductMasterDet(TextBox1.Text);
  5. }
The entire code of the default.aspx.cs page will look as follows:
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Data;
  8. using System.Configuration;
  9. using System.Data.SqlClient;
  11. public partial class _Default : System.Web.UI.Page
  12. {
  13. public SqlConnection con;
  14. public SqlCommand com;
  15. string constr;
  16. protected void Page_Load(object sender, EventArgs e)
  17. {
  19. }
  21. private void connection()
  22. {
  23. constr = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
  24. con = new SqlConnection(constr);
  25. con.Open();
  26. }
  28. [System.Web.Script.Services.ScriptMethod()]
  29. [System.Web.Services.WebMethod]
  30. public static List<string> GetCompletionList(string prefixText, int count)
  31. {
  32. return AutoFillProducts(prefixText);
  34. }
  36. private static List<string> AutoFillProducts(string prefixText)
  37. {
  38. using (SqlConnection con = new SqlConnection())
  39. {
  40. con.ConnectionString = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
  42. using (SqlCommand com = new SqlCommand())
  43. {
  44. com.CommandText = "select ProductName from ProdcutMaster where " + "ProductName like @Search + '%'";
  46. com.Parameters.AddWithValue("@Search", prefixText);
  47. com.Connection = con;
  48. con.Open();
  49. List<string> countryNames = new List<string>();
  50. using (SqlDataReader sdr = com.ExecuteReader())
  51. {
  52. while (sdr.Read())
  53. {
  54. countryNames.Add(sdr["ProductName"].ToString());
  55. }
  56. }
  57. con.Close();
  58. return countryNames;
  59. }
  61. }
  62. }
  64. private void GetProductMasterDet(string ProductName)
  65. {
  66. connection();
  67. com = new SqlCommand("GetProductDet", con);
  68. com.CommandType = CommandType.StoredProcedure;
  69. com.Parameters.AddWithValue("@ProductName", ProductName);
  70. SqlDataAdapter da = new SqlDataAdapter(com);
  71. DataSet ds=new DataSet();
  72. da.Fill(ds);
  73. DataTable dt = ds.Tables[0];
  74. con.Close();
  75. //Binding TextBox From dataTable
  76. txtbrandName.Text =dt.Rows[0]["BrandName"].ToString();
  77. txtwarranty.Text = dt.Rows[0]["warranty"].ToString();
  78. txtPrice.Text = dt.Rows[0]["Price"].ToString();
  79. }
  80. protected void TextBox1_TextChanged(object sender, EventArgs e)
  81. {
  82. //calling method and ing Values
  83. GetProductMasterDet(TextBox1.Text);
  84. }
  85. }
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 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.