[WebMethod()]
[ScriptMethod()]
public string StateSuggest(string tryValue, string[] additionalParams) {
SqlCommand sqlCommand = new SqlCommand();
//SQL Query to load cities
string loadCitySQL = @"
SELECT distinct top 7 State.StateId as StateId, State.Name as StateName FROM State
WHERE (State.Name like @tryValue OR State.Name like @tryValue1) ";
//bind query parameters
sqlCommand.Parameters.AddWithValue("tryValue", "% " + tryValue + "%");
sqlCommand.Parameters.AddWithValue("tryValue1", tryValue + "%");
List<SuggestionItem> items = new List<SuggestionItem>();
//load data from DataBase
using (SqlConnection sqlConnection = new SqlConnection(_SQLConnectionString)) {
sqlConnection.Open(); //open connection
sqlCommand.CommandText = loadCitySQL;//set command SQL
sqlCommand.Connection = sqlConnection;//set command Connection
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader()) {
while (sqlDataReader.Read()) {
//parce data from DataReader
string state = string.Empty;
string stateId = string.Empty;
if (sqlDataReader["StateName"] != null
&& sqlDataReader["StateName"] != DBNull.Value) {
state = sqlDataReader["StateName"].ToString();
}
if (sqlDataReader["StateId"] != null
&& sqlDataReader["StateId"] != DBNull.Value) {
stateId = sqlDataReader["StateId"].ToString();
}
//create SuggestionItem
SuggestionItem suggestionItem = new SuggestionItem();
suggestionItem.Title = state;
suggestionItem.Id = stateId;
//add item to the list
items.Add(suggestionItem);
}
}
sqlConnection.Close();
}
return SuggestionItem.SuggestionArrayToJSON(items.ToArray(), tryValue);
}
ASP.NET AJAX AutoComplete Control Demonstration.
Start typing in the textboxes below. Note that County is filtered by state and City is filtered by County boxes.
The state box shows suggestions even if the box is empty. Also it allows to select more than one state.
ConvincingMail AutoComplete Control Description
ConvincingMail AutoComplete is an ASP.NET AJAX extender that can be attached to any TextBox control, and will associate that control with a popup list to display items that returend by the webservice for the prefix typed into the textbox.
The list with suggested items supplied by a web service is positioned under the textbox.
In the sample above, the textbox is associated with a ConvincingMail AutoComplete extender that pulls States, Counties and Cities. Start typing in the textbox to see a list of suggested items returned by a webservice.
The County suggestions are filtered by the State value and City suggestions are filtered by County value.
ConvincingMail AutoComplete Properties & Usage
Control setup: To use the control on the asp.net page you need to follow this steps:
- Step 1: Add ConvincingMail AutoComplete dll refference to your project.
- Step 2: Declare control refference on your page. e.g.:
<%@ Register Assembly="ConvincingMail.AdvancedAutoSuggest" Namespace="ConvincingMail.AdvancedAutoSuggest" TagPrefix="cc1" %>
- Step 3: Add Prototype JS Framework reference to your page.
- Step 4: Add the control to your page and set properties. e.g.:
<cc1:AdvancedAutoSuggestExtender TargetControlID="CountyTextBox" AdditionalFields="StateIdTextBox" UpdateField="CountyIdTextBox" TitleTdCss="titleTd-green" TitleTrCss="titleTr-green" CommentsTdCss="commentsTd-green" CommentsTrCss="commentsTr-green" HilightedTrCss="suggestionsHilightedTr-green" ServiceUrl="~/Suggestions.asmx/CountySuggest" ID="AdvancedAutoSuggestExtender1" runat="server" /%> TargetControlID -- Required. The TextBox control where the user types content to be automatically completed. ServiceUrl -- Required. URL of the webservice which returns suggestions. AdditionalFields -- Optional. Comma separated list of controls which should be used to filter results. UpdateField -- Optional. The TextBox (HiddenTextBox) control where the ID value of selected item is set. TitleTdCss -- Optional. CSS style of the suggestion item title cell. TitleTrCss -- Optional. CSS style of the suggestion item title row. CommentsTdCss -- Optional. CSS style of the suggestion item comments title cell. CommentsTrCss -- Optional. CSS style of the suggestion item comments title row. HilightedTrCss -- Optional (v2.1). CSS style of the suggestion hilighted item (mouse over). SuggestOnEmptyField -- Optional. When true the control makes a server call for suggestions when the text box is empty. (default: false). OnClientItemSelected -- Optional, (v2.1). ClientSide Javascript function to call when an item is selected from the suggestions list. The function accepts two params: sender and item. The item param is the selected suggestion item has the following properties:
- Id - An ID value of the selected item.
- Title - A highlighted title of the selected item. (like it appeares in the suggestion list)
- Description - Selected item description. (like it appeares in the suggestion list)
- TitleValue - Original title of the item. (no hilighting tags)
Suggestions WebService:
- Step 1: -- Create new webservice (.asmx) in your project. e.g.: Suggestions.asmx
- Step 2: -- Create the method to return a JSON list of items. View Code Example
Hi all New version released today. (v2.1) added SuggestOnEmptyField and OnClientItemSelected. Now you can allow the suggestions for an empty field and call your custom client function to process the selected item.
Check the state box on this page.
If you have questions or need help please use our forum. Thanks |
|
Don't know why, but i always receive an "Unknown server tag" error. got the .dll in my /bin/ folder and done step2, but it doesn't work... |
|
Hi Arro I'm working on the new version of the control. Check more info & ideas in the forum.
You can put a validator on the ID field and require it to be populated.
It is hard to determine why the down arrow doesn't work for you. Can you past your page where the error happens?
Thanks. |
|
Hi again, one thing I noticed that in your AdvancedAutoSuggestDemo - the down key doesnt work to access the list. but on your page it does.
Also I was wondering if it would be possible to make tab work like down until the user has selected from the list.
I really need for the user to select something in order to get the ID. |
|
Hi Arro you are right. To populate the id box the value should be selected from the list by mouse click or up/down enter arrows. The Tab button just moves the focus to the next element of the page like usual drop down list box.
Thanks |
|
thanks - this works really well... one thing I'd like to see improved is when I'm in the State box and type in Kansas, kansas shows up in the pick list. I then hit TAB and the ID textbox is not populated.
Perhaps I need to handle that differently ? |
|
| Ah I didn't realize that my webservice had to actually be called "Suggestions.asmx" - I had used my own webservice name and that was the problem. Very nice work! |
|
Hi Darren 1. Check ServiceUrl="~/Suggestions.asmx/MethodName" property. It should point to your .asmx file and MethodName should be ListWorldLocationsForAutoComplete in your case. 2. The server side method should be described with WebMethod() and ScriptMethod() attributes.
Thanks.
P.S. To get more help please use our forum. |
|
When I perform the exact steps you outlined above, I can't get the textbox to call my webservice function. I'm pretty certain I'm doing everything correctly but I'm stuck. The signature of my webservice function is (in VB)
Public Function ListWorldLocationsForAutoComplete(ByVal String, ByVal string()) as String
I put a breakpoint in that function and it never gets hit.
Any suggestions? |
|
|
|
|
|
Thank you Mojtaba Vali for your comment! With this control you can force user to select an exact value from the list and its Id will be set in a hidden box.
For example: There are two "Duval" counties in USA in Florida and Texas. So when a user types "duv" in county field there are two items in the list appear one for Florida and the other for Texas.
Also using this control you can display more info with every suggestion item. |
|
| it is a nice control but what's its advantage to ajax control toolkit autocomplete extender?
|
|
|
|
Really cool extender Thank you! |
|
Leave a Comment
|