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>


AutoComplete Extender In GridView Asp.Net


Download Sample Code


If you like this post than join us or share

16 comments:

Anonymous said...

file not found in dowload link


Anonymous said...

i cannot found the download link


gomathi said...

Based on Textbox autocompleteExtender how Gridview Rows are Changed


Anonymous said...

i want autocomplete textbox in java script and code behind is c#.net please help me in this task and reply me my mail samba.revuru@gmail.com


Anonymous said...

I want vb code for this Ajax autocomplete extender textbox in EditItemTemaplate of GridView


Anonymous said...

Unknown server tag 'ajaxToolkit:autocompleteextender'.


ssss said...

sssssssssssssssssssssss


Being a DEVELOPER said...

sir
plz tell me why use a count parameter in GetCompletionList Method

and send the answer my mail address
nitendragodare@gmail.com


Amit said...

this is a very nice article,



I want to apply autocomplete in gridview (in asp.net3.5 with C#) using jquery and this gridview is within the ajax update panel
For this i am using method within the ItemTemplate

$(document).ready(function(){
$('[id$=txtItemCode]').autocomplete("SearchItem.aspx").result(function (event, data, formatted) {
if (data) {
alert('Value: '+ data[1]);

}
else {
$('[id$=txtItemID]').val('-1');
}
});
});

since in gridview the textbox id is TextBox id="txtItemCode" but

on browser such as type="textbox" id="ctl00_otherName_101txtItemCode"
and then for the next row same textbox id is now
input type="textbox" id="ctl00_otherName_102txtItemCode" 

i tried
1. put this code within the ItemTemplate
2. %= txtItemCode.ClientID %
3. $("*[@id$=theGridId] input[@id$=txtItemCode]")
4. jQuery.expr[':'].asp = function(elem, i, match) {
return (elem.id && elem.id.match(match[3] + "$"));
};
and $(":asp(txtItemCode)").autocomplete...
5.$('.txtItemCode').autocomplete("LookupCodes.aspx?type=IC", { mustMatch: true })

but autocomplete does not work in all the cases

plz help me.


ecommerce developers said...

If we install visual studio 2010 version in any PC, can easily code this one because every thing is drag and drop no need to code much,..


Anonymous said...

file not fount


Anonymous said...

download fail....


All about personal finance said...

can u pls mail tz code to jacksmithandu@gmail.com


Unknown said...

@MITHUN M: you can download source code from the download link provided in the article


Akshara said...

hello,
your code is amazing.
its work in c#,but when i have tried to convert it into vb,its not working.
can u plz send me this code in vb.net.
my emailid id aksharas9@gmail.com


Akshara said...

thank u sir,i got ur mail.its working.


Find More Articles