Click here to Skip to main content
15,868,141 members
Articles / Web Development / HTML
Tip/Trick

Creating a Database Driven Auto Complete TextBox Using jQuery for ASP.NET Websites

Rate me:
Please Sign up or sign in to vote.
3.83/5 (12 votes)
27 Jan 2013CPOL3 min read 94.6K   8.4K   15   11
This tip talks about creating an auto complete textbox using jQuery.

Introduction

This tip talks about creating an auto complete textbox using jQuery. The data will be fetched from the server side and pushed to the client side and will be displayed to the user as auto suggest. Previously, I had written a tip for AutoCompleteList using AJAX control toolkit. Now I tried AutoCompleteList using jQuery.

Background

There are many scenarios for using AutoCompleteList through which suggestions are displayed on keyup event in Textbox. This tip describes how AutoCompleteList works using jQuery provided in ASP.NET. This tip is written from a beginner's point of view.

Using the Code

Let us see how this small application works by looking at a small example. We will try to implement a small project and will incorporate the auto complete functionality in that.

Let us start with the database design for the sample application. We are creating a simple database with single table named tblCountry. It consists of the following two columns:

  1. CountryID which is an identity field
  2. CountryName which is nvarchar(50) datatype

Image 1

Let us fill this database with some sample data for testing purposes.

Image 2

Now we have a small database ready. Let us now see how the server side code will extract the data from this. We have a function named BindName() in which the details filled in a database table can be simply viewed. Then we will declare output object of StringBuilder class in which we will try to append the CountryName in a comma separated list as ("item1","item2") and then finally we will push it to client side so that it can be used via jQuery/JavaScript.

C#
private string BindName()
{
    DataTable dt = null;
    using (conn = new SqlConnection
                  (ConfigurationManager.ConnectionStrings["tempdbConn"].ConnectionString))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select CountryName from tblCountry";
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                dt = new DataTable();
                da.Fill(dt);
            }
        }
    }
   
    StringBuilder output = new StringBuilder();
    output.Append("[");
    for (int i = 0; i < dt.Rows.Count; ++i)
    {
        output.Append("\"" + dt.Rows[i]["CountryName"].ToString() + "\"");

        if (i != (dt.Rows.Count - 1))
        {
            output.Append(",");
        }
    }
    output.Append("];");

    return output.ToString();
}

Now in our PageLoad event, we will call the BindName function and assign the values to publicly defined string Datatype listFilter. This listFilter variable is defined publicly (as a class member variable) because we will use it in an .aspx page (i.e. markup) so that it can be accessible from client side scripts.

C#
public string listFilter = null;

protected void Page_Load(object sender, EventArgs e)
{
    listFilter = BindName();
}

Now let us create a function named LoadList() on client side which will access this data and proceed further.

JavaScript
function LoadList()
{        
    var ds=null;
    ds = <%=listFilter %>

To call the loadlist function, the body tag is the perfect place so that we can be rest assured that this function will be called when the body of the HTML page is getting loaded.

JavaScript
onload="LoadList()

Now we have the basic skeleton in place. Now when we run the application, the serverside code will fetch the data and will put it on client side so that it can be used from the client side. The body of HTML when loaded will associate this data with the auto complete functionality.

Image 3

The user will then be able to see the suggestions whenever he types in the associated textbox.

Image 4

And we have an auto complete text box ready. The only catch in using this approach is that all the data from the table is being written on client side before it is being rendered. This is unlike the webservice approach where we can get the filtered data using the LIKE operator and process them at client side. But this approach could be useful when we have small amount to data in auto complete suggestions and writing a web service and having the extender controls seems like an overkill.

Points of Interest

This tip describes how AutoCompleteList works using Jquery provided in ASP.NET. This tip is written from a beginner's point of view. I tried to keep it simple so that auto suggest feature needed with small data can be put in place without having to create web services. This is one of the approaches. I will try to incorporate more features in this small application and to enhance further with other methods of doing the same thing.

History

  • 24th January 2013: First version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
I am a Software Engineer from Bhopal. I started my Career from Programming in ASP and now working as a Web Developer in ASP.Net (C#). I Love coding and always ready to gain new thing and always been towards Microsoft Technologies. Apart from coding my other hobbies are traveling, Internet Surfing, spending time with family and hang out with friends.

http://www.webtekspace.blogspot.in/

Comments and Discussions

 
GeneralComma separated multiple words Pin
Member 1085816214-Nov-15 17:41
Member 1085816214-Nov-15 17:41 
Questionabout autosearch in asp.net Pin
Member 114930142-Mar-15 15:59
Member 114930142-Mar-15 15:59 
QuestionLoadList not found error Pin
Member 1033708724-Feb-15 21:08
Member 1033708724-Feb-15 21:08 
Generalgood Pin
upendra shahi15-Jul-14 21:17
upendra shahi15-Jul-14 21:17 
GeneralMy vote of 1 Pin
Nirav Prabtani14-Jun-14 2:15
professionalNirav Prabtani14-Jun-14 2:15 
client side code missing.
QuestionImage with text in auto complete textbox(Like, Selecting the location with the country img) Pin
vinu3mn16-Mar-14 19:13
vinu3mn16-Mar-14 19:13 
QuestionQuery Pin
SatishBandre18-Feb-14 9:41
SatishBandre18-Feb-14 9:41 
GeneralMy vote of 1 Pin
AbdullaMohammad25-Oct-13 9:15
AbdullaMohammad25-Oct-13 9:15 
QuestionMessage Closed Pin
3-May-13 20:10
Azad R Chouhan3-May-13 20:10 
AnswerRe: how to do it in fast way Pin
AshishChaudha3-May-13 20:19
AshishChaudha3-May-13 20:19 
QuestionGot a problem Pin
Member 990726013-Mar-13 1:13
Member 990726013-Mar-13 1:13 

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.