We know
how to use AutoComplete control to display suggestions as you enter text into
a TextBox.
However,
as we know AutoComplete control only retrieves text and no values. In sort,
AutoComplete
only
retrieves text and no IDs. You might be thinking what use of IDs ?. An
example of where we have
multiple
tables and we store ID in foreign table instead of text. Consider a scenario,
where you have to
search
for department name and for that particular department, you have to display
its employee data
from
employee table and in that department id has been stored. For this you will
store department ID
into
employee table. Here where Text and Value Pairs with AutoComplete control is
useful.
The AutoComplete
contorl includes a static method named CreateAutoCompeleteItem() that returns
a single
string
that represents a text and value pair. You can use this method when returning
a string array from the
GetSuggestions()
method to include a primary key with each suggestion.
|
A TextBox control, however, can
represent only a single value. In order to represent the ID of the selected
text, you need to add a hidden form field to your page.You can update the value of the hidden field whenever a user selects a new suggestions I will explain here part by part.
[System.Web.Services.WebMethod]
public static string[] GetSuggestions(string prefixText, int count)
{
try
{
string constr=string.Empty;
constr= WebConfigurationManager.ConnectionStrings["ConnectionString"].
ConnectionString;
string sqlQuery = "SELECT [FirstName],[CustomerID] FROM [Customer]
WHERE FirstName like @FirstName";
SqlConnection conn = new SqlConnection(conStr);
SqlCommand cmd=new SqlCommand(sqlQuery,conn);
cmd.Parameters.AddWithValue("@FirstName",
prefixText + "%");
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
List<string>
custList = new List<string>();
string custItem = string.Empty;
while (dr.Read())
{
custItem=AutoCompleteExtender.CreateAutoCompleteItem(dr[0].ToString(),
dr[1].ToString());
custList.Add(custItem);
}
conn.Close();
dr.Close();
return custList.ToArray();
}
catch (Exception ex)
{
throw ex;
}
}
Instead
of creating web service, I am creating into same page by adding
[System.Web.Services.WebMethod].
In
First part, with prefix I am fetching records and bind into DataReader.
In Second part, I am
looping
datareader and calling the AutoCompelteExtender.CreateAutoCompleteItem()
method
and
adding into List
The
AutoComplete extender is declaration as below :-
<cc1:AutoCompleteExtender ID="ace" TargetControlID="txtCustName"
ServiceMethod="GetSuggestions" MinimumPrefixLength="2"
OnClientItemSelected="ace_itemSelected" FirstRowSelected="true"
runat="server" />
The
AutoCompelte extender control includes an OnClientItemSelected property. When
a new
suggestion
is selected, the ace_itemSelected() JavaScript method executes.
The
ace_ItemSelected() method updates the value of the HiddenField with the value
of the selected
suggestion.
The JavaScript method looks like below :-
function ace_itemSelected(sender,
e)
{
var hdCustID = $get('<%=
hdCustID.ClientID %>');
hdCustID.value =
e.get_value();
}
The
second parameter passed to the JavaScript method includes a value property
that represents the primary key
of
the selected suggestion. The primary key is assigned to the hidden field so
that it can be read when the page
is
posted back to the server.
When you select a text, an OnTextChanged() event will occur and fetch record from database for that particular
customer
and display into grid. Code looks like below :-
protected void txtCustName_TextChanged(object sender, EventArgs e)
{
gvCustomer.DataSource=
GetCustomerDetail(Convert.ToInt32(hdCustID.Value));
gvCustomer.DataBind();
}
public SqlDataReader GetCustomerDetail(int CustID)
{
try
{
string conStr;
conStr
= WebConfigurationManager.ConnectionStrings["MyBlogConnectionString"]
.ConnectionString;
string sqlQuery = "SELECT CustomerID,FirstName,MiddleName,LastName,Desgination,
Address,City,";
sqlQuery=sqlQuery + "State,Country FROM
[Customer] WHERE CustomerID =
@CustomerID";
SqlConnection conn = new SqlConnection(conStr);
SqlCommand cmd = new SqlCommand(sqlQuery,
conn);
cmd.Parameters.AddWithValue("@CustomerID",
CustID);
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw ex;
}
}
 |
| follow links: http://weblogs.asp.net/manojkdotnet/archive/2009/11/30/text-and-value-pairs-with-autocompleteextender -and-display-record-in-gridview.aspx http://www.aspsnippets.com/Articles/Fetch-multiple-values-as-Key-Value-pair-in-ASP.Net-AJAX- AutoCompleteExtender.aspx |
