jQuery UI Autocomplete Widget with ASP.NET VB

You might also be interested in the Using jQuery Autocomplete to Populate Another Autocomplete post.

As a follow up to the jQuery UI Autocomplete Widget with ColdFusion and the jQuery UI Autocomplete Widget with PHP posts, I did one with ASP.NET (VB.NET) as the backend. I swear this is the last one I’m doing. I’m running out of languages that I work in.

The jQuery UI folks have released an autocomplete widget that is pretty slick. This example uses the JavaScriptSerializer() function in .NET 3.5. I heard a rumor .NET 4 might make this json encoding with data easier. We’ll see.
autocomplete
This example will use US states and territories to populate the autocomplete. It will also demonstrate how to fill other fields with data returned from the database. This data can be used to fill a visible text box or a hidden form field. It also demonstrates the basic autocomplete functionality which may be fine for some applications.

Of course, you will need the jQuery core file, the jQuery UI core file, and the jQuery UI style sheet of choice. The style sheet comes from the themes available in the jQuery UI website and can be downloaded with the core file or you can link to the latest versions of both the core files and the css:

<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1/themes/redmond/jquery-ui.css" /> 
 
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1/jquery-ui.min.js"></script>

The HTML is straight forward and stripped down for the example:

<form action="Default.aspx"  method="post">
<fieldset>
<legend>jQuery UI Autocomplete Example - ASP.NET VB Backend</legend>
<p>Start typing the name of a state or territory of the United States</p>
<p class="ui-widget"><label for="state">State (abbreviation in separate field): </label>
	<input type="text" id="state"  name="state" /> <input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></p>
    <input type="hidden" id="state_id" name="state_id" />
<p class="ui-widget"><label for="state_abbrev">State (replaced with abbreviation): </label>
<input type="text" id="state_abbrev" name="state_abbrev" /></p>
<p><input type="submit" name="submit" value="Submit" /></p>
</fieldset>
</form>

As a bonus, we dump out the form values to see what we have right underneath the form itself:

Sub Page_Load(Source As Object, E As EventArgs)  
 	Dim formfields As String = "<p>" 
	
     For Each sItem In Request.Form
	 	formfields = formfields + "<strong>" + sItem + "</strong> = " +  Request.Form(sItem) + "<br />"
  	Next  
	formoutput.Text = formfields + "</p>"
 End Sub  

And the jQuery on the page is equally brief:

$(function() {
        
            $('#abbrev').val("");
            
            $("#state").autocomplete({
                source: "states.aspx",
                minLength: 2,
                select: function(event, ui) {
                    $('#state_id').val(ui.item.id);
                    $('#abbrev').val(ui.item.abbrev);
                }
            });
            
            $("#state_abbrev").autocomplete({
                source: "states_abbrev.aspx",
                minLength: 2
            });
        });

Notice that there are two autocomplete functions on the page, one for each example in the demo. Each function calls a different aspx file which return slightly different result sets.

Also, the minLength for autocomplete to return results is set to 2 to prevent too many rows from being returned.

The jquery autocomplete will append the text typed into the autocomplete field as the URL parameter ‘term.’ This URL parameter is used to query the database.

From the jquery documentation:

The request parameter “term” gets added to that URL.

Both .NET pages return the data after a few steps:

  1. It creates a new javascript serializer
  2. It creates an object to hold the data from each returned row in the query
  3. It queries the database and fills a dataset (keep reading if you like readers better)
  4. Loops an array of the query results adding each row to an object
  5. Adds the object to an ArrayList
  6. Outputs the ArrayList as JSON data

The states.aspx file returns the id field, the state field as ‘value’, and the abbrev field. These values are placed in the appropriate text boxes by the autocomplete jQuery function.

<%@ Page Language="VB" Debug="false" %>
<%@ Import Namespace="System.Web.Script.Serialization" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>


<script runat="server">
    Dim serializer As JavaScriptSerializer

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        serializer = New JavaScriptSerializer()
        Response.Write(JSONData(Request.QueryString("Term")))
    End Sub
    
    Public Class State
        Public id As Integer
        Public value As String
        Public abbrev As String
    End Class

    Private Function JSONData(ByVal term As String) As String
        
        Dim stateArray As New ArrayList
        Dim index As Integer = 0
        
        Dim objConn As New SqlConnection("YOUR-CONNECTION-STRING-HERE")
        Dim myds As New DataSet("States")
        
        objConn.Open()
       
        Dim adapter As New SqlDataAdapter("SELECT id, state, abbrev FROM states WHERE state like '%' + @ac_term + '%'", objConn)
        adapter.SelectCommand.Parameters.Add("@ac_term", SqlDbType.VarChar)
        adapter.SelectCommand.Parameters("@ac_term").Value = term
        adapter.Fill(myds, "States")

        For Each dr As DataRow In myds.Tables(0).Rows
            Dim st As New State()
            st.id = dr("id").ToString()
            st.value = dr("state").ToString()
            st.abbrev = dr("abbrev").ToString()
            stateArray.Add(st)
        Next
        
        objConn.Close()
        
        Return serializer.Serialize(stateArray)
    End Function
    
    </script>

The states_abbrev.aspx shows the basic functionality of the autocomplete function by just assigning results of the query to the ‘label’ and ‘value’ fields. Explanation on the ‘label’ and ‘value’ fields from the jQuery UI site:

Very important information below. Please read and understand before expecting the autocomplete to work properly.

The local data can be a simple Array of Strings, or it contains Objects for each item in the array, with either a label or value property or both. The label property is displayed in the suggestion menu. The value will be inserted into the input element after the user selected something from the menu. If just one property is specified, it will be used for both, eg. if you provide only value-properties, the value will also be used as the label.”

<%@ Page Language="VB" Debug="false" %>
<%@ Import Namespace="System.Web.Script.Serialization" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
    Dim serializer As JavaScriptSerializer

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        serializer = New JavaScriptSerializer()
        Response.Write(JSONData(Request.QueryString("Term")))
    End Sub
    
    Public Class State
        Public label As String
        Public value As String
    End Class

    Private Function JSONData(ByVal term As String) As String
        
        Dim stateArray As New ArrayList
        Dim index As Integer = 0
        
        Dim objConn As New SqlConnection("YOUR-CONNECTION-STRING-HERE")
        Dim myds As New DataSet("States")
        
        objConn.Open()
        
        Dim adapter As New SqlDataAdapter("SELECT id, state, abbrev FROM states WHERE state like '%' + @ac_term + '%'", objConn)
        adapter.SelectCommand.Parameters.Add("@ac_term", SqlDbType.VarChar)
        adapter.SelectCommand.Parameters("@ac_term").Value = term
        adapter.Fill(myds, "States")

        For Each dr As DataRow In myds.Tables(0).Rows
            Dim st As New State()
            st.label = dr("state").ToString()
            st.value = dr("abbrev").ToString()
            stateArray.Add(st)
        Next

		objConn.Close()
        
        Return serializer.Serialize(stateArray)
    End Function
    
    </script>

Usual recommended jQuery and .NET reading:



Posted in Uncategorized. Permalink. Both comments and trackbacks are closed.

18 Comments

  1. John
    April 7, 2011 at 8:18 am | Permalink

    Jen – I just wanted to thank you for clearing this stuff up for me. I found A LOT of posts that in my opionion either complicated of simplified the use of JQuery UI Autocomplete Widget. Part of the problem for me too was the articles I found didn't mention really what version of the Autocomplete were being used (Devbridges, Basassistance (or whatever that is called) or some variant of them. THANKS!!!!

  2. March 9, 2011 at 10:11 am | Permalink

    @Tom
    Put the change to the other field in on the 'select' event of the autocomplete.
    select: function(event, ui){
    //changes here with jquery/javascript
    }
    If you need to look up something based on their selection in the autocomplete, then you could do an ajax post in the select function to run some code and return a value.

  3. March 8, 2011 at 3:31 pm | Permalink

    I have worked out the issues and found that the easiest way to work with textboxes in a grid is with a CssClass that identifies the control. I have the autolookup part working on the row I am editing but now need to do a postback to change another field depending on the return of the autolookup.

    I cannot use a conventional onBlur or onTextChanged because I get back the values I typed in to do the autolookup not the selected value (it is short-curcuited ). The autolookup needs to complete and put the looked up value in the testbox before I can postback.

    So I think I need for the jQuery to initiate the postback. I have tried various versions of:

    .result(function (event, item)

    but none have worked. I am using UI 1.8.9. for autocomplete.

    Thanks for any help on this one too!

  4. January 30, 2011 at 2:33 pm | Permalink

    @Amit
    You can put the vb.net code in any of the free vb to c# converters that are available on the internet.
    The code you do not know where to put only displays the form field values. You do not have to have it on your page.

  5. Amit Rai
    January 30, 2011 at 9:10 am | Permalink

    this is really a very great effort,
    can you provide the full source code in asp.net 3.5 with C# ?
    another thing is that i am confuse that
    where to put the following code

    Sub Page_Load(Source As Object, E As EventArgs)
    Dim formfields As String = "<p>"

    For Each sItem In Request.Form
    formfields = formfields + "<strong>" + sItem + "</strong> = " + Request.Form(sItem) + "<br />"
    Next
    formoutput.Text = formfields + "</p>"
    End Sub

  6. January 27, 2011 at 7:22 pm | Permalink

    Stylesheet in the Master page, Javascript in the Parent page and a public property in the User Control referenced in the Javascript such as: $("#<%=SearchControl1.ClientName.ClientID%>").autocomplete({ source: "SearchFill.aspx?myControl=searcr01", minLength: 2 });

    Maybe someone knows a better way.

  7. Tom
    January 27, 2011 at 4:11 pm | Permalink

    OK Thanks – this page does not use AjaxControlToolkit just Ajax in Update Panels with Web User Controls for all parts of the page which I have to do autolookups on. It also uses Master Pages a couple of layers deep. I will try Firebug.

  8. January 27, 2011 at 4:00 pm | Permalink

    @Tom
    You probably have some conflicts between the jquery automcomplete and whatever else you have on your page. I have removed all AjaxControlToolkit stuff from my aspx pages and go all with jquery now. If you run the page in Firefox with the Firebug plugin, it should tell you that there are issues with the javascript.

One Trackback

  1. [...] http://www.jensbits.com/2010/04/14/jquery-ui-autocomplete-widget-with-asp-net/ Categories: Asp.net, Jquery Tags: Asp.net, Autocomplete, Autocomplete with Jquery, autosuggest, hidden field Jquery autocomplete, Jquery, Jquery autocomplete demo with asp.net, Jquery Autocomplete server side, Jquery Autocomplete with aspnet, Jquery cachelength problem, Jquery caching error, Jquery caching problem, Jquery result, Jquery result handler, jquery UI autocomplete, jquery ui autocomplete with asp.net Comments (0) Trackbacks (0) Leave a comment Trackback [...]