Text and Value Pairs with AutoCompleteExtender and Display record in Gridview
| 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. Download Source Code here. 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; conStr = WebConfigurationManager.ConnectionStrings["MyBlogConnectionString"].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<string> collections variable custList, and returing array of customer. The AutoComplete extender is declaration as below :- |
| <cc1:AutoCompleteExtender ID="ace" TargetControlID="txtCustName" ServiceMethod="GetSuggestions" MinimumPrefixLength="1" 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; } } |
| After selecting value from textbox, output look like below :- |
I hope you like text/value pair with AutoComplete and display record into grid at same time. Feedback are welcome.