Ajax AutoCompleteExtender in GridView

In this example i am implementing Ajax AutoCompleteExtender TextBox in EditItemTemaplate of GridView using AjaxControlToolkit In Asp.Net, for this we need to create a web service which calls the method to fetch data from database and display results as suggestions for textbox

We can also create AutoComplete TextBox In Winforms Windows Forms Application.

Add a new webservice to the project and name it AutoComplete.asmx or whatever you want, in the code behind of the web service we write methods to get records from database and a web method called GetCompletionList which takes text entered in textbox as parameter to search database , this method is automatically called when ever user types anything in the textbox, following is the code for web service.

C# CODE

---

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class AutoComplete : System.Web.Services.WebService {

    public AutoComplete () 
    {
             
    }

    [WebMethod]
    public string[] GetCompletionList(string prefixText, int count)
    {
        if (count == 0)
        {
            count = 10;
        }
        DataTable dt = GetRecords(prefixText);
        List items = new List(count);

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string strName = dt.Rows[i][0].ToString();
            items.Add(strName);
        }
        return items.ToArray();
    }

    public DataTable GetRecords(string strName)
    {
        string strConn = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(strConn);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddWithValue("@Name", strName);
        cmd.CommandText = "Select Name from Test where Name like '%'+@Name+'%'";
        DataSet objDs = new DataSet();
        SqlDataAdapter dAdapter = new SqlDataAdapter();
        dAdapter.SelectCommand = cmd;
        con.Open();
        dAdapter.Fill(objDs);
        con.Close();
        return objDs.Tables[0];
   }
}

VB.NET

---

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration


 _
 _
 _
 _
Public Class AutoComplete
    Inherits System.Web.Services.WebService

     _
    Public Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer) As String()
        If count = 0 Then
            count = 10
        End If
        Dim dt As DataTable = GetRecords(prefixText)
        Dim items As New List(Of String)(count)

        For i As Integer = 0 To dt.Rows.Count - 1
            Dim strName As String = dt.Rows(i)(0).ToString()
            items.Add(strName)
        Next
        Return items.ToArray()
    End Function

    Public Function GetRecords(ByVal strName As String) As DataTable
        Dim strConn As String = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString
        Dim con As New SqlConnection(strConn)
        Dim cmd As New SqlCommand()
        cmd.Connection = con
        cmd.CommandType = System.Data.CommandType.Text
        cmd.Parameters.AddWithValue("@Name", strName)
        cmd.CommandText = "Select Name from Test where Name like '%'+@Name+'%'"
        Dim objDs As New DataSet()
        Dim dAdapter As New SqlDataAdapter()
        dAdapter.SelectCommand = cmd
        con.Open()
        dAdapter.Fill(objDs)
        con.Close()
        Return objDs.Tables(0)

    End Function

End Class

In html source of aspx page add a ToolkitScriptManager and add ServiceReference and path to asmx file inside it, in gridview add autocomplete extender for the textbox which we want to show auto suggestion.

---

   1:  <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">

   2:  <Services>

   3:       <asp:ServiceReference Path="AutoComplete.asmx"/>

   4:  </Services>

   5:  </asp:ToolkitScriptManager>

   6:   

   7:  <asp:UpdatePanel ID="UpdatePanel1" runat="server">

   8:  <ContentTemplate>

   9:   

  10:  <asp:GridView ID="GridView1" runat="server"

  11:                AutoGenerateColumns="False"

  12:                DataSourceID="SqlDataSource1">

  13:  <Columns>

  14:  <asp:CommandField ShowEditButton="True"/>

  15:  <asp:TemplateField HeaderText="ID" SortExpression="ID">

  16:  <ItemTemplate>

  17:  <asp:Label ID="lblID" runat="server" Text='<%#Eval("ID") %>'/>

  18:  </ItemTemplate>

  19:   

  20:  <EditItemTemplate>

  21:  <asp:Label ID="lblID" runat="server" Text='<%#Bind("ID") %>'/>

  22:  </EditItemTemplate>

  23:  </asp:TemplateField>

  24:   

  25:  <asp:TemplateField HeaderText="Name" SortExpression="Name">

  26:  <ItemTemplate>

  27:  <asp:Label ID = "lblName" runat="server" Text='<%#Eval("Name")%>'/>

  28:  </ItemTemplate>

  29:  <EditItemTemplate>

  30:  <asp:TextBox ID="txtName" runat="server" Text='<%#Bind("Name")%>'/>

  31:   

  32:  <asp:AutoCompleteExtender runat="server" 

  33:                            ID="autoComplete1" 

  34:                            TargetControlID="txtName"

  35:                            ServicePath="AutoComplete.asmx" 

  36:                            ServiceMethod="GetCompletionList"

  37:                            MinimumPrefixLength="1" 

  38:                            CompletionInterval="10"

  39:                            EnableCaching="true"

  40:                            CompletionSetCount="12" />

  41:  </EditItemTemplate>

  42:  </asp:TemplateField>

  43:   

  44:  </Columns>

  45:  </asp:GridView>

  46:  </ContentTemplate>

  47:  </asp:UpdatePanel>

  48:   

  49:  <asp:SqlDataSource ID="SqlDataSource1" runat="server"

  50:  ConnectionString="<%$ConnectionStrings:DatabaseConnectionString%>"

  51:  SelectCommand="SELECT [ID], [Name] FROM [Test]"

  52:  UpdateCommand="Update Test set [Name] = @Name where ID = @ID">

  53:  <UpdateParameters>

  54:  <asp:Parameter Name="Name" />

  55:  <asp:Parameter Name="ID" />

  56:  </UpdateParameters>

  57:  </asp:SqlDataSource>

Download Sample Code