AutoComplete TextBox (Using AJAX AutoCompleteExtender)

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 description for WebService
///

[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: