ASP.Net AJAX AutoComplete Extender help

Djvarley · August 17th, 2011, 09:11 AM

Hello there,

I have been tasked with making a search function with an AutoComplete extender using AJAX and an SQL database.

I have some preliminary code written up that no longer gives me compiling errors, yet it doesn't fire when I try to invoke it (typing in the textbox).

Could I please have some assistance in getting this code to work or pointing me in the right direction? There are several tutorials on this matter and my code and all the infrastructure is similar except from the variables that I need to use. (Connection String etc.)

Also I am not too sure on the locations that the code I have written needs to be so I will include the location before each snippet.

Below is my code for the back end or .asmx file (Not .asmx.vb).

Code:

Imports System.Web
 
Imports System.Web.Services
 
Imports System.Web.Services.Protocols
 
Imports System.Collections.Generic
 
Imports System.Data
 
Imports System.Data.SqlClient
 
Imports System.Configuration
 
<System.Web.Script.Services.ScriptService()><WebService(Namespace:="http://www.commpartners.us/webservices", Description:="Webservice to allow a autocompleter service lookup")><WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)>Public Class PartnerList
    Inherits System.Web.Services.WebService

<WebMethod(Description:="Method to retrieve Partner List")> Public Function GetPartnerList(ByVal prefixText As String, ByVal count As Integer) As Array

        Dim SqlConnection1 As New SqlConnection(ConfigurationManager.AppSettings("NorthwindConnectionString"))
 
    Dim SqlCommand1 As New SqlCommand("SELECT TOP & count.ToString() & * from Products WHERE ProductName like '& prefixText &%'", SqlConnection1)
 
    SqlCommand1.Parameters.AddWithValue("nrows", count)
 
    SqlCommand1.Parameters.AddWithValue("term", prefixText & "%")
 
    Dim suggestions As New List(Of String)
 
    SqlConnection1.Open()
 
    Dim dr As SqlDataReader = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection)
    While dr.Read
 
            suggestions.Add(dr(0).ToString)
            
    End While
    Return suggestions.ToArray
End Function
 
End Class

Here is my front end (.aspx) code

Code:

<asp:TextBox ID="searchtext" runat="server"></asp:TextBox>
        <asp:AutoCompleteExtender ID="TextBox1_AutoCompleteExtender" runat="server" 
            TargetControlID="searchtext" ServiceMethod="ProductList"
                          ServicePath="~/WebService.asmx" MinimumPrefixLength="1" CompletionInterval="500"></asp:AutoCompleteExtender>
        <br />
        <br />
        <asp:LinkButton ID="LinkButton1" runat="server" >go</asp:LinkButton>
    </div>
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        <Services>

                <asp:ServiceReference Path="~/WebService.asmx" />

            </Services>
        </asp:ScriptManager>

Any help on this would be greatly appreciated.

Regards,

David

Djvarley · August 19th, 2011, 07:31 AM

Hello an update on the code above.

I have now got the code to fire and hit the breakpoints throughout however (I think) it is my SQL query that is not returning any values. Below is my updated code if anybody could help me.

Code:

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

Dim SqlConnection1 As New SqlConnection(
            ConfigurationManager.AppSettings("NorthwindConnectionString"))

Dim SqlCommand1 As New SqlCommand("SELECT ProductName from Products WHERE ProductName like '@prefixText + %'", SqlConnection1)
        SqlCommand1.Parameters.Add("@count", Data.SqlDbType.Int).Value = count
        SqlCommand1.Parameters.Add("@prefixText", Data.SqlDbType.VarChar, 50).Value = ("prefixText+%")

 Dim suggestions As New List(Of String)

        SqlConnection1.Open()

        If count = 0 Then
            count = 10
        End If

        If prefixText.Equals("xyz") Then
            Return New String(-1) {}

        End If


        Dim dr As SqlDataReader = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection)
        While dr.Read

            suggestions.Add(dr(0).ToString)

        End While
        Return suggestions.ToArray
    End Function

ginadave1z · August 19th, 2011, 08:17 AM

How to we create an XmlHttpRequest object for Internet Explorer? How is this different for other browsers?

Djvarley · August 24th, 2011, 04:39 AM

Third time lucky...

I have now got the code to work, for example the "results" variable returns all the values from my SQL query. However it does not display the information retrieved, yet my code is extremely similar to a lot of tutorials around. Can anyone spot anything out of place?

Once again my code is below, if anyone would like a .ZIP of the whole project just ask.

Code:

 <WebMethod()> _
    Public Function GetCompletionList(ByVal prefixText As String, _
ByVal count As Integer) As String()
        Dim SelectQry = "select * from Products where ProductName like '%" & prefixText & "%'"
        Connection.Open()
        Dim Results As New ArrayList
        Try
            Using Command As New SqlCommand(SelectQry, Connection)
                Using Reader As SqlDataReader = Command.ExecuteReader()
                    Dim Counter As Integer
                    While Reader.Read
                        If (Counter = count) Then Exit While
                        Results.Add(Reader("ProductName").ToString())
                        Counter += 1
                    End While
                End Using
                Dim ResultsArray(Results.Count - 1) As String
                ResultsArray = Results.ToArray(GetType(System.String))
                Return ResultsArray
            End Using
        Catch ex As Exception
            Throw ex
        End Try
    End Function