missice's Blog

AutoComplete Textbox with Additional Parameters From Database
Posted by Moderator1 on 7/23/2007 11:16:01 PM     Category: AJAX
Total Views : 41847    
 
Introduction
This article explains the concept of the Ajax AutoComplete Textbox to fetch data from the database with additional parameter.
A Textbox can be integrated with the Ajax AutoComplete Extender to perform AutoComplete functionality and it also helps to fetch data from the database. This concept is explained clearly in our previous article titled “AutoComplete From Database". This article is aimed to provide more knowledge about the AutoComplete Extender which can take some additional parameters through which we can fine tune a data fetched from the database. To achieve this we need to install the new AjaxControlToolkit version 10618, released on June 2007.

Key Properties

The main properties to achieve this AutoComplete Textbox with additional parameter are contextKey and UseContextKey. These two properties help to set some additional parameters to the prefixText that is passed to the webservice. ContextKey ia a user or page specific context provided to an optional overload of the web method described in the ServiceMethod of the AutoComplete Extender. If the context key is used, it should have the same signature with an additional parameter named contextKey of type string. The UseContextKey is to specify whether or not the contextKey property should be used.
ASP.NET Web Hosting – Click Here    

Sample Scenario

We are going to fill a single AutoComplete Textbox with two different types of values based on the condition provided from a DropDownList control. The first type of value will be country name and the second type of value will be the states in US. You can choose the type of values to be filled in the AutoComplete Textbox from the DropDownList provided. This sample is for only demonstration purpose, but in real time you can process any type of contextKey values to achieve excellent stuffs.

Make sure you have installed latest version (10618) of AjaxToolkit control in your development system. In your Ajax Enabled website, drag and drop a Textbox from your Toolbox. Then drag and drop a ScriptManager and AutoCompleteExtender to your Default.aspx page. Then add a webservice to your project as WebService.asmx. Add the ScriptService reference to the webserive as follows.

[System.Web.Script.Services.ScriptService]

Now, write a webmethod ‘GetCountryOrStatesInfo’ to fetch the data from the Country or StatesinUS table as follows

[WebMethod] public string[] GetCountryOrStatesInfo(string prefixText, int count, string contextKey)
{
   int count = 10;
   string sql;
   if (contextKey == "Country")
      sql = "Select Country_Name from Country Where Country_Name like @prefixText";
   else
      sql = "Select States_Name from States Where States_Name like @prefixText";
   SqlDataAdapter da = new SqlDataAdapter(sql,”Your connection String”);
   da.SelectCommand.Parameters.Add("@prefixText",SqlDbType.VarChar, 50).Value = prefixText + "%";   DataTable dt = new DataTable();
   da.Fill(dt);
   string[] items = new string[dt.Rows.Count];
   int i = 0;
   foreach (DataRow dr in dt.Rows)
   {
     items.SetValue(dr[0].ToString(), i);
     i++;
   }
   return items;
}

The above webmethod takes 3 arguments such as prefixText, count and contextKey. So any method that uses contextKey must be with the above signature. The prefixText will pass the initial value entered in the AutoComplete TextBox, count will give the number of items returned to the popup menu of the TextBox and contextKey will be the additional parameter to be passed to the webmethod. So in the above sample webmethod, we pass the type of value to be displayed in the TextBox that is either Country of States.

Next, in the Default.aspx page, set the AutoCompleteExtender’s TargetControlID property to the TextBox Id. Now you can see a new Extenders Tab is added in the Textbox’s Property window. Set ServicePath as WebService.asmx, ServiceMethod as GetCountryOrStatesInfo and useContextKey to true. Specify the default values for contextKey as Country. The code for the AutoComplete Extender will be as follows

<cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength="1" ServiceMethod="GetCountryOrStatesInfo" UseContextKey="true" ContextKey="Country" ServicePath="WebService.asmx" TargetControlID="TextBox1"></cc1:AutoCompleteExtender>

AutoCompleteExtender1.ContextKey = cmbList.SelectedValue;

The code of the DropDownList control will be

<asp:DropDownList ID="cmbList" runat="server" AutoPostBack="True" OnSelectedIndexChanged="cmbList_SelectedIndexChanged"> <asp:ListItem Value="Country">List only Country</asp:ListItem> <asp:ListItem Value="States">List only States</asp:ListItem> </asp:DropDownList>

Save your project and run the application. You can see the AutoComplete TextBox and DropDownList control. Now type some letters in the TextBox, it will populate the corresponding countries. Next change the value in the DropDownList as List Only States, and type some letters in the TextBox it will populate the States from the starting letter you typed.