Using AutoComplete Ajax Control With Separate ID Field from Name Field

In the previous post, I detailed how to use the SQL stored procedure to select matches.

AutoComplete Extender offers a convenient way to select values froma TextBox. One problem is that multiple details can be displayed, but the whole text is selected by default. I wanted to allow multiple AutoCompletes, select the id without the other details and append the appropriate delimeter in between entries. First, I setup the WebService method. Note that the Stock object has Symbol and Name properties:

   [WebMethod]
    public string[] GetStocksInPrefix(string prefixText, int count)
    {
        int curCount = 0;
        if (count == 0)
        {
            count = 10;
        }
        List<string> items = new List<string>();
        JavaScriptSerializer jss = new JavaScriptSerializer();
        string[] stocks = null;
        string connectionString = getConnectionString("ConnectionString");
        SqlDataReader rdr = null;
        SqlConnection conn = new SqlConnection(connectionString);
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_aspnet_GetStocksByPrefix", conn);
        try
        {
            command.CommandType = System.Data.CommandType.StoredProcedure;
            conn.Open();
            command.Parameters.AddWithValue("@Prefix", prefixText+"%");
            rdr = command.ExecuteReader();
            string tmp = string.Empty;
            Stock _stock = null;
            while (rdr.Read() && curCount<count )
            {
                _stock = new Stock(rdr["Symbol"].ToString(), rdr["Name"].ToString(), rdr["Exchange"].ToString());
                tmp = rdr["Symbol"] + "\t" + rdr["Name"];
                items.Add(AutoCompleteExtender.CreateAutoCompleteItem(tmp, jss.Serialize(_stock)));
                curCount++;
            }
            command.Parameters.Clear();
        }
        catch (Exception ex)
        {
        }
        finally
        {
            if (rdr != null) rdr.Close();
            if(conn!=null) conn.Close();
        }
        return items.ToArray();
    }

Then, I add the javascript event, 'OnSymbolSelected',to the OnClientItemSelected event in the AutoComplete Extender:

                    <asp:TextBox ID="txtSymbols" runat="server" Width="300px"></asp:TextBox>
                    <cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" 
                        TargetControlID="txtSymbols" ServiceMethod="GetStocksInPrefix" 
                        ServicePath="~/WebServiceAutoCompleteSymbol.asmx" MinimumPrefixLength="2"
                        CompletionListHighlightedItemCssClass="watermark" CompletionSetCount="10"
                        DelimiterCharacters=", "
                        CompletionListElementID="Symbol"
                        CompletionListItemCssClass="watermarkMatch"                        
                        EnableCaching="true" CompletionInterval="1000" OnClientItemSelected="OnSymbolSelected"
                         >
                    </cc1:AutoCompleteExtender>

Finally, add the javascript function to the aspx page. This function reads the Stock object into results, takes the SelectedText and gets the original string before the new text was completed by replacing the completed text with an empty string. If it is an additional field (that does not contain ' ' or ',', then only the id (symbol in this case) is added. Othewise, the original id plus the ' ' delimeter and the new id is added:

<script type="text/javascript" language="javascript">   
    function OnSymbolSelected(source, eventArgs)   
    {   
        var results = eval('('  + eventArgs.get_value() + ')');   
        if (results.symbol != null)   {  
            var symbols = document.getElementById('<%= txtSymbols.ClientID %>').value;
            var original = symbols.replace((results.symbol+'\t'+results.name),'');
            if (original.indexOf(' ')>0 || original.indexOf(',')>0)
                document.getElementById('<%= txtSymbols.ClientID %>').value = original + (' '+ results.symbol);
            else  
                document.getElementById('<%= txtSymbols.ClientID %>').value = results.symbol;
            }
    }   
</script>  

References: http://ziqbalbh.wordpress.com/2008/06/11/google-like-autocomplete-suggestions/
http://www.tizag.com/javascriptT/javascript-string-replace.php
http://techron.blogspot.com/2008/04/reading-textbox-or-other-control-values.html

Using LIKE in SQL

Be careful in the order of parameters while using LIKE in T-SQL. The following stored procedure did not work @Prefix LIKE dbo.aspnet_Stocks.Name. It must be reversed:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sproc_aspnet_GetStocksByPrefix]
    @Prefix as varchar(50)=NULL
AS
BEGIN
            SELECT *
            FROM dbo.aspnet_Stocks
            WHERE ((Symbol LIKE @Prefix) OR ( [Name]LIKE @Prefix))
            RETURN 0    
END

This was used in an AutoComplete AJAX control. The following reference describes the setup.

Reference: http://www.aspdotnetcodes.com/AutoComplete_From_Database.aspx

A more efficient stored procedure would use a FullText Catalog indexed on a Text Column as described here: http://support.microsoft.com/?kbid=916784