Click here to Skip to main content
13,051,413 members (63,570 online)
Click here to Skip to main content
Add your own
alternative version


107 bookmarked
Posted 24 May 2011

AutoComplete With DataBase and AjaxControlToolkit

, 8 Jul 2011
Rate this:
Please Sign up or sign in to vote.
This is a very simple code which makes an Auto Complete Combo with database.
Sample Image - maximum width is 600 pixels


This is very simple code which makes an Auto Complete Combo with database.

It's useful. First of all, you do not have to know about Ajax functions, just download the AJAX Control Toolkit on CodePlex and follow me, then enjoy. Besides when there are many rows, you can type part of the word in the text box, then it can offer all of the words which are similar to it.

Background: What is Ajaxcontroltoolkit?

The ASP.NET AJAX Control Toolkit is an open-source project built on top of the Microsoft ASP.NET AJAX framework and contains more than 30 controls that enable you to easily create rich, Interactive web pages. If you want to know more about it, visit this link.

Using the Code

It the first step, you must download AjaxControlToolkit from here for .NET 3.5 OR here for .NET 4.0.

You must go here and download ajaxcontroltoolkit, then Copy ajaxcontroltoolkit and paste them to Bin Folder, right click on solution, choose Add Reference, in the browse tab double click on the Bin Folder, and double click on ajaxcontroltoolkit, then on the Build Menu > click Rebuild.


New Query

CREATE TABLE [dbo].[tblCustomer](
	    [CompanyName] [nvarchar](500) NULL,
	    [ID] [int] IDENTITY(1,1) NOT NULL
        ) ON [PRIMARY]

insert into dbo.tblCustomer(CompanyName) values('calemard')
insert into dbo.tblCustomer(CompanyName) values('dantherm')
insert into dbo.tblCustomer(CompanyName) values('dango dienenthal')
insert into dbo.tblCustomer(CompanyName) values('daewoo')
insert into dbo.tblCustomer(CompanyName) values('daim engineering')

Visual Studio 2008 - .NET 3.5: Create Web Site and name it AutoComplete, create Web Form and name it AutoComplete.aspx, in HTML view, write this code.
But there is a little difference between C# and VB in this section.

  1. This code in the bottom is for C# coders.
  2. If you are a VB coder, please modify 2 sections in page tag
    1. One: correct language=VB
    2. Two: correct CodeFile="AutoComplete.aspx.vb"
<%@ Page Language="C#" AutoEventWireup="false"

         CodeFile="AutoComplete.aspx.cs" Inherits="AutoComplete" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit"

         TagPrefix="ajaxToolkit" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

<html xmlns="">
<head runat="server">
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
<form id="form1" runat="server">
<ajaxToolkit:ToolkitScriptManager  ID="ScriptManager1" runat="server">
<ajaxToolkit:AutoCompleteExtender ID="autoComplete1" runat="server"












  DelimiterCharacters=";, :"

  <%-- Make the completion list transparent and then show it --%>
  <OpacityAction Opacity="0" />
  <HideAction Visible="true" />

  <%--Cache the original size of the completion list the first time
    the animation is played and then set it to zero --%>
  <ScriptAction Script="// Cache the size and setup the initial size
                                var behavior = $find('AutoCompleteEx');
                                if (!behavior._height) {
                                    var target = behavior.get_completionList();
                                    behavior._height = target.offsetHeight - 2;
                           = '0px';
                                }" />
  <%-- Expand from 0px to the appropriate size while fading in --%>
  <Parallel Duration=".4">
  <FadeIn />
  <Length PropertyKey="height" StartValue="0" 

	EndValueScript="$find('AutoCompleteEx')._height" />
  <%-- Collapse down to 0px and fade out --%>
  <Parallel Duration=".4">
  <FadeOut />
  <Length PropertyKey="height" StartValueScript=
	"$find('AutoCompleteEx')._height" EndValue="0" />
<asp:TextBox ID="myTextBox" autocomplete ="off" runat="server"></asp:TextBox>


For StyleSheet (CSS file)

  • Create StyleSheet: Solution > Right Click > Add New Item > Web Service >
  • Name: StyleSheet.css
  • Language: Visual Basic OR C#
  • Go To > StyleSheet.css (File) > Ctrl+A (Select All) > Delete
  • Go to this section (below) > Select this code > Ctrl+C >
  • Go To StyleSheet.css (file) > Ctrl+V (paste)
/*AutoComplete flyout */
    margin : 0px!important ;
    background-color : inherit ;
    color : windowtext ;
    border : buttonshadow ;
    border-width : 1px ;
    border-style : solid ;
    cursor : 'default' ;
    overflow : auto ;
    height : 200px ;
    font-family : Tahoma ;
    font-size : small ;
    text-align : left ;
    list-style-type : none ;
/* AutoComplete highlighted item */
    background-color : #ffff99 ;
    color : black ;
    padding : 1px ;

    /* AutoComplete item */
    background-color : window ;
    color : windowtext ;
    padding : 1px ;

For VB

  • Create Web Service: Solution > Right Click > Add New Item > Web Service >
  • Name: AutoComplete.asmx
  • Language: Visual Basic
  • Go To > App_Code > AutoComplete.vb
' (c) Copyright Microsoft Corporation.
' This source is subject to the Microsoft Public License.
' See
' All other rights reserved.
Imports System
Imports System.Collections
Imports System.Linq
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Xml.Linq
Imports System.Collections.Generic
Imports System.Data
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:="")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class AutoComplete
    Inherits System.Web.Services.WebService
    Dim cn As New SqlClient.SqlConnection()
    Dim ds As New DataSet
    Dim dt As New DataTable
<WebMethod()> _
Public Function GetCompletionList(ByVal prefixText As String, _
	ByVal count As Integer) As String()

        Dim strCn As String = _
		"data source=.;Initial Catalog=MyDB;Integrated Security=True"
        cn.ConnectionString = strCn
        Dim cmd As New SqlClient.SqlCommand
        cmd.Connection = cn
        cmd.CommandType = CommandType.Text
        'Compare String From Textbox(prefixText) 
        'AND String From Column in DataBase(CompanyName)
        'If String from DataBase is equal to String from TextBox(prefixText) 
        'then add it to return ItemList
        '-----I defined a parameter instead of passing value 
        'directly to prevent SQL injection--------'
        cmd.CommandText = "select * from tblCustomer Where CompanyName like @myParameter"
        cmd.Parameters.AddWithValue("@myParameter", "%" + prefixText + "%")

            Dim da As New SqlDataAdapter(cmd)
        Catch ex As Exception
        End Try

        dt = ds.Tables(0)

        'Then return List of string(txtItems) as result

        Dim txtItems As New List(Of String)
        Dim dbValues As String

        For Each row As DataRow In dt.Rows

            ''String From DataBase(dbValues)
            dbValues = row("CompanyName").ToString()
            dbValues = dbValues.ToLower()


        Return txtItems.ToArray()

    End Function

End Class

For C#

  • Web Service: Solution > Right Click > Add New Item > Web Service >
  • Name: AutoComplete.asmx
  • Language: C#
  • Go To > App_Code > AutoComplete.cs
// (c) Copyright Microsoft Corporation.
// This source is subject to the Microsoft Public License.
// See
// All other rights reserved.
using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

/// Summary description for AutoComplete

[WebService(Namespace = "")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, 
// uncomment the following line.
public class AutoComplete : System.Web.Services.WebService {

    public AutoComplete () {

        //Uncomment the following line if using designed components

    public string[] GetCompletionList(string prefixText, int count)
        SqlConnection cn =new SqlConnection();
        DataSet ds = new DataSet();
        DataTable  dt = new DataTable();
        String strCn = "data source=.;Initial Catalog=MyDB;Integrated Security=True";
        cn.ConnectionString = strCn;
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.Text;
        //Compare String From Textbox(prefixText) AND String From 
        //Column in DataBase(CompanyName)
        //If String from DataBase is equal to String from TextBox(prefixText) 
        //then add it to return ItemList
        //-----I defined a parameter instead of passing value directly to 
        //prevent SQL injection--------//
        cmd.CommandText = "select * from tblCustomer Where CompanyName like @myParameter";
        cmd.Parameters.AddWithValue("@myParameter", "%" + prefixText + "%");

            SqlDataAdapter da = new SqlDataAdapter(cmd);
        dt = ds.Tables[0];

	    //Then return List of string(txtItems) as result
        List<string> txtItems =new List<string>();
        String  dbValues;

        foreach (DataRow row  in dt.Rows)
             //String From DataBase(dbValues)
            dbValues = row["CompanyName"].ToString();
            dbValues = dbValues.ToLower();

        return txtItems.ToArray();


GetCompletionList is a function that catches 2 arguments, prefixText as string and count as int.

When you type some characters, they are saved in prefixText and number of your characters are saved in count. And at the end, function returns list of string (they are similar to your characters) which has been obtained as follows:

I have written some code in ADO.NET section, these rows have been filtered by prefixText, which are similar to your characters that are typed in text box.
Additionally, I defined a parameter instead of passing value directly to prevent SQL injection.

I create txtItems in List of string data type, we can save words that we want. Then in a foreach loop, I converted them into tolower,  I added those values to my result value (txtItems), finally I return txtItems.

Try Step by Step

  1. Go here for .NET 3.5 OR here for .NET 4.0 and download the AjaxControlToolkit file.
  2. Copy the folder "AjaxControlToolkit.Dll" and all dependers, there are 18 objects, to your web site Bin folder (C:\AutoComplete\Bin).
  3. Right click on solution, choose refresh, then right click again and click add reference, then in the browse tab double click on the Bin Folder, and double click on ajaxcontroltoolkit, on the Build Menu > click Rebuild.
  4. Create database and tables like above, and add some rows which have common words.
  5. Create Web Form and name it: "AutoComplete.aspx". In the HTML view, write some code like above. (This should be exactly like my code because this section is case sensitive).
  6. Create a webservice:
    Solution > Right Click > Add New Item > Web Service > Name: AutoComplete.asmx Language: C# or VB
    Go To > App_Code > AutoComplete.cs
  7. For some animation effect, I added Stylesheet, use it for user friendly.
  8. If you are a VB coder, use the VB sample, otherwise use the C# sample.
  9. Run the program and write in Text Box a word that contains 2 characters or more such as da, and you will see a list of words that are similar to your character.


Feel free to leave any feedback on this article; it is a pleasure to see your comments and vote about this code. If you have any questions, please do not hesitate to ask me here.


This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)


About the Author

Mahsa Hassankashi
Software Developer
Iran (Islamic Republic of) Iran (Islamic Republic of)
I have been working with different technologies and data more than 10 years.
I`d like to challenge with complex problem, then make it easy for using everyone. This is the best joy.

ICT Master in Norway 2013
Work as Data Scientist in Germany - Berlin ( currently )
Diamond is nothing except the pieces of the coal which have continued their activities finally they have become Diamond.

*Article of The Community Spotlight in Microsoft ASP.NET, Wednesday, February 11, 2015,
*Article of The Day in Microsoft ASP.NET Tuesday, February 3, 2015,
*1 Jan 2015: CodeProject MVP 2015
*22 Mar 2014: Best Web Dev Article of February 2014 - Second Prize

You may also be interested in...

Comments and Discussions

AnswerRe: AutoComplete in DetailsView? Pin
Angelus1920-Mar-13 23:15
memberAngelus1920-Mar-13 23:15 
GeneralMy vote of 5 Pin
venkatesun6-Mar-13 23:43
membervenkatesun6-Mar-13 23:43 
GeneralRe: My vote of 5 Pin
Mahsa Hassankashi7-Mar-13 9:58
memberMahsa Hassankashi7-Mar-13 9:58 
QuestionHow to set the first matching value in the textbox by default Pin
Exelioindia4-Mar-13 21:33
memberExelioindia4-Mar-13 21:33 
QuestionAdd extra parameter Pin
PradnyaPatil28-Feb-13 3:16
memberPradnyaPatil28-Feb-13 3:16 
GeneralRe: Add extra parameter Pin
Mahsa Hassankashi28-Feb-13 23:29
memberMahsa Hassankashi28-Feb-13 23:29 
Questionautocomplete is not working Pin
buds6814-Feb-13 22:34
memberbuds6814-Feb-13 22:34 
GeneralGood Code Pin
AshishChaudha26-Dec-12 17:44
memberAshishChaudha26-Dec-12 17:44 
GeneralMy vote of 5 Pin
tanweer akhtar23-Dec-12 17:44
membertanweer akhtar23-Dec-12 17:44 
QuestionWhy WebService Pin
Daniel Guimarães Scatigno27-Nov-12 7:24
memberDaniel Guimarães Scatigno27-Nov-12 7:24 
GeneralMy vote of 5 Pin
elppai7-Nov-12 22:07
memberelppai7-Nov-12 22:07 
QuestionPerfect to search for student name, but student number is needed Pin
ThomasDyck8-Oct-12 15:16
memberThomasDyck8-Oct-12 15:16 
QuestionProblem in run time Pin
shanmugam_19812-Oct-12 23:25
membershanmugam_19812-Oct-12 23:25 
QuestionDoubts Pin
hanx.xcb19-Sep-12 3:44
memberhanx.xcb19-Sep-12 3:44 
Hello, a question, you can use more than one autocomplete on a single page, if so, will have some idea of how it's done?
Question. Pin
Mai2shi17-Sep-12 22:32
memberMai2shi17-Sep-12 22:32 
GeneralMy vote of 4 Pin
Morteza M6-Sep-12 23:42
groupMorteza M6-Sep-12 23:42 
GeneralRe: My vote of 4 Pin
Mahsa Hassankashi5-Nov-14 12:40
memberMahsa Hassankashi5-Nov-14 12:40 
GeneralMy vote of 5 Pin
Deepak Joy Jose4-Sep-12 0:25
memberDeepak Joy Jose4-Sep-12 0:25 
GeneralRe: My vote of 5 Pin
Mahsa Hassankashi5-Nov-14 12:41
memberMahsa Hassankashi5-Nov-14 12:41 
GeneralMy vote of 4 Pin
AHimour6-Aug-12 0:58
memberAHimour6-Aug-12 0:58 
QuestionAdding a picture Pin
Member 917645722-Jul-12 19:40
memberMember 917645722-Jul-12 19:40 
GeneralMy vote of 5 Pin
mehrdad12614-Jul-12 21:06
membermehrdad12614-Jul-12 21:06 
GeneralRe: My vote of 5 Pin
Mahsa Hassankashi5-Jul-12 2:46
memberMahsa Hassankashi5-Jul-12 2:46 
Questionthanks Pin
felixntny26-Jun-12 3:29
memberfelixntny26-Jun-12 3:29 
AnswerRe: thanks Pin
Mahsa Hassankashi26-Jun-12 3:52
memberMahsa Hassankashi26-Jun-12 3:52 
QuestionAmazing work! Pin
Ammar_Ahmad10-Jun-12 5:47
memberAmmar_Ahmad10-Jun-12 5:47 
AnswerRe: Amazing work! Pin
Mahsa Hassankashi26-Jun-12 3:51
memberMahsa Hassankashi26-Jun-12 3:51 
QuestionExcellent Pin
thomaspxavier6-Jun-12 2:14
memberthomaspxavier6-Jun-12 2:14 
AnswerRe: Excellent Pin
Mahsa Hassankashi6-Jun-12 13:05
memberMahsa Hassankashi6-Jun-12 13:05 
GeneralThanks Pin
jaffrey1104-Jun-12 0:20
memberjaffrey1104-Jun-12 0:20 
GeneralRe: Thanks Pin
Mahsa Hassankashi4-Jun-12 10:31
memberMahsa Hassankashi4-Jun-12 10:31 
GeneralMy vote of 4 Pin
shamjid30-May-12 22:49
membershamjid30-May-12 22:49 
QuestionProblem in finding Table[0] Pin
creativemukulsharma28-May-12 1:39
membercreativemukulsharma28-May-12 1:39 
AnswerRe: Problem in finding Table[0] Pin
Mahsa Hassankashi28-May-12 4:51
memberMahsa Hassankashi28-May-12 4:51 
GeneralMy vote of 5 Pin
kireinochan26-May-12 22:04
memberkireinochan26-May-12 22:04 
GeneralMy vote of 5 Pin
sravani.v20-May-12 18:13
membersravani.v20-May-12 18:13 
GeneralRe: My vote of 5 Pin
Mahsa Hassankashi22-May-12 12:13
memberMahsa Hassankashi22-May-12 12:13 
Generalthanks Pin
kvprasad16-May-12 7:05
memberkvprasad16-May-12 7:05 
GeneralRe: thanks Pin
Mahsa Hassankashi18-May-12 4:41
memberMahsa Hassankashi18-May-12 4:41 
QuestionPlz help me to solve this problem Pin
leeya12310-May-12 20:01
memberleeya12310-May-12 20:01 
AnswerRe: Plz help me to solve this problem Pin
Mahsa Hassankashi18-May-12 4:48
memberMahsa Hassankashi18-May-12 4:48 
AnswerRe: Plz help me to solve this problem Pin
mahesh_prajapati198821-Sep-12 0:09
membermahesh_prajapati198821-Sep-12 0:09 
AnswerRe: Plz help me to solve this problem Pin
malokm28-Sep-12 3:10
membermalokm28-Sep-12 3:10 
Questionhaving problem on App_code .vb Pin
deinos_mania8725-Apr-12 9:54
memberdeinos_mania8725-Apr-12 9:54 
AnswerRe: having problem on App_code .vb Pin
Mahsa Hassankashi25-Apr-12 10:54
memberMahsa Hassankashi25-Apr-12 10:54 
Questionneed C# code for folliwing mess Pin
Santhosh_KS17-Apr-12 20:15
memberSanthosh_KS17-Apr-12 20:15 
QuestionAutocompleteextender Pin
chhitiz1229-Mar-12 1:04
memberchhitiz1229-Mar-12 1:04 
AnswerRe: Autocompleteextender Pin
Mahsa Hassankashi29-Mar-12 3:04
memberMahsa Hassankashi29-Mar-12 3:04 
GeneralRe: Autocompleteextender Pin
chhitiz1210-Apr-12 21:20
memberchhitiz1210-Apr-12 21:20 
AnswerRe: Autocompleteextender Pin
sau736-Apr-12 2:24
membersau736-Apr-12 2:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 8 Jul 2011
Article Copyright 2011 by Mahsa Hassankashi
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid