AutoCompleteExtender AJAX Question

Ok I am using the code below in file called autocomplete.asmx (web service file) my main question is do I need to create a different web service for every field I want my auto complete to work for? IE maybe I would like to have the Company Name pulled out instead of country, but another time maybe name, now I know this just involves changing the select statement but How could I go about doing this so that depending on what field it is, it knows what select statement to use?

Thanks

public class AutoComplete : System.Web.Services.WebService

{

[WebMethod]

public string[] GetCountriesList(string prefixText)

{

    DataSet dtst = new DataSet();

    SqlConnection sqlCon = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);

    string strSql = "SELECT CountryName FROM Tbl_ooo WHERE CountryName LIKE '" + prefixText + "%' ";

    SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);

    sqlCon.Open();

    SqlDataAdapter sqlAdpt = new SqlDataAdapter();

    sqlAdpt.SelectCommand = sqlComd;

    sqlAdpt.Fill(dtst);

    string[] cntName = new string[dtst.Tables[0].Rows.Count];

    int i = 0;

    try

    {

        foreach (DataR开发者_高级运维ow rdr in dtst.Tables[0].Rows)

        {

            cntName.SetValue(rdr["CountryName"].ToString(), i);

            i++;

        }

    }

    catch { }

    finally

    {

        sqlCon.Close();

    }

    return cntName;

}

}

Yes, you can use same webservice webmethod to populate country and company.

For that you want to use ContextKey property in ajax AutoCompleteExtender control

Below is the sample Code

Markup :

Search

<asp:TextBox ID="txtSearch" CssClass="textBlackBold" runat="server"       Width="350px"></asp:TextBox>                                
<asp:DropDownList ID="ddlType" runat="server"                                AutoPostBack="True" onselectedindexchanged="ddlType_SelectedIndexChanged">                
                                <asp:ListItem Value="0">Country</asp:ListItem>
                                <asp:ListItem Value="1">Companies</asp:ListItem>   
</asp:DropDownList>
<asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
                CompletionListCssClass="autocomplete_completionListElement" 
                CompletionListItemCssClass="autocomplete_listItem" 
                CompletionListHighlightedItemCssClass="autocomplete_highlightedListItem" 
                EnableCaching="true" ContextKey="Products" UseContextKey="true"
                TargetControlID="txtSearch" MinimumPrefixLength="1" 
                ServiceMethod="GetInfo" ServicePath="~/WebService.asmx" >
            </asp:AutoCompleteExtender>

Code Behind C# Code :

protected void ddlType_SelectedIndexChanged(object sender, EventArgs e) { string strContextKey = "";

    if(ddlType.SelectedValue.ToString() == "0")
        strContextKey = "Country";
    else
        strContextKey = "Companies";

    AutoCompleteExtender1.ContextKey = ddlType.SelectedItem.Text;
}

WebService Code :

[WebMethod]
public string[] GetInfo(string prefixText, string contextKey)
{
    DataSet dtst = new DataSet();                        
    SqlConnection sqlCon = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
    string strSql = "";

    if (contextKey == "Country")
    {                        
        strSql = "SELECT CountryName FROM Tbl_ooo WHERE CountryName LIKE '" + prefixText + "%' ";              
    }
    else if(contextKey == "Companies")
    {
        strSql = //Other SQL Query
    }

    SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);                        
    sqlCon.Open();                        
    SqlDataAdapter sqlAdpt = new SqlDataAdapter();
    sqlAdpt.SelectCommand = sqlComd;                        
    sqlAdpt.Fill(dtst);                        
    string[] cntName = new string[dtst.Tables[0].Rows.Count];                        
    int i = 0;                        
    try                        
    {                            
       foreach (DataRow rdr in dtst.Tables[0].Rows)                            
       {                                
          cntName.SetValue(rdr[0].ToString(),i);
          i++;                            
       }                        
    }
    catch { }                        
    finally                        
    {
        sqlCon.Close();                        
    }                        
    return cntName; 
}