{infiniteZest}
// Articles. Tutorials. Utilities.
Home  |   Search  |   Login  
Categories Skip Navigation Links
New / All
AJAX
Apple
ASP.NET
.NET
Git
Google / Android
Python / IronPython
Miscellaneous
SQL Server
Using AutoCompleteExtender with results from database
Summary
Google Suggest style autocomplete feature is readily available from the AutoCompleteExtender control from the AJAX Toolkit. This article shows how to use control with the data obtained from database.
 
Table of Contents

Required pieces for AutoCompleteExtender

1. Existence of ScriptManager

2. Add the Search box

3. Add the AutoCompleteExtender

4. WebService for getting the results

Code Discussion

Additional Related Articles

 

Required pieces for AutoCompleteExtender

For the AutoCompleteExtender to work, you need the following:

  1. The MS AJAX already installed. You will drag-drop the ScriptManager from AJAX Extensions.
  2. A TextBox (or a Label) that serves as the primary control under which the auto suggest results are shown
  3. The AJAX Control Toolkit which contains AutoCompleteExtender control.
  4. A Web Service from which the auto suggest results are obtained by the AutoCompleteExtender

Here we will see the example code for all the above pieces, but the most important piece is the code from the web service that gets data from the database.

1. Existence of ScriptManager

Make sure you have added the ScriptManager to the page once and only once. Meaning, if there is a ScriptManager on the Master Page, you don’t need to (and can’t) add ScriptManager again to the child page. You will have the following simple lines in the page code:

<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>

2. Add the Search box

Add a TextBox that would work as a search box (or some kind of field box) under which the list of suggestions will be shown. This is the standard text box that you will format the way you want. Programmatically, you will be able to get the value of the text entered into this box in the regular fashion (i.e. from the .Text property)

<asp:TextBox ID="txtSuggest" runat="server" Width="140px" Font-Names="Verdana" Font-Size="10pt">
</asp:TextBox>

3. Add the AutoCompleteExtender

Now just drag and drop the the AutoCompleteExtender from the Toolbox in the AJAX Toolkit section. Apart from the ID for this control, you need to set one other important property for this control:

  • TargetControlID

In the dropdown list associated with this property, you will see all the controls that are already on your page that can be extended. For the AutoCompleteExtender, you will see the listing of all the labels and textboxes. Choose the suggest textbox described above.

Now a fascinating thing happens, if you see the properties of the txtSuggest textbox, you will see a new section named ’Extenders’ added, and under this, you will see another section for the AutoCompleteExtender you have just linked to. A few important properties are discussed below:

  • CompletionInterval: The default value of 1000 means 1sec.
  • CompletionSetCount: Default is 10 (the number of items shown in the suggest box). This should probably be no more than 20.
  • MinimumPrefixLength: Default is 3. How many characters do you want the user type into the textbox before suggesting some values. I think anywhere from 1 to 4 are reasonable values.

Now the two most important properties -- where to get the values from -- are discussed in the next section.

4. WebService for getting the results

The web service and a method inside this service which returns the results for auto suggest are set via the following two properties:

  • ServicePath: This is the name of the asmx file; For example, SearchCusts.asmx.
  • ServiceMethod: A method inside the above web service that returns the auto suggest results; For example, GetList.

You create this web service just like a regular web service -- Right Click on the solution, choose Add New Item, and click on the WebService template.

If you named this web service SearchCusts, you will see a file called SearchCusts.asmx created. Your code behind file will be created in the App_Code directory. Default name for this web service would be SearchCusts.cs (for the C# project).

Your data access method could be something like this:

/// <summary>
/// Returns a list of suggestions.
/// </summary>
/// <param name="prefixText">The suggestions will have this as prefix</param>
/// <param name="count">Number of suggestions</param>
/// <returns>A string array of suggestions</returns>

[WebMethod]
public string[] GetCustList(string prefixText, int count)
{
    string connString = ConfigurationManager.ConnectionStrings["YourDB"].ToString();

    string selectString = "SELECT DISTINCT TOP " + count.ToString() + " Cust from Custs WHERE Cust LIKE ’" + prefixText + "%’";

    List<String> CustList = new List<string>(count);
    using (SqlConnection sqlConn = new SqlConnection(connString))
    {
        sqlConn.Open();

        using (SqlCommand sqlCmd = new SqlCommand(selectString, sqlConn))
        {
            SqlDataReader reader = sqlCmd.ExecuteReader();
            while (reader.Read())
               CustList.Add(reader["Cust"].ToString());
        }
    }
    return (CustList.ToArray());
}  

Code Discussion

Inside the web service, you will have a public webmethod with parameters for passing prefix text and the number of suggestion to be returned.

[WebMethod]
public string[] GetCustList(string prefixText, int count)

In the SELECT query that is to be applied to the database, you will be applying both the parameters. You will be getting the "TOP count" (for example: TOP 10 or TOP 15, etc.) for the number of records. You will be looking for records that start with the prefix: LIKE prefixText% (for example: LIKE a% or LIKE andy% etc.).

Depending on the need, you can also do %prefixText%, so the search for that word anywhere in the suggestion:

string selectString = "SELECT DISTINCT TOP " + count.ToString() + " Cust from Custs WHERE Cust LIKE ’" + prefixText + "%’";

Now you need to make up a array of suggestion list and pass it on to the AutoCompleteExtender:

    List<String> CustList = new List<string>(count);
    using (SqlConnection sqlConn = new SqlConnection(connString))
    {
        sqlConn.Open();

        using (SqlCommand sqlCmd = new SqlCommand(selectString, sqlConn))
        {
            SqlDataReader reader = sqlCmd.ExecuteReader();

            while (reader.Read())
                CustList.Add(reader["Cust"].ToString());
        }
    }

    return (CustList.ToArray());

On the front-end, the AutoCompletExtender looks like this (with all the parameters added):

<cc1:AutoCompleteExtender ID="aceSearch"
    runat="server"
    MinimumPrefixLength="1"
    ServiceMethod="GetCustList"
    ServicePath="SearchCusts.asmx"
    TargetControlID="txtSearch">
</cc1:AutoCompleteExtender>

Additional Related Articles

How does an MS AJAX extender control get initialized on the client side?
(Analysis of AutoCompleteExtender placement on the page at runtime)

The structure and usage of AutoCompleteExtender control

Incorporating ASP.NET AJAX into existing Custom Controls and JavaScript files

Browser Detection from the Client-Side in ASP.NET AJAX

Bookmark and Share This

More Articles With Similar Tags
icon-autocompleteextender.jpg
This article looks at various aspects of autocompleteextender. The web service method that returns the matching results and various important properties of the control are also discussed.
icon-ajaxservercontrol-new-project.jpg
This article explains the various pieces of the code generated for beginning an AJAX Server Control. The class ScriptControl and the over-ridden methods GetScriptDescriptors() and GetScriptReferences() are explained, among others.
icon-ajaxservercontrol-toolbox.jpg
This article talks about using an AJAX Server Control from a web site. Registering the control and adding a ScriptManager are also discussed.
This test has questions about using an AJAX Server Control from a web site. Registering the control and adding a ScriptManager are also included.
This is a test on the various pieces of the code generated for an AJAX Server Control. This includes the class ScriptControl and the over-ridden methods GetScriptDescriptors() and GetScriptReferences().
About  Contact  Privacy Policy  Site Map