Trying to get JQuery Autocomplete working on Asp.Net page.

Question

Can someone shed some light on the problem please:

I have the following:

    $(document).ready(function () {
    $("#txtFirstContact").autocomplete({url:'http://localhost:7970/Home/FindSurname' });
});

On my Asp.Net page. The http request is a function on an MVC Controller and that code is here:

    Function FindSurname(ByVal surname As String, ByVal count As Integer)
    Dim sqlConnection As New SqlClient.SqlConnection
    sqlConnection.ConnectionString = My.Settings.sqlConnection
    Dim sqlCommand As New SqlClient.SqlCommand

    sqlCommand.CommandText = "SELECT ConSName FROM tblContact WHERE ConSName LIKE '" & surname & "%'"

    sqlCommand.Connection = sqlConnection

    Dim ds As New DataSet
    Dim da As New SqlClient.SqlDataAdapter(sqlCommand)
    da.Fill(ds, "Contact")
    sqlConnection.Close()

    Dim contactsArray As New List(Of String)
    For Each dr As DataRow In ds.Tables("Contact").Rows
        contactsArray.Add(dr.Item("ConSName"))
    Next
    Return Json(contactsArray, JsonRequestBehavior.AllowGet)

End Function

As far as I'm aware, the Controller is returning JSON data, however I don't know if the Function Parameters are correct, or indeed if the format returned is interprettable by the AutoComplete plugin.

If anyone can assist in the matter I'd really appreciate it.

Answer 1

The function needs to accept a parameter named q which contains the search text. You can pass the count in using the extraParams option but it's not passed by default.

The content I return in my MVC controller actions is actually a new line delimited list built using a StringBuilder.AppendLine for each record in the search results.

HTH

This is a very good example of usage within MVC http://geekswithblogs.net/renso/archive/2009/09/08/jquery-autocomplete-in-asp.net-mvc-framework.aspx

Answer 2

Hi Dan,

Almost got this working, certainly not getting any errors now but likewise not getting any results.

I've now using the new version of JQuery which has AutoComplete built in, and do now get the animated wheel in the textbox which I wasn't getting before - so hopefully that's a good sign.

My MVC Function is:

    Function FindSurname(ByVal q As String, ByVal limit As Integer) As String
    Dim sqlConnection As New SqlClient.SqlConnection
    sqlConnection.ConnectionString = My.Settings.sqlConnection
    Dim sqlCommand As New SqlClient.SqlCommand

    sqlCommand.CommandText = "SELECT ConSName FROM tblContact WHERE ConSName LIKE '" & q & "%'"

    sqlCommand.Connection = sqlConnection

    Dim ds As New DataSet
    Dim da As New SqlClient.SqlDataAdapter(sqlCommand)
    da.Fill(ds, "Contact")
    sqlConnection.Close()
    Dim a As New StringBuilder
    For Each dr As DataRow In ds.Tables("Contact").Rows
        a.Append(dr.Item("ConSName"))
        a.AppendLine()
    Next
    Return a.ToString
End Function

and the script on the aspx page is:

    $(document).ready(function () {
    $("#txtFirstContact").autocomplete({    source: 'http://localhost:7970/Home/FindSurname/',
                                            minLength: 2
                                        });
});

Again, the "minLength" parameter seems to be working fine too as the animated wheel doesn't 'kick in' until I've entered the 2nd character.

Any additional pointers would be very appreciated.