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 

AutoCompleteExtender control example of AJAX in asp.net using web service
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:

AutoCompleteExtender control example of AJAX in asp.net

  • 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 ToolScriptManager from the AjaxControlToolkit toolbox and Place a TextBox control. Also drag AutoCompleteExtender control form Ajax toolkit onto the design page(.aspx)
Source Code:

<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"></asp:ToolkitScriptManager>
       <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.
Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned for more technical updates."
Previous
Next Post »

11 comments

Click here for comments
Anonymous
admin
August 21, 2013 ×

Unknown server tag 'asp:AutoCompleteExtender'.

Reply
avatar
August 21, 2013 ×

have you installed the ajaxtoolkit? and if yes then have you added the line
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

I suggest you to recheck the code thoroughly and if still you face problem then let me know. I will help you resolve it.

Reply
avatar
Unknown
admin
September 23, 2013 ×

hi sir your site very useful for me

Reply
avatar
Anonymous
admin
September 23, 2013 ×

It doesn't work at all.

Reply
avatar
September 23, 2013 ×

Thanks Nagendra Babu..i am glad my website helped you..keep reading :)

Reply
avatar
September 23, 2013 ×

Hello sunil..its completely working..can you please describe the problem you are facing?

Reply
avatar
Unknown
admin
January 30, 2014 ×

it doesnt work to me. could you help me?

Reply
avatar
January 30, 2014 ×

Hello Christian Yonathan..what problem you are facing??

Reply
avatar
Anonymous
admin
June 26, 2014 ×

Be sure to define your C# LIST method with STATIC (ex.: public static List...). Spent way to much time trying to figure out why it wasn't working so I thought I would share.

Reply
avatar
Anonymous
admin
June 11, 2015 ×

Thank you for this great article, got it to work. Any suggestions on how to handle an instance where my lookup result should also return the looked up record ID ? Similar to how we would use a drop down list control with a Data Text and Data Value field ?

Respectfully yours

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..