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










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: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

  • GUEST
    Dear Sir,
    This is really very good example. I have done it in visual studio 2005. When I am typing in text box it is not showing anything

    I have debug the call Function is returning value "ResultsArray" has value. I have also check MinimumPrefixLength="1" but still facing the problem. Its quit urgent plz help me.

    Thanks in advance
    12/7/2010 10:25:08 PM

  • GUEST
    Sorry, I am quite away from my workstation. Are you able to see the results in the browser when calling the webservices directly? you may try testing this way. 12/8/2010 8:13:21 AM

  • GUEST
    my textbox is showning nothing while i type but my web servise is running fine.
    Wha'd be the error?????
    2/25/2011 2:51:17 AM

  • pons
    Can you put a break point and try by debugging and let us know the outcome
    Pon
    2/25/2011 3:36:59 AM

  • GUEST
    Thanks your help,It is very help full.. I was implemented into C#.. Check below the code...

    private SqlConnection Connection
    {
    get
    {

    SqlConnection cn = new SqlConnection();

    ConnectionStringSettingsCollection connectionStrings = WebConfigurationManager.ConnectionStrings as ConnectionStringSettingsCollection;
    cn.ConnectionString = (connectionStrings["ConnectionString"]).ToString();
    cn.Open();
    return cn;
    }
    }
    [WebMethod]
    public string[] GetProducts(string prefixText, int count)
    {
    dynamic SelectQry = "select * from Products where ProductName like '%" & prefixText & "%'";
    ArrayList Results = new ArrayList();
    try
    {
    using (SqlCommand Command = new SqlCommand(SelectQry, Connection))
    {
    using (SqlDataReader Reader = Command.ExecuteReader())
    {
    int Counter = 0;

    while (Reader.Read())
    {
    if ((Counter == count))
    break;
    Results.Add(Reader["ProductName"].ToString());
    Counter += 1;
    }


    }
    string[] ResultsArray = new string[Results.Count];


    ResultsArray = (string[])Results.ToArray(typeof(string));
    return ResultsArray;
    }
    }
    catch (Exception ex)
    {
    throw ex;
    }
    }
    5/18/2011 12:41:11 AM

  • GUEST
    its really helpful........ thanks for ur code.... 6/7/2011 10:40:20 PM

  • GUEST
    Thank you very much, its saved my time 8/24/2011 1:32:22 AM

  • GUEST
    sir,
    I tested the webservice i created and it is working. But while typing in the textbox it didn't show anything. I put the breakpoint in the webservice function. it didn't even stops at the breakpoint. Can you help me to find the solution.
    9/4/2011 11:52:08 PM

  • GUEST
    [System.ComponentModel.ToolboxItem(false)]
    [System.Web.Script.Services.ScriptService]
    9/15/2011 6:01:32 AM

  • GUEST
    My web service method is not calling?plz help 9/15/2011 6:02:44 AM

  • GUEST
    9/28/2011 7:03:20 AM

  • GUEST
    thanks a lot dude... :D 4/12/2012 11:06:01 PM

  • GUEST
    Hi; found this example online today and put it into my code. VB is not registering any errors or warnings but I too am unable to have anything auto complete in my textbox. Could you give me a few instances why this might be occurring? Thanks in advance. 8/13/2012 8:01:30 PM

  • GUEST
    gud one!! 9/16/2012 2:58:54 AM


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



Spam Bot Trap



   



Select Theme
White
Blue
Brown
Gray