AJAX AutoCompleteExtender to Create Autocomplete TextBox Example in ASP.Net C# VB.Net

As you knew that Microsoft community published a series of controls named ASP.NET AJAX Control Toolkit. In my previous post i have explained how you can Install ASP.NET AJAX Control Toolkit. You can use this AutoCompleteExtender in your page to make an autocomplete textbox within just few minutes. You can’t imagine how much it’s easy. In this AJAX tutorial i will show you how one can create Autocomplete TextBox using AJAX in ASP.NET C# VB.Net. The Autocomplete TextBox provides user a nice & cool experience while entering data. So you can incorporate autocomplete facilty to give your user best UI experience.

Desired Output:
Auto Complete Extender

To do the Example First Create a Database Table:

CREATE TABLE [dbo].[tblStock](
    [BrandName] [varchar](max) NULL,
    [CategoryName] [varchar](max) NULL,
    [ProductName] [varchar](max) NULL,
    [LogicalQuantity] [bigint] NULL,
    [PhysicalQuantity] [bigint] NULL,
    [QuarentineQuantity] [bigint] NULL
) ON [PRIMARY]

Now Insert Some Data:

INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Handwash',55781,55780,1)
INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Soap',38786,38780,6)
INSERT INTO tblStock VALUES('Kohinoor','ToothPaste','Tibbet Clear',34503,34500,3)
INSERT INTO tblStock VALUES('Proctor & Gamble','Bubble','',43809,43800,9)
INSERT INTO tblStock VALUES('Telenor','Mobile','Data SIM',43876,43870,6)
INSERT INTO tblStock VALUES('Telenor','Mobile','GP Modem',23907,23900,7)
INSERT INTO tblStock VALUES('Uniliver','Soap','Lux',19806,19800,6)
INSERT INTO tblStock VALUES('Uniliver','TeleProducts','Brittle',20703,20700,3)
INSERT INTO tblStock VALUES('Uniliver','ToothPaste','Close Up',16755,16750,5)

Now modify the Web.Config File to Connect to Database:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now prepare the HTML Markup:

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>

    <asp:Label runat="server" ID="lblReferrerName" Text="Referrer: "></asp:Label>
    <asp:TextBox ID="txtName" runat="server"></asp:TextBox>

    <asp:AutoCompleteExtender ID="txtName_AutoCompleteExtender" runat="server" 
        DelimiterCharacters="" Enabled="True" ServiceMethod="GetCompletionList" 
        ServicePath="" TargetControlID="txtName" UseContextKey="True" MinimumPrefixLength="2" CompletionInterval="10" EnableCaching="true" CompletionSetCount="3">
    </asp:AutoCompleteExtender>
</asp:Content>

Note: First drag & drop a ToolkitScriptManager not ScriptManager. After that drag & drop AutoCompleteExtender. Set TargetControlID=TextBoxID for which you want to implement autocomplete. After that set up the other properties like MinimumPrefixLength, CompletionInterval, CompletionSetCount.

Don’t confuse about ServiceMethod=”GetCompletionList” line from the above code. I will show you how you can create webservice method for Autocomplete Extender. Move your mouse on the TextBox. Then from TextBox control smart tag, select the Add AutoComplete page method option from the provided menu, shown in below:

TextBoxSmartTag

After that you will found that a webservice method will be added in your default.aspx.cs page named GetCompletionList just after the Page_Load Event. Now you need to modify this method to get your expected set of data. Now look at my code from below:

C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    [System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
    public static string[] GetCompletionList(string prefixText, int count, string contextKey)
    {
       string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        SqlConnection conn = new SqlConnection(connectionString);
        // Try to use parameterized inline query/sp to protect sql injection
        SqlCommand cmd = new SqlCommand("SELECT TOP " + count + " ProductName FROM tblStock WHERE ProductName LIKE '" + prefixText + "%'", conn);
        SqlDataReader oReader;
        conn.Open();
        List<string> CompletionSet = new List<string>();
        oReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        while (oReader.Read())
            CompletionSet.Add(oReader["ProductName"].ToString());
        return CompletionSet.ToArray();
    }

Note: Don’t forget to add “System.Collections.Generic;”, “System.Configuration”, “System.Data.SqlClient”, “System.Data” namespaces.

VB.Net Code:

    Public Shared Function GetCompletionList(prefixText As String, count As Integer, contextKey As String) As String()
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Dim conn As New SqlConnection(connectionString)
        ' Try to use parameterized inline query/sp to protect sql injection
        Dim cmd As New SqlCommand("SELECT TOP " & count & " ProductName FROM tblStock WHERE ProductName LIKE '" & prefixText & "%'", conn)
        Dim oReader As SqlDataReader
        conn.Open()
        Dim CompletionSet As New List(Of String)()
        oReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        While oReader.Read()
            CompletionSet.Add(oReader("ProductName").ToString())
        End While
        Return CompletionSet.ToArray()
    End Function

Note: Don’t forget to add “System.Collections.Generic;”, “System.Configuration”, “System.Data.SqlClient”, “System.Data” namespaces.

Code Explanation:
If you observe the web method GetCompletionList you will found three parameters named prefixText, count, contextKey. Which you can use to enhance your method. Here i have used first two parameters. The prefixText parameter will give you the text that user enter into the textbox. So you can use prefixText parameter in your sql as LIKE clause to match data. The second one count which is used to return no of data. If you specify CompletionSetCount=”3″ then you will get maximum 3 data. So set the count as per client requirement.

Hope everything is clear now. And now you can create AJAX AutoComplete TextBox in your project smoothly. If still you have any understanding gap please download the full source code from below:

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Ajax, Asp.net, C#, Database, SQL Server, VB.Net
7 comments on “AJAX AutoCompleteExtender to Create Autocomplete TextBox Example in ASP.Net C# VB.Net
  1. Rutvij Thakar says:

    thanks, this is great example but i need solution for textchaged event, how its work on textchanged ? Example: I Search ‘Delhi’ based on that my next textbox will fill ‘Delhi’s’ District do some needful

    • Cathy Emmons says:

      I am trying to add a datalist control in the main content control. But it is not showing up in the browser. Any suggestions.

  2. Nigel says:

    Do you mind if I quote a couple of your articles as long as I provide credit and sources back how to cancel google plus account your weblog? My blog site is in the very same niche as yours and my users would definitely benefit from some of the information you present here. Please let me know if this ok with you. Thanks!

  3. Robert says:

    Really nice and informative thanks for sharing this. Web Development Company

  4. Cathy Emmons says:

    I am trying to add a datalist control in the main content control. But it is not showing up in the browser. Any suggestions.

  5. SRINIVASULU says:

    thanks, this is great example but i need solution for textchaged event, how its work on textchanged ? Example: I Search ‘iNDIA’ based on that my next textbox will fill ‘iNDIA’s’ STATES do some needful

  6. Ruhul says:

    I am using ADO.entity model and your code do not act . Please help me

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*