Autocomplete extender with Id and Description

It was a very long time ago that i worked with the Ajax Toolkit, but sometimes you are requested to implement old projects..

And the language is VB.NET, that i was not using since 2003.

The request was to implement an Autocomplete extender from a customers list (from a Sql Server table), but in the database is requested to write the autoincrement ID, not the description.

In order to implement the ajax toolkit, in the .aspx markup must be added the header:

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

With the obvious adding of a reference , for the ASP.NET project, to AjaxControlToolkit.dll

A simple autoextender can be written as

<asp:TextBox ID="txtAgency" runat="server" ></asp:TextBox>
<cc1:AutoCompleteExtender ID="aceAgency" FirstRowSelected="false" MinimumPrefixLength="1" TargetControlID="txtAgency" ServiceMethod="GetListAgencies" runat="server">
</cc1:AutoCompleteExtender>

in the aspx page; in the code behind for this page we can implement a page method (the “ServiceMethod”) as

<System.Web.Script.Services.ScriptMethod(), _
System.Web.Services.WebMethod()> _
Public Shared Function GetListAgencies(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
    Dim conn As SqlConnection = New SqlConnection
    '
    conn.ConnectionString = <string from web.config>
    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandText = "SELECT AnagId, AnagName FROM [dbo].[SomeTableOrView] WHERE AnagName LIKE @SearchText + '%'"
    cmd.Parameters.AddWithValue("@SearchText", prefixText)
    cmd.Connection = conn
    conn.Open()
    Dim lstAgencies As List(Of String) = New List(Of String)
    Dim sdr As SqlDataReader = cmd.ExecuteReader
    While sdr.Read
        lstAgencies.Add(sdr("AnagName").ToString))
    End While
    conn.Close()
    Return lstAgencies
End Function

This works, we type one letter (MinimumPrefixLength=”1″) on the keyboard and we are proposed with the list of names beginning with the typed letter.

But from the database in our sample we have only the AnagName value , the AnagId is not connected in some manner with the selected item: if we need to save that Id instead of the name this approach is not valid.

In order to save that id we need some javascript code and another approach in the web method.

First, this is the new aspx markup:

<asp:TextBox ID="txtAgency" runat="server"></asp:TextBox>
<cc1:AutoCompleteExtender ID="aceAgency" FirstRowSelected="false" TargetControlID="txtAgency" ServiceMethod="GetListAgencies" OnClientHidden="onClientHiddenAgency" MinimumPrefixLength="1"  EnableCaching="true" OnClientItemSelected="onClientItemSelectedAgency" OnClientPopulating="onClientPopulatingAgency" runat="server">
</cc1:AutoCompleteExtender>
<input type="hidden" id="hdnAgencyId" name="hdnAgencyId" runat="server" value="0" />

The OnClient… properties are the names of javascript functions; the EnableCaching is true because the underlying data are in this case very static.

Note that there is an hidden field where to save the requested Id and this is runat=server because we need to refer this field when we will save the record.

The javascript functions (ok, not the best javascript code…):

var boolChosenAgency = false;

function onClientItemSelectedAgency(source, e) {
    window.document.getElementById("<%=hdnAgencyId.ClientID%>").value = e.get_value();
    boolChosenAgency = true;
}

function onClientHiddenAgency(source, e) {
    if (!boolChosenAgency) {
        window.document.getElementById("<%=hdnAgencyId.ClientID%>").value = "0";
        window.document.getElementById("<%=txtAgency.ClientID%>").value = "";
    }
}

function onClientPopulatingAgency(source, e) {
    window.document.getElementById("<%=hdnAgencyId.ClientID%>").value = "0";
    boolChosenAgency = false;
}

What happens with this code is:

• When the user begins to write, the Id value is changed to 0 and boolChosenAgency is set to false (“the user has not stille chosen an valid value”)
  
• Only when an item is chosen the Id (coming from e.get_value()) is written in the hidden field
  
• OnClientHiddenAgency is always executed, if the user has opened the list but has not clicked on a value then the id value is changed to 0
  

On the code behind so we must verify : if the hidden id is 0 and/or the textbox is empty the the id must be null on the db, otherwise we can write the int value.

The scope of the code is also: if the user write a not valid text, the Id is invalidated.

But how we can have the id in e.get_value() ?

This is done with this code for the web method:

<System.Web.Script.Services.ScriptMethod(), _
System.Web.Services.WebMethod()> _
Public Shared Function GetListAgencies(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
    Dim conn As SqlConnection = New SqlConnection
    '
    conn.ConnectionString = <string from web.config>
    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandText = "SELECT AnagId, AnagName FROM [dbo].[SomeTableOrView] WHERE AnagName LIKE @SearchText + '%'"
    cmd.Parameters.AddWithValue("@SearchText", prefixText)
    cmd.Connection = conn
    conn.Open()
    Dim lstAgencies As List(Of String) = New List(Of String)
    Dim sdr As SqlDataReader = cmd.ExecuteReader
    While sdr.Read
        lstAgencies.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(sdr("AnagName").ToString, sdr("AnagID").ToString))
    End While
    conn.Close()
    Return lstAgencies
End Function

The trick is done using the CreateAutoCompleteItem method as in the above code.

In the javascript code we can use e.get_value for the ID and e.get_text() for the name (“AnagName” in the sample).