http://www.vbknowledgebase.com/?Id=107&Desc=Asp-.Net-Ajax-AutoComplete-Textbox-with-Database
Autocomplete Textbox In Asp.net
It is very cool to have Autocomplete Textbox In Asp.net for the textbox in the ASP.net. However if the Auto Complete is not responsive, then the users will feel that it is tedious to type the characters. As we need a very light weight and highly responsive solution, we have selected this WebService based approach rather than the Ajax Update Panel and Webpage to fetch the data.
CloudSpokes is a new dev community, focused exclusively on cloud platforms. Come develop new skills with real world contests and win cash for winning entries. Join us!
Autocomplete Textbox In Asp.net with WebService
For fetching data the data is transferred from web server to client (JavaScript) using web service. So the data transfer between server and client is very minimal based on the configuration value MinimumPrefixLength. Thus you feel it is responsive. As some animations will give a better user expereience, it is purely optional and the AutoCompleteExtender allows that as well
This article needs Ajax Control kit. It needs to be downloaded separately.
Once downloaded, create a new website and select the Default.Aspx and then switch to design mode in the Web Form for designing the page.
In Web Form
While in design mode, open the tool box and right click and choose items. In the "Choose Toolbox Item" choose the downloaded AjaxControlToolKit.dll.
Now you will see a list Ajax controls in the tool Box.
Drag the AutoCompleteExtender to the Web form. This will register the Tool Kit in the top.
Right click the website and add a web service named AutoComplete.asmx
CloudSpokes is a new dev community, focused exclusively on cloud platforms. Come develop new skills with real world contests and win cash for winning entries. Join us!
In Web Service
import AjaxControlToolkit using Imports AjaxControlToolkit in the top
Add a new function named as GetProducts with the following paramters
prefixText: Ajax control kit needs this string for getting the typed in characters from the screen
count: the number of rows to be returned
next step is to get data from DB
import System.Data using Imports System.Data in the top
import System.Data.SqlClient also
now get a datareader to fill up the Array list.
(I am not going to explain how to fetch the data from db as you can find the information in the asp.net data controls category)
I faced some issues while converting object array to string array, So checkout for the sample to get the clear picture.
Now its time to fill up the AutoCompleteExtender with the web service and web method we created just now.
Fill up the following
ServicePath =" AutoComplete.asmx"
ServiceMethod =" GetProducts "
MinimumPrefixLength ="1"
CompletionSetCount ="10"
If you are running the website now you may not get the suggestions when you type more than 1 Character(based on configuration MinimumPrefixLength ="1" ). Perhaps, The chances are the following line might be missed out
<System.Web.Script.Services.ScriptService ()>
It should be along with
<WebService(Namespace:="
Source Code for Autocomplete Textbox In Asp.net
Markup (*.ASPX)
<%@ Register Assembly= "AjaxControlToolkit" Namespace= "AjaxControlToolkit" TagPrefix= "cc1" %>
<!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>Ajax AutoComplete Sample</title>
</head>
<body>
<form id= "form1" runat= "server">
<asp:ScriptManager ID= "ScriptManager1" runat= "server" EnablepageMethods= "true">
</asp:ScriptManager>
<div>
<asp:TextBox ID= "TextBox1" runat= "server"></asp:TextBox>
<cc1:AutoCompleteExtender runat= "server" ID= "autoComplete1" TargetControlID= "TextBox1"
ServiceMethod= "GetProducts" ServicePath= "AutoComplete.asmx" MinimumPrefixLength= "1"
CompletionSetCount= "10">
</cc1:AutoCompleteExtender>
</div>
</form>
</body>
</html>
http://tempuri.org/ ")> _
<System.Web.Script.Services.ScriptService()> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)>
After this change hit the run. It should compile and run. If you type more than one Character (based on configuration MinimumPrefixLength ="1") it should suggest few items, based on the characters typed. If you are not getting any output, you may try running the WebService in browser by ‘set as start page’ on the WebService. The logic is for making sure the WebService is working as expected and returning the list of GetProducts.
CloudSpokes is a new dev community, focused exclusively on cloud platforms. Come develop new skills with real world contests and win cash for winning entries. Join us!
WebService Class(*.Vb)
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient
' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<WebService( Namespace:= "http://tempuri.org/")> _
<System.Web.Script.Services.ScriptService()> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
< Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class AutoComplete
Inherits System.Web.Services.WebService
Private ReadOnly Property ConnectionString() As String
Get
Return "Server=./SQLEXPRESS;Database=NorthWind;Trusted_Connection=True"
End Get
End Property
Private ReadOnly Property Connection() As SqlConnection
Get
Dim ConnectionToFetch As New SqlConnection(ConnectionString)
ConnectionToFetch.Open()
Return ConnectionToFetch
End Get
End Property
<WebMethod()> _
Public Function GetProducts( ByVal prefixText As String, _
ByVal count As Integer) As String()
Dim SelectQry = "select * from Products where ProductName like '%" & prefixText & "%'"
Dim Results As New ArrayList
Try
Using Command As New SqlCommand(SelectQry, Connection)
Using Reader As SqlDataReader = Command.ExecuteReader()
Dim Counter As Integer
While Reader.Read
If (Counter = count) Then Exit While
Results.Add(Reader( "ProductName").ToString())
Counter += 1
End While
End Using
Dim ResultsArray(Results.Count - 1) As String
ResultsArray = Results.ToArray(GetType(System. String))
Return ResultsArray
End Using
Catch ex As Exception
Throw ex
End Try
End Function
End Class