blowfly
asked on
Multi column or ItemData in AJAX AutoCompleteExtender?
I've got the AJAX AutoCompleteExtender working in my project. For example, one of them a list of suburbs - if you type in "BUR" it will start listing "BURWOOD" "BURWOOD EAST" etc.
That's great, except that to really use the response, I need to access an ID number, not the string itself. For example, the submitted page needs to use Suburb_ID 1500 rather than the string "BURWOOD".
The lazy approach would be to run SQL queries to translate the string back into the ID ("SELECT ID FROM Suburbs WHERE Name = 'BURWOOD'; ") but architecturally and performance wise this is dreadful.
So my question: Is there a way to for AJAX AutoCompleteExtender to store multiple columns, or, associated item data with each item it lists?
That's great, except that to really use the response, I need to access an ID number, not the string itself. For example, the submitted page needs to use Suburb_ID 1500 rather than the string "BURWOOD".
The lazy approach would be to run SQL queries to translate the string back into the ID ("SELECT ID FROM Suburbs WHERE Name = 'BURWOOD'; ") but architecturally and performance wise this is dreadful.
So my question: Is there a way to for AJAX AutoCompleteExtender to store multiple columns, or, associated item data with each item it lists?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No, but if you find one, it would be a "treasure"...
I am not saying that one doesn't exist, and that the AutoCompleteExtender doesn't support it. I am saying that I don't know of any solution...
I am not saying that one doesn't exist, and that the AutoCompleteExtender doesn't support it. I am saying that I don't know of any solution...
ASKER
Fair enough, thanks for the help.
You can accomplish it one of 2 ways. You can create a Custom Control that inherits the AutoCompleteExtender class and override the Render event.
Or... you can go with this guy's javascript solution:
http://www.darwyn.com/blogs/post/2009/03/03/custom-Auto-Complete-Extender-Extended.aspx
Or... you can go with this guy's javascript solution:
http://www.darwyn.com/blogs/post/2009/03/03/custom-Auto-Complete-Extender-Extended.aspx
If I did not misunderstand the need, here is you can try a couple of lines.
using (SqlConnection ConnStr = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))
{
ConnStr.Open();
SqlCommand sqlcmd = new SqlCommand(@"NameOfTheStoredProcedure", ConnStr);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@ourComingEnteredWord", SqlDbType.NVarChar, 50).Value = prefixText.Trim().ToLower();
SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
DataTable dt = new DataTable("ResultTable");
try
{
da.Fill(dt);
}
catch {
}
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
//added for keyvalue pair feature.
items.SetValue(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(dr["SuburbName"].ToString(), dr["SuburbID"].ToString()), i);
i++;
}
return items;
}//end of sqlconnection
ASKER
Otherwise points to you.