Click here to Skip to main content
Click here to Skip to main content

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

, 27 Jan 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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

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.

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.

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.

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.

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.

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

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)

Share

About the Author

AshishChaudha
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/
Follow on   Twitter   Google+

Comments and Discussions

 
Generalgood PinmemberMember 1065743915-Jul-14 22:17 
GeneralMy vote of 1 PinprofessionalNirav Prabtani14-Jun-14 3:15 
QuestionImage with text in auto complete textbox(Like, Selecting the location with the country img) Pinmembervinu3mn16-Mar-14 20:13 
QuestionQuery PinmemberSatishBandre18-Feb-14 10:41 
GeneralMy vote of 1 Pinmembersamthec25-Oct-13 10:15 
QuestionMessage Removed PinmemberAzad R Chouhan3-May-13 21:10 
Message Removed
AnswerRe: how to do it in fast way PinprofessionalAshishChaudha3-May-13 21:19 
QuestionGot a problem PinmemberMember 990726013-Mar-13 2:13 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 28 Jan 2013
Article Copyright 2013 by AshishChaudha
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid