Example of how to use Ajax control AutoCompleteExtender associated with an Access database
"AutoComplete is an ASP.NET AJAX extender that can be attached to any TextBox control, and will associate that control with a popup panel to display words that begin with the prefix typed into the textbox".
This example shows how to use this control, in a real-life example. When you start typing a word in the textbox below appears the list of cities that begin with those letters. If you select one of the cities on the list, the text will be pasted into the textbox.
Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AutoCompleteExtenderDemo.aspx.cs" Inherits="AutoCompleteExtenderDemo" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Example of how to use Ajax control AutoCompleteExtender associated with an Access database - MdmSoft</title>
<style type="text/css">
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ToolkitScriptManager ID="tsmCity" runat="server"></asp:ToolkitScriptManager>
<asp:TextBox ID="TextBoxCity" runat="server" Height="18px" Width="300px"></asp:TextBox>
<asp:AutoCompleteExtender
runat="server"
ID="AutoCompleteExtenderCity"
TargetControlID="TextBoxCity"
ServiceMethod="GetCompletionList"
MinimumPrefixLength="1"
CompletionInterval="500"
EnableCaching="true"
CompletionSetCount="15"
UseContextKey="True"
ShowOnlyCurrentWordInCompletionListItem="true">
</asp:AutoCompleteExtender>
</div>
</form>
</body>
</html>
Code:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Web;
public partial class AutoCompleteExtenderDemo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// This method returns an array with the list of cities that begin with prefixText
/// </summary>
/// <param name="prefixText"></param>
/// <param name="count"></param>
/// <param name="contextKey"></param>
/// <returns></returns>
[System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
public static string[] GetCompletionList(string prefixText, int count, string contextKey)
{
List<String> cities = GetCities(prefixText, count);
return cities.ToArray();
}
/// <summary>
/// This method queries a database and returns a list of cities that begin with prefixText
/// </summary>
/// <param name="prefixText"></param>
/// <param name="count"></param>
/// <returns></returns>
private static List<string> GetCities(string prefixText, int count)
{
// Define the connection string
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
HttpContext.Current.Server.MapPath("~/App_Data/Northwind.mdb");
// Define the SELECT command
string selectCommand = "SELECT DISTINCT TOP " + count + " City " +
"FROM Customers " +
"WHERE City LIKE '" + prefixText + "%' " +
"ORDER BY City";
List<String> suggestedCities = new List<string>();
DataTable dtSuggestedCities = new DataTable();
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
using (OleDbCommand command = new OleDbCommand(selectCommand,connection))
{
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(dtSuggestedCities);
}
}
if (dtSuggestedCities != null
&& dtSuggestedCities.Rows != null
&& dtSuggestedCities.Rows.Count > 0)
{
foreach (DataRow dr in dtSuggestedCities.Rows)
{
suggestedCities.Add(dr["City"].ToString());
}
}
return suggestedCities;
}
}
Result from the example:
