This coding example is to show how to use an AJAX Auto Complete Extender in ASP.NET to give users dropdown options for a textbox as they type.
autocomplete.aspx - This is your normal aspx page
Code:
<%@Page Language="VB" CodeFile="autocomplete.aspx.vb" Inherits="autocomplete" %>
<%@Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxtoolkit" %>
'ajaxcontroltoolkit.dll must be in your bin folder
<!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Auto Complete Extender</title>
</head>
<body>
<form id="form1" runat="server">
'the method used to grab the autocomplete data is contained in a
'webservice. We must tell the ScriptManager where to find this file and 'add it as a service
'Note: I call my .NET extensions prefix atlas. This is defined in your 'web.config and can be any prefix you want to use (just so you're not 'confused)
<atlas:ScriptManager ID="ScriptManager1" runat="server">
<Services>
<atlas:ServiceReference Path="autocomplete.asmx" />
</Services>
</atlas:ScriptManager>
<div align="center">
<asp:TextBox ID="textbox1" runat="server" />
'the prefix here is as defined in Registration of the ajax control
'toolkit at the top of our page.
'Basically, you define what textbox control you want to auto
'complete. In this case it's textbox1. You tell it where the service
'path can be found (autocomplete.asmx) and what the name of
'the method you've created in your web service (in this case
'getemails - you'll see this used later)
<ajaxtoolkit:AutoCompleteExtender ID="comp_textbox1" runat="server" TargetControlID="textbox1" ServicePath="autocomplete.asmx" ServiceMethod="getemails" MinimumPrefixLength="2" />
</div>
</form>
</body>
</html>
Then we have the web service code behind
autocomplete.vb - This is stored in your App_Code folder. Note the codebehind reference in our autocomplete.asmx file
Code:
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports MySql.Data.MySqlClient
' this is only needed because I use MySQL. The Mysql connector must be in your bin folder
Imports System.Collections.Generic
' Added to the default web service template as we're using a list to return the auto complete options.
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
<System.Web.Script.Services.ScriptService()> _
'we have to add this line to enable the service to be called from Javascript
Public Class autocomplete_email
Inherits System.Web.Services.WebService
<WebMethod()> _
'This is where our method starts. Note the name (getemails) is as
'used in the servicemethod when we added the autocomplete
'extender to our .aspx page
'prefixText is the text that the user enters into the textbox
Public Function getemails(ByVal prefixText As String, ByVal count As Integer)
Dim connString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("myconnection").ConnectionString
Dim conn As New MySqlConnection(connString)
Dim mycommand As New MySqlCommand
Dim rs As MySqlDataReader
Dim items As New List(Of String)
'we add the prefixText into our SQL query to retrieve the options
'for our autocomplete
Dim strsql As String = "SELECT email FROM users WHERE email LIKE '" & replace(prefixText,"'","''") & "%' ORDER BY email ASC"
mycommand.Connection = conn
mycommand.CommandText = strsql
Try
conn.Open()
rs = mycommand.ExecuteReader
If rs.HasRows Then
'we loop through the results adding each result to our list of
'items
Do While rs.Read
items.Add(rs("email"))
Loop
End If
Catch ex As Exception
items.Add(ex.ToString)
' this will show the exception in the autocomplete list if
' there are any errors
Finally
mycommand.Dispose()
conn.Close()
conn.Dispose()
End Try
Return items.ToArray ' we return our list of items as an array
End Function
End Class
Hope that helps.
March 26th, 2008, 07:59 AM
Using AJAX Auto Complete Extender with Database 
Similar Threads 
Linear Mode
