Monday, March 26, 2012

Autocomplete using VB.NET and AJAX



For using the Autocomplete extender using AJAX and VB.NET you need to create a webservice (.asmx) first. Below is the code for the webservice I'm using to get the autocomplete strings. The webservice uses a SQL statement to get the results. The statement has the "like" clause which matches the first letter and gets all the strings that are supersets of the string entered.

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class AutoComplete
    Inherits System.Web.Services.WebService


<WebMethod()> _
   Public Function GetProducts(ByVal prefixText As String, _
                                 ByVal count As Integer) As String()

        Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLServer2008DBConnectionString").ToString())
        cn.Open()
        Dim myCommand As New SqlCommand()
        myCommand.Connection = cn
        myCommand.CommandText = "Select ac_id from dbo.tblAccountCarrier where ac_id like '" & prefixText & "%'"
        Dim Results As New ArrayList
        Try
            Using Command As New SqlCommand(myCommand.CommandText, cn)
                Using dr As SqlDataReader = myCommand.ExecuteReader()

                    Dim Counter As Integer
                    While dr.Read
                        If (Counter = count) Then Exit While
                        Results.Add(dr("ac_id").ToString())
                        Counter += 1
                    End While
                End Using
                Dim ResultsArray(Results.Count - 1) As String
                ResultsArray = Results.ToArray(GetType(System.String))
                Return ResultsArray
            End Using
            cn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Function

The second part is using the autocomplete AJAX extender in the .aspx page. You need to register the AJAXtoolkit at the top of the page using this tag:
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

In the Autocomplete tag you need to specify the  ServicePath which is a path to the above webs service and ServiceMethod which is the name of the web method created in the class.


In the .aspx page


<asp:TextBox ID="acct_idTextBox" runat="server"></asp:TextBox>
                    <cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" ServicePath="~/AutoComplete.asmx"
                       ServiceMethod ="GetProducts" MinimumPrefixLength="1" TargetControlID="acct_idTextBox">
                    </cc1:AutoCompleteExtender>  

1 comment:

Your comments are moderated by your ISP.