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

---

show source

01[WebService(Namespace = "http://tempuri.org/")]

02[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

03[System.Web.Script.Services.ScriptService]

04public class AutoComplete : System.Web.Services.WebService {

05 

06    public AutoComplete ()

07    {

08 

09    }

10 

11    [WebMethod]

12    public string[] GetCompletionList(string prefixText, int count)

13    {

14        if (count == 0)

15        {

16            count = 10;

17        }

18        DataTable dt = GetRecords(prefixText);

19        List<string> items = new List<string>(count);

20 

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

22        {

23            string strName = dt.Rows[i][0].ToString();

24            items.Add(strName);

25        }

26        return items.ToArray();

27    }

28 

29    public DataTable GetRecords(string strName)

30    {

31        string strConn = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

32        SqlConnection con = new SqlConnection(strConn);

33        SqlCommand cmd = new SqlCommand();

34        cmd.Connection = con;

35        cmd.CommandType = System.Data.CommandType.Text;

36        cmd.Parameters.AddWithValue("@Name", strName);

37        cmd.CommandText = "Select Name from Test where Name like '%'+@Name+'%'";

38        DataSet objDs = new DataSet();

39        SqlDataAdapter dAdapter = new SqlDataAdapter();

40        dAdapter.SelectCommand = cmd;

41        con.Open();

42        dAdapter.Fill(objDs);

43        con.Close();

44        return objDs.Tables[0];

45   }

46}</string></string>

VB.NET

---

show source

01Imports System.Web

02Imports System.Web.Services

03Imports System.Web.Services.Protocols

04Imports System.Collections.Generic

05Imports System.Data.SqlClient

06Imports System.Data

07Imports System.Configuration

08 

09 

10<system.web.script.services.scriptservice()> _

11<webservice(namespace:="http: tempuri.org="" ")=""> _

12<webservicebinding(conformsto:=wsiprofiles.basicprofile1_1)> _

13<global.microsoft.visualbasic.compilerservices.designergenerated()> _

14Public Class AutoComplete

15    Inherits System.Web.Services.WebService

16 

17    <webmethod()> _

18    Public Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer) As String()

19        If count = 0 Then

20            count = 10

21        End If

22        Dim dt As DataTable = GetRecords(prefixText)

23        Dim items As New List(Of String)(count)

24 

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

26            Dim strName As String = dt.Rows(i)(0).ToString()

27            items.Add(strName)

28        Next

29        Return items.ToArray()

30    End Function

31 

32    Public Function GetRecords(ByVal strName As String) As DataTable

33        Dim strConn As String = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString

34        Dim con As New SqlConnection(strConn)

35        Dim cmd As New SqlCommand()

36        cmd.Connection = con

37        cmd.CommandType = System.Data.CommandType.Text

38        cmd.Parameters.AddWithValue("@Name", strName)

39        cmd.CommandText = "Select Name from Test where Name like '%'+@Name+'%'"

40        Dim objDs As New DataSet()

41        Dim dAdapter As New SqlDataAdapter()

42        dAdapter.SelectCommand = cmd

43        con.Open()

44        dAdapter.Fill(objDs)

45        con.Close()

46        Return objDs.Tables(0)

47 

48    End Function

49 

50End Class

51</webmethod()></global.microsoft.visualbasic.compilerservices.designergenerated()></webservicebinding(conformsto:=wsiprofiles.basicprofile1_1)></webservice(namespace:="http:></system.web.script.services.scriptservice()>

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

Tweet

If you like this post than join us or share