Share via

Using AJAX AutoCompleteExtender with Web Services

Introduction

Hi Techies,

This is my very first post to the asp.net wiki and hope to have many post in the future.
Today I will demonstrate you on how to populate a text box with suggested values when user types a character in text box using a AJAX Auto Complete Extender. This demonstrate uses web service to get data from the database.

Demonstration

Suppose I have a database called "Demo" and have a table called "tblFlightInformation" to store Flight Schedule details.

Following is the database table script;
**
**

USE [Demo]
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 SET ANSI_PADDING ON
 GO
 CREATE TABLE  [dbo].[tblFlightInformation](
  [FlightId] [int] NOT NULL,
  [FlightDestination] [varchar](50) COLLATE  SQL_Latin1_General_CP1_CI_AS NULL,
  [FlightLeaveTime] [datetime] NULL,
  [FlightReturnTime] [datetime] NULL,
 CONSTRAINT [PK_tblFlightInformation] PRIMARY KEY CLUSTERED
 (
  [FlightId] ASC
 )WITH (IGNORE_DUP_KEY = OFF) ON  [PRIMARY]
 ) ON  [PRIMARY]
  
GO
 SET ANSI_PADDING OFF

Lets populate the table with sample data;

INSERT INTO  dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
 VALUES (1, 'India', '2011-11-12 19:30:00', '2011-11-12 23:00:00')
  
INSERT INTO  dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
 VALUES (2, 'Italy', '2011-11-13 06:00:00', '2011-11-13 12:45:00')
  
INSERT INTO  dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
 VALUES (3, 'Singapore', '2011-11-13 04:10:00', '2011-11-14 01:15:00')
  
INSERT INTO  dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
 VALUES (4, 'America', '2011-12-01 09:20:00', '2011-12-06 08:50:00')
  
INSERT INTO  dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
 VALUES (5, 'Saudi Arabia', '2011-12-10 23:10:00', '2011-12-13 19:10:00')

Lets create an asp.net web site and add a web service (WebServiceFlightInformation.asmx) and a web method called "LoadDestinationInformation".

[WebMethod]
 public List<string> LoadDestinationInformation(string prefixText)
 {
 using (SqlConnection conn = new SqlConnection())
 {
 conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightInformation"].ConnectionString;
  
using (SqlCommand cmd = new SqlCommand())
 {
 cmd.CommandText = "select FlightDestination from tblFlightInformation where " +
 "FlightDestination like @FlightDestination + '%'";
 cmd.Parameters.AddWithValue("@FlightDestination", prefixText);
 cmd.Connection = conn;
 conn.Open();
  
List<string> FlighDestination = new  List<string>();
  
using (SqlDataReader sdr = cmd.ExecuteReader())
 {
 while (sdr.Read())
 {
 FlighDestination.Add(sdr["FlightDestination"].ToString());
 }
 }
  
conn.Close();
 return FlighDestination;
 }

}

After that lets create the user interface by adding a aspx page to our web site. Following is the mark up for the aspx page.

<%@ Page Title="Home Page" Language="C#"  AutoEventWireup="true"
 CodeFile="Default.aspx.cs" Inherits="_Default" %>
  
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
  
<html xmlns="http://www.w3.org/1999/xhtml">
 <head id="Head1" runat="server">
 <title>Ajax AutoCompleteExtender without Webservice</title>
 </head>
 <body>
 <form id="form1" runat="server">
 <ajax:ToolkitScriptManager ID="ScriptManager1" runat="server"  EnablePageMethods = "true"/>
 <div>
 <asp:TextBox ID="txtCountry" runat="server"  AutoPostBack="True"></asp:TextBox>
 <ajax:AutoCompleteExtender ID="AutoCompleteExtender1"
runat="server"
TargetControlID="txtCountry"
 MinimumPrefixLength="1"
EnableCaching="true"
CompletionSetCount="1"
CompletionInterval="1000"
ServiceMethod="LoadDestinationInformation"
ServicePath="WebServiceFlightInformation.asmx">
 </ajax:AutoCompleteExtender>
 </div>
 </form>
 </body>
 </html>

See Also