Using Ajax Auto-complete extender control

Now its easy to use auto-complete or auto-suggest text box in your asp.net project or website. It’s quite simple. Follow the steps carefully.

Step 1: Open visual studio

Step 2: Select a ajax enabled webapplication or ajax enabled website

Step 3: In the design view of default.aspx page, make sure script manager is in the page and if not add a script manager from the ajax tool controls

Step 4: Drag and drop the textbox and autocomplete extender control and rename textbox as mytextbox.

Step 5: Copy the access database you want to use to app_data folder of the website

Step 6: Right click on the app_data folder and then add existing item and add the mdb file and you can see the database appear in the application folder.Right click on the project->Add new item ->add a webservice and rename it as autocomplete.asmx.
( In case this is the first time you are creating a webservice you may get a prompt asking if you need to put the code in a separate class file. Select Yes )

Step 7: Now paste the code in autocomplete.cs file under the app_code folder. Change the query string and other credentials as applicable to your project

using System;
using System.Collections.Generic;
using System.Web.Services;
using System.Data;
using System.Data.OleDb;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class AutoComplete : WebService
{
[WebMethod]
public string[] GetCompletionList(string prefixText,int count, string contextKey)
{
count = 10;
string querystring;
OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Documents and Settings/user/My Documents/Visual Studio 2008/Projects/WebApplication1/WebApplication1/App_Data/data.mdb");
querystring = "select distinct student_name from student_query where course_name like @prefixText";
OleDbDataAdapter da = new OleDbDataAdapter(querystring, aConnection);
da.SelectCommand.Parameters.Add("@prefixtext", OleDbType.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[0].ToString(), i);
i++;
}
return items;
}
}


Step 8: In the default.aspx page make the following changes
For the autocomplete extender include the following properties

TargetControlID="myTextBox" ServicePath="AutoComplete.asmx" ServiceMethod="getCompletionlist" MinimumPrefixLength="1" UseContextKey="true" ContextKey="course">

Step 9 : Now if you run the project you should be able to see the suggestions for a particular letter depending on the words in your database.

Tips: If you have done all the above steps are still you are not able to find what is wrong with your project , there should be something wrong with your config file.
You don’t have to give up. Follow these steps now,
Go to this page
Watch the video if you are new and then download the c# code from there. Make necessary modification according to your project as you already have the webservice code to connect to access database. With little modification of the query strings you can use SQL datasource too.

Comments are welcome. Please let me know if this works for you.