Ajax AutoCompleteExtender control example in asp.net using C#,VB.Net without using web service

Introduction: In previous articles i explained Ajax AutoCompleteExtender control example in asp.net C#,VB.Net using web service and Ajax Accordion example to create Vertical DropDown menu and Drag & drop to upload multiple files using AjaxFileUpload like Facebook and Ajax TextBoxWatermarkExtender example and Show tool tip message using CSS and HTML and Highlight gridview row on mouse over using CSS and Bind,Save,Edit,Update,Cancel,Delete and paging example in DetailsView and How to allow only numbers, characters in the textbox using FilteredTextBoxExtender control of Ajax in asp.net" and CalendarExtendar control of Ajax in asp.net 

Description: Have you ever noticed how the related suggestions highlights as you start typing in the Google search box? This is called AutoComplete. So if you want Google type auto complete TextBox functionality then we can also implement this functionality in our asp.net web applications using AutoCompleteExtender control of AJAX. AJAX is very powerful tool for improving performance, look and feel. 

The concept is simple. When you start typing in the TextBox, AutoCompleteExtender fetches the matching strings from the database and display while typing e.g. when you type a single character ‘m’ in the TextBox then all the Qualifications starting with ‘m’ will be fetched  and displayed.

There are two ways to implement AutoComplete functionality:

1) Using Web Service
2) Using Static Method
 

In this example we will learn the second method i.e. using Static Method in the code behind file. First method i.e. using Web service was explained in the article Ajax AutoCompleteExtender control example in asp.net C#,VB.Net using web service

Implementation: Let's create an asp.net sample website to it in action.  

•  Create  a DataBase in SQL Server e.g. "MyDataBase"and create a table in that DataBase and Name it "Qualification_ Table" as shown in figure below:

• Create ConnectionString in the web.config file as :

<connectionStrings>

    <add name="conStr" connectionString="Data Source=LALIT;Initial Catalog=MyDataBase;Integrated Security=True"/>

  </connectionStrings>

Note: Replace the Data Source and the Initial Catalog as per your application.

Place a script manager from the AJAX Extensions category of visual studio tool toolbox and Place a TextBox control. Also drag AutoCompleteExtender control form Ajax toolkit onto the design page(.aspx)

• If you don’t know how to install Ajax control toolkit then read the article How to install Ajax Control Toolkit in Visual Studio? 

Source Code:

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

       <fieldset style="width:250px;">

            <legend>AutoExtender control example</legend>    

        Qualification:    <asp:TextBox ID="txtQualifications" runat="server"></asp:TextBox>

        <asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="txtQualifications" ServiceMethod="FetchQualifications" MinimumPrefixLength="1" CompletionSetCount="20" CompletionInterval="0" EnableCaching="true"></asp:AutoCompleteExtender>

            </fieldset>

Notice that on the design page(.aspx) a new directive:

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

is automatically added on dragging the AutoCompleteExtender control form Ajax toolkit in the very second line just below the <@page> directive which is always very first line of the design page(.aspx).This means Ajax is now registered to be used.

The properties of AutoCompleteExpender control that are used in this example are as follows:

TargetControlID - The TextBox control where the user types content to be automatically completed e.g. In our case Qualification TextBox.

ServiceMethod - The web service method that we created for fetching the list of Qualifications from the database. 

MinimumPrefixLength - Minimum number of characters that must be entered before getting suggestions e.g. if you type ‘a’ then matching string starting with character ‘a’ will appear.

CompletionInterval - Time in milliseconds when the timer will kick in to get suggestions using the web service. Always keep it as low as you can so that it fetches the suggestion immediately. If you set CompletionInterval  =1000 then it means it  is set to 1 second because 1000 milliseconds =1 second.

EnableCaching – It is a Boolean property to check whether client side caching is enabled or disabled.

CompletionSetCount - Number of suggestions to be retrieved from the web service.

• Now in the code behind file (.aspx.cs) create the static function to automatically Fetch Qualifications on typing in the Qualification TextBox. e.g. When you type a single character e.g. ‘m’ then all the Qualifications starting with ‘m’ will appear like suggestions.

C#.Net Code to implement auto complete on TextBox without using web service

Include the following namespaces:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections.Generic;

and then write the  code as:

[System.Web.Script.Services.ScriptMethod()]

    [System.Web.Services.WebMethod]

    public static List<string> FetchQualifications(string prefixText)

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ToString());

        con.Open();

        SqlCommand cmd = new SqlCommand("select * from Qualification_Table where Qualification like @QualName+'%'", con);

        cmd.Parameters.AddWithValue("@QualName", prefixText);

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataTable dt = new DataTable();

        da.Fill(dt);

        List<string> QualNames = new List<string>();

        for (int i = 0; i < dt.Rows.Count; i++)

        {

            QualNames.Add(dt.Rows[i][1].ToString());

        }

        return QualNames;

    }

VB.Net Code o implement auto complete on TextBox without using web service

• In the Code behind file (.aspx.vb) write the code as:

Include the following namespaces:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Imports System.Collections.Generic

Write the code as:

  <System.Web.Script.Services.ScriptMethod> _

<System.Web.Services.WebMethod> _

    Public Shared Function FetchQualifications(prefixText As String) As List(Of String)

        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ToString())

        con.Open()

        Dim cmd As New SqlCommand("select * from Qualification_Table where Qualification like @QualName+'%'", con)

        cmd.Parameters.AddWithValue("@QualName", prefixText)

        Dim da As New SqlDataAdapter(cmd)

        Dim dt As New DataTable()

        da.Fill(dt)

        Dim QualNames As New List(Of String)()

        For i As Integer = 0 To dt.Rows.Count - 1

            QualNames.Add(dt.Rows(i)(1).ToString())

        Next

        Return QualNames

    End Function

• Now run the website and check by entering single character of the qualification name that is in your Database table. It will show all the matching Qualifications  starting with entered character.