Asp .Net Ajax AutoComplete Textbox with Database

This article is written by Pon Saravanan on 27-Oct-09 Last modified on :22-Apr-10

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.

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

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:="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.

Source Code for Autocomplete Textbox In Asp.net

Markup (*.ASPX)

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<%@ 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>

WebService Class(*.Vb)

Imports System.Web
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

« Previous - Asp.Net Ajax CheckBoxList

JavaScript Ajax Cascading Dropdown - Next »

Comments

GUEST
Great tutorial but, is there a way that i can make it in c# 11/11/2009 9:38:32 AM
pons
you can Easyly convert it using some tools available online. Yes you may need to do some changes but mostly they are all working fine. 11/11/2009 5:55:05 PM
GUEST
This gives an error at "Return ResultsArray" - Value of type 1-dimensional array String cannot be converted to String 11/27/2009 4:32:50 PM
pons
hi
Can you please elaborate on which line are you getting this error? So far i am using this in various places, it is working fine. 11/27/2009 10:25:46 PM
GUEST
c 3/29/2010 5:34:02 AM
GUEST
dfgdgh 3/29/2010 7:06:15 AM
GUEST
Guest remember to put the () as the end of the string in the function method

Public Function GetProducts(ByVal prefixText As String, ByVal count As Integer) As String() <----- 4/29/2010 3:54:40 PM
GUEST
Hi,

I am using your code in my program. The web service returns result but when I type in the textbox it does not display anything. Do I need to add anythign other what is listed in the above code. 5/11/2010 2:57:51 PM
GUEST
Hi,

I am using your code in my program. The web service returns result but when I type in the textbox it does not display anything. Do I need to add anythign other what is listed in the above code. 5/11/2010 2:59:26 PM
pons
As per the code MinimumPrefixLength="1" so you need to type more than one charactor to start the auto complete. 5/11/2010 6:51:25 PM
GUEST
I did enter more than 1 character but it is not displaying anyuthing. Is there anywhere I can send you my code. 5/12/2010 7:42:14 AM
pons
Put a debug break in GetProducts class and proceed with further debuggging.

I will be happy to assist you with source code. But i guess you can try debugging once.
I just rechecked my samples in visual studio 2008, it is actually working fine. 5/12/2010 7:33:21 PM
GUEST
I found the answer. Instead of prefixTest I had used PrefixTest. it is now working fine. thanks for your help. 5/13/2010 12:20:27 PM
GUEST
hi pls tell me. i have three textboxes i need to add the autocomplete extender for these three textboxes. each textboxes values are from different column of different table. and each boxes having different methods.

Thanks in advance.
tvnathanmsc@yahoo.co.in 5/17/2010 6:54:43 AM
pons
Ok why not try with one text box first and try the others next. I am pretty sure it is possible.

Try and let me know how it goes.

5/17/2010 7:18:52 AM
GUEST
Please give this ource code for me 5/18/2010 10:51:46 AM
GUEST
nnn

6/25/2010 12:45:02 AM
GUEST
thank you soo much!!!!!!!
i've been searching and trying about this for the past weeks.

i just have one question. is it possible to search data from the cache? 7/5/2010 4:33:15 AM
GUEST
thank you soo much!!!!!!!
i've been searching and trying about this for the past weeks.

i just have one question. is it possible to search data from the cache? 7/5/2010 4:42:45 AM
GUEST
yes possible, you can make the GetProducts cache based. SO that it can take further data from cache. 7/5/2010 6:19:58 AM
GUEST
When i use this code and run my web application, I get folloing error and then the auto complete does not work.
'Sys.ArgumentUndefinedException: Value cannot be undefined.
Parameter name: type' 7/6/2010 11:15:07 AM
GUEST
sorry, below is the complete error message:

Microsoft JScript runtime error: Sys.ArgumentUndefinedException: Value cannot be undefined.
Parameter name: type 7/6/2010 11:18:53 AM
pons
Hi
Please use the sample as is in a seperate page. As the javascript is shared in the same page between the scripts. There is a high chance that another control is interfering the scripts with script manager.

You then try to locate the root cause from there. Alternatively you may remove few controls from that page until you dont have any errors. and find out which control is causing the trouble.

Hope you can narrow down the isssue. please let me know if you found the solution. Definetly it will be helpful for other readers 7/6/2010 6:53:24 PM
GUEST
hi.. how can i do this on cache based? can you give an example?? 7/7/2010 2:04:34 AM
pons
Ok I dont have enough time to develop the code for caching, However you can do something like following in the function GetProducts

function GetProducts(prefixText )
if(cache(prefixText ) isnot nothing )
return cach(prefixText )
else
'use the exisiting code
ResultsArray = Results.ToArray(GetType(System.String))
cache(prefixText)=ResultsArray 'you try this or use add function in cache to add

end if
end function

' if i have enough time in near future i will test and update here

thanks 7/7/2010 3:44:17 AM
GUEST
will this code work in Visual Studio 2008 ?? pls reply 7/29/2010 5:39:39 AM
pons
Yes, I have developed the application in the Visual Studio 2008 7/29/2010 7:03:50 PM
GUEST

hey guys im having problem here i dun know y im geting this error while running my code
Could not create type 'googlesuggest.AutoComplete'.
%@ WebService Language="C#" CodeBehind="AutoComplete.asmx.cs" Class="googlesuggest.AutoComplete" %
8/19/2010 12:16:55 AM
GUEST
8/19/2010 2:29:23 PM

Comments
Captcha Image
For you specially:
Captcha Text	Enter the text in the image.(Not Case sensitive)

Spam Bot Trap

Remember

Forgot Password

Configurations

Asp .Net

Javascript and CSS

Classic VB File IO

SQL Server

VB.Net Winforms

VisualBasic 6.0 Others

Asp .Net GridView

WPF

ASP.Net Data Controls

Ajax Tutorial

Select Theme
White
Blue
Brown
Gray

1020px

1260px

1024px

1250px