Display two different values from different columns of same row in autocoompleteextender

Question

I am using autocompleteextender in my asp.net website. It is working fine. I want additional functionality when user types it shows suggestion like below

suppose I type P in textbox Result - PNQ , PAR, PBC etc

Here evry keyword has fullform in second column so I want data to display like this

PNQ (Pune Airport)

When this value get selected by user then should only select PNQ in textbox not entire value i.e PNQ (Pune Airport). This I want becuase users should get clear what they are selecting. Can anyone help me in this.

 <asp:AutoCompleteExtender ServiceMethod="SearchClientCode"
 ServicePath="~/myadmin/shipments-profile.aspx" MinimumPrefixLength="1"
 OnClientShown="resetPosition" CompletionInterval="100" EnableCaching="false"
 CompletionSetCount="10" TargetControlID="clientCode" ID="clientCodeExtender"
 runat="server" FirstRowSelected="false" CompletionListCssClass="completionList"
 CompletionListItemCssClass="listItem"
 CompletionListHighlightedItemCssClass="itemHighlighted"></asp:AutoCompleteExtender>


[System.Web.Script.Services.ScriptMethod(), System.Web.Services.WebMethod()]
public static List<string> SearchClientCode(string prefixText, int count)
{
    MySqlConnection conn = new MySqlConnection();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings("conio").ConnectionString;
    MySqlCommand cmd = new MySqlCommand();
    cmd.CommandText = "SELECT clientID, clientName FROM clientsDetails where (clientID like @SearchText)";
    cmd.Parameters.AddWithValue("@SearchText", prefixText + "%");
    cmd.Connection = conn;
    conn.Open();
    List<string> customers = new List<string>();
    MySqlDataReader sdr = cmd.ExecuteReader;
    while (sdr.Read) {
        customers.Add(sdr("clientID").ToString);
    }
    conn.Close();
    return customers;
}

Answer 1

You need to add some client side functions and change in webmethod to accomplish your requirements

Html:

Hook up two events OnClientPopulated and OnClientItemSelected and set BehaviorID to control.

<asp:AutoCompleteExtender ServiceMethod="SearchClientCode" ServicePath="~/myadmin/shipments-profile.aspx" MinimumPrefixLength="1" OnClientShown="resetPosition" CompletionInterval="100" EnableCaching="false" CompletionSetCount="10" TargetControlID="clientCode" ID="clientCodeExtender" runat="server" FirstRowSelected="false" CompletionListCssClass="completionList" CompletionListItemCssClass="listItem" CompletionListHighlightedItemCssClass="itemHighlighted" OnClientPopulated="onClientPopulated" OnClientItemSelected="itemSelected" BehaviorID="AutoCompleteEx"></asp:AutoCompleteExtender>

Javascript

<script type="text/javascript">
  function itemSelected(ev)
  {
    var index=$find("AutoCompleteEx")._selectIndex;
    if(index!=-1) {
   $find("AutoCompleteEx").get_element().value =$find("AutoCompleteEx").get_completionList().childNodes[index]._value;
  }
 else{
  $find("AutoCompleteEx").get_element().value = '';   
 }
}

function onClientPopulated(sender,e)
{
  var List=$find("AutoCompleteEx").get_completionList();
  for(i=0;i<List.childNodes.length;i++)
   {
     var _value=JSON.parse(List.childNodes[i]._value);
     var abbr=_value[0];
     var fullform =_value[1];
     List.childNodes[i]._value=abbr;
     List.childNodes[i].innerHTML="<span>"+ abbr + "("+fullform+")</span>"
   }  }</script>

webmethod:

[System.Web.Script.Services.ScriptMethod(), System.Web.Services.WebMethod()]
public static List<string> SearchClientCode(string prefixText, int count)   
 {
   MySqlConnection conn = new MySqlConnection();
   conn.ConnectionString = ConfigurationManager.ConnectionStrings("conio").ConnectionString;
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "SELECT clientID, clientName FROM clientsDetails where (clientID like @SearchText)";
cmd.Parameters.AddWithValue("@SearchText", prefixText + "%");
cmd.Connection = conn;
conn.Open();
List<string> customers = new List<string>();
MySqlDataReader sdr = cmd.ExecuteReader;
JavaScriptSerializer serializer = new JavaScriptSerializer();
while (sdr.Read) {
    object[] item = new object[] { sdr("clientID").ToString(), sdr("clientName").ToString() };
    customers.Add(serializer.Serialize(item));
}
conn.Close();
return customers;}

Hope it helps!!