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 :-

 

Cust

 

I hope you like text/value pair with AutoComplete and display record into grid at same time. Feedback are welcome.

Published Monday, November 30, 2009 9:33 PM by Manoj karkera

Comments

# re: Text and Value Pairs with AutoCompleteExtender and Display record in Gridview

Thursday, February 11, 2010 12:29 PM by Sundar

I have a doubt in AutoCompleteExtender ... I have manually bind the Gridview in the code behind using DataTable.In the Gridview,two text boxes are available.In the autocompleteExtender, i have set the TargetControlID as first text box name.It is coming correctly.When i manually insert the next row it throws the error in the screen.I don't know what is the exact problem happened for this case......

If anybody knows pls tell me the solution...

# re: Text and Value Pairs with AutoCompleteExtender and Display record in Gridview

Monday, February 15, 2010 9:54 PM by Manoj karkera

Just check below link, it will help you

  Implementing autocomplete textbox in gridview using Ajax autocomplete extender

  www.dotnetfunda.com/.../article224.aspx

# re: Text and Value Pairs with AutoCompleteExtender and Display record in Gridview

Tuesday, November 16, 2010 11:00 PM by Gayathri

This blog helped me lot! Thanks.. !

# re: Text and Value Pairs with AutoCompleteExtender and Display record in Gridview

Wednesday, November 17, 2010 1:05 AM by Manoj karkera

Welcome :)