AutoComplete TextBox (Using AJAX AutoCompleteExtender) from Database
Recently
I tried AutoComplete extender (AJAX toolkit) in one of my on going
web application. What I suppose to do is, user shall be able to get
the AutoSense list of available products from database on typing the
name of the product in a TextBox. When user types some letters in the
Textbox, a popup panel will come to action and displayed the related
words. So that the user can choose exact word from the popup panel.
BTW, Ajax extension and Ajax Tool Kit should already be installed to
implement any AJAX extenders. Here are the steps how to accomplish an
AutoComplete TextBox from Database:
-
We
start by creating new website. Create a new website by selecting
“ASP.NET AJAX-Enabled Web Site” from installed templates in “New
Web Site” window.
-
“ScriptManager”
would already be there in your webpage (Default.aspx), as we have
selected AJAX Enabled Website.
-
Now
drag and drop a Textbox from your Toolbox and AutoCompleteExtender
to your webpage.
-
Then
add a webservice to your project as WebService.asmx.
-
First
of all, you need to Import “System.Web.Script.Services”
namespace and add the “ScriptService” reference to the
webserive.
-
Next,
just need to write a simple webmethod ‘GetProducts’ to fetch the
data from the Product table which will return the string array with
product names.
-
We
will pass the “PrefixText” to this webmethod; I mean the
characters that are typed by the user in the textbox to get the
exact AutoComplete Product list form Database that start with the
characters typed by the user.
-
Here
is the complete webmethod for that:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Web.Script.Services;
/// <summary>
/// Summary description for WebService
/// </summary>
[ScriptService]
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebService : System.Web.Services.WebService
{
public WebService()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public string[] GetProducts(string prefixText)
{
string sql = "Select * from product Where name like @prefixText";
SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.AppSettings["DBConn"]);
da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataTable dt = new DataTable();
da.Fill(dt);
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["name"].ToString(), i);
i++;
}
return items;
}
}
-
you
can see that we have passed prefixText as argument in above
webmethod , which sends it to the query to fetch only the related
words that starts with the prefixText values. Then it returns the
result as an array of strings.
-
In
the your webpage, set the AutoCompleteExtender’s TargetControlID
property to the TextBox Id. You also need to set ServicePath
property as WebService.asmx, ServiceMethod as GetProducts and
MinimimPrefixLength as 1.
-
So,
your web page design code will be something like:
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<div>
<asp:TextBox ID="TextBox1" runat="server" Width="785px"></asp:TextBox>
<cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1" ServiceMethod="GetProducts" ServicePath="WebService.asmx" TargetControlID="TextBox1">
</cc1:AutoCompleteExtender>
</div>
</form>
Thats
it! Quite simple and a useful feature that user would like to have. Find the sample Code available to Download in attechment.