In this article I will explain with an example, how to implement Cascading Autocomplete TextBoxes from database using jQuery in ASP.Net with C# and VB.Net.
The three country, state and city cascading or dependent Autocomplete TextBoxes will be implemented using jQuery AJAX and WebMethods in ASP.Net.
Database
Three tables Countries, State and City are created with the following schema.
Countries Table
![Implement Cascading Autocomplete TextBoxes from database using jQuery in ASP.Net]()
States Table
![Implement Cascading Autocomplete TextBoxes from database using jQuery in ASP.Net]()
Cities Table
![Implement Cascading Autocomplete TextBoxes from database using jQuery in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The HTML Markup consists of three ASP.Net TextBoxes (Country, State and City) which will be implemented as Autocomplete TextBoxes and three ASP.Net HiddenFields for holding the ID values for the Country, State and City.
There is also an ASP.Net Button which will be used for fetching the Text and the ID values for the selected Country, State and City.
<table border="0" cellpadding="0" cellspacing="5">
<tr>
<td>
Country
</td>
<td>
<asp:TextBox ID="txtCountry" runat="server" />
<asp:HiddenField ID="hfCountry" runat="server" />
</td>
</tr>
<tr>
<td>
State
</td>
<td>
<asp:TextBox ID="txtState" runat="server" Enabled="false" />
<asp:HiddenField ID="hfState" runat="server" />
</td>
</tr>
<tr>
<td>
City
</td>
<td>
<asp:TextBox ID="txtCity" runat="server" Enabled="false" />
<asp:HiddenField ID="hfCity" runat="server" />
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnSubmit" Text="Submit" runat="server" OnClick = "Submit" />
</td>
</tr>
</table>
Namespaces
You will need to import the following namespaces.
C#
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Web.Services
Imports System.Configuration
Imports System.Data.SqlClient
The WebMethods for populating Autocomplete TextBoxes
The following three WebMethods are used for populating the data for the Country, State and City Autocomplete TextBoxes.
Each WebMethod accepts two parameters i.e. Prefix and ParentId, Prefix is the search term entered in the Autocomplete TextBox while ParentId is the ID of the item selected in the parent Cascading Autocomplete, for example for State Autocomplete, the ParentId will be the CountryId of the selected Country.
The generic method PopulateAutoComplete accepts SqlCommand object as parameter and returns a List of string values for populating the Autocomplete TextBox.
C#
[WebMethod]
public static string[] GetCountries(string prefix, int parentId)
{
SqlCommand cmd = new SqlCommand();
string query = "SELECT CountryId, CountryName FROM Countries WHERE CountryName LIKE @Prefix + '%'";
cmd.Parameters.AddWithValue("@Prefix", prefix);
cmd.CommandText = query;
return PopulateAutoComplete(cmd);
}
[WebMethod]
public static string[] GetStates(string prefix, int parentId)
{
SqlCommand cmd = new SqlCommand();
string query = "SELECT StateId, StateName FROM States WHERE StateName LIKE @Prefix + '%' AND CountryId=@CountryId";
cmd.Parameters.AddWithValue("@Prefix", prefix);
cmd.Parameters.AddWithValue("@CountryId", parentId);
cmd.CommandText = query;
return PopulateAutoComplete(cmd);
}
[WebMethod]
public static string[] GetCities(string prefix, int parentId)
{
SqlCommand cmd = new SqlCommand();
string query = "SELECT CityId, CityName FROM Cities WHERE CityName LIKE @Prefix + '%' AND StateId=@StateId";
cmd.Parameters.AddWithValue("@Prefix", prefix);
cmd.Parameters.AddWithValue("@StateId", parentId); cmd.CommandText = query;
return PopulateAutoComplete(cmd);
}
private static string[] PopulateAutoComplete(SqlCommand cmd)
{
List<string> autocompleteItems = new List<string>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
autocompleteItems.Add(string.Format("{0}-{1}", sdr[1], sdr[0]));
}
}
conn.Close();
}
return autocompleteItems.ToArray();
}
VB.Net
<WebMethod()> _
Public Shared Function GetCountries(prefix As String, parentId As Integer) As String()
Dim cmd As New SqlCommand()
Dim query As String = "SELECT CountryId, CountryName FROM Countries WHERE CountryName LIKE @Prefix + '%'"
cmd.Parameters.AddWithValue("@Prefix", prefix)
cmd.CommandText = query
Return PopulateAutoComplete(cmd)
End Function
<WebMethod()> _
Public Shared Function GetStates(prefix As String, parentId As Integer) As String()
Dim cmd As New SqlCommand()
Dim query As String = "SELECT StateId, StateName FROM States WHERE StateName LIKE @Prefix + '%' AND CountryId=@CountryId"
cmd.Parameters.AddWithValue("@Prefix", prefix)
cmd.Parameters.AddWithValue("@CountryId", parentId)
cmd.CommandText = query
Return PopulateAutoComplete(cmd)
End Function
<WebMethod()> _
Public Shared Function GetCities(prefix As String, parentId As Integer) As String()
Dim cmd As New SqlCommand()
Dim query As String = "SELECT CityId, CityName FROM Cities WHERE CityName LIKE @Prefix + '%' AND StateId=@StateId"
cmd.Parameters.AddWithValue("@Prefix", prefix)
cmd.Parameters.AddWithValue("@StateId", parentId)
cmd.CommandText = query
Return PopulateAutoComplete(cmd)
End Function
Private Shared Function PopulateAutoComplete(cmd As SqlCommand) As String()
Dim autocompleteItems As New List(Of String)()
Using conn As New SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
cmd.Connection = conn
conn.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
autocompleteItems.Add(String.Format("{0}-{1}", sdr(1), sdr(0)))
End While
End Using
conn.Close()
End Using
Return autocompleteItems.ToArray()
End Function
Displaying GridView Selected Row in DetailsView control
The jQuery AutoComplete plugin has been applied to all the three TextBoxes i.e. Country, State and City. A jQuery AJAX call is made by each of these TextBoxes to their respective PageMethod (WebMethod) and the list of items returned from the PageMethod (WebMethod) acts as source of data for the jQuery Autocompletes.
The data received from the server is processed in the jQuery AJAX call success event handler. A loop is executed for each received item in the list of items and then an object with text part in the label property and value part in the val property is returned.
A Select event handler has been defined for the jQuery AutoComplete and when an item is selected from the AutoComplete List, the value of the item is stored in its respective HiddenField.
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.0.min.js" type="text/javascript"></script>
<script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js" type="text/javascript"></script>
<link href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css"
rel="Stylesheet" type="text/css" />
<script type="text/javascript">
$(function () {
$("[id$=txtCountry]").autocomplete({
source: function (request, response) {
AjaxCall("Default.aspx/GetCountries", request.term, 0, response)
},
select: function (e, i) {
$("[id$=hfCountry]").val(i.item.val);
$("[id$=txtState]").removeAttr("disabled");
$("[id$=txtState]").focus();
},
minLength: 1
});
$("[id$=txtState]").autocomplete({
source: function (request, response) {
AjaxCall("Default.aspx/GetStates", request.term, $("[id$=hfCountry]").val(), response)
},
select: function (e, i) {
$("[id$=hfState]").val(i.item.val);
$("[id$=txtCity]").removeAttr("disabled");
$("[id$=txtCity]").focus();
},
minLength: 1
});
$("[id$=txtCity]").autocomplete({
source: function (request, response) {
AjaxCall("Default.aspx/GetCities", request.term, $("[id$=hfState]").val(), response)
},
select: function (e, i) {
$("[id$=hfCity]").val(i.item.val);
},
minLength: 1
});
});
function AjaxCall(url, prefix, parentId, response) {
$.ajax({
url: url,
data: "{ 'prefix': '" + prefix + "', parentId: " + parentId + "}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
success: function (r) {
response($.map(r.d, function (item) {
return {
label: item.split('-')[0],
val: item.split('-')[1]
}
}))
},
error: function (r) {
alert(r.responseText);
},
failure: function (r) {
alert(r.responseText);
}
});
}
</script>
Fetching the selected item on Server Side
The Text and ID parts for the selected Country, State and City can be fetched on server side inside the click event handler of the Submit Button from the Request.Form collection as shown below.
C#
protected void Submit(object sender, EventArgs e)
{
string countryName = Request.Form[txtCountry.UniqueID];
string countryId = Request.Form[hfCountry.UniqueID];
string stateName = Request.Form[txtState.UniqueID];
string stateId = Request.Form[hfState.UniqueID];
string cityName = Request.Form[txtCity.UniqueID];
string cityId = Request.Form[hfCity.UniqueID];
string message = string.Format("Country: {0} {1}\\n", countryName, countryId);
message += string.Format("State: {0} {1}\\n", stateName, stateId);
message += string.Format("City: {0} {1}", cityName, cityId);
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('" + message + "');", true);
}
VB.Net
Protected Sub Submit(sender As Object, e As EventArgs)
Dim countryName As String = Request.Form(txtCountry.UniqueID)
Dim countryId As String = Request.Form(hfCountry.UniqueID)
Dim stateName As String = Request.Form(txtState.UniqueID)
Dim stateId As String = Request.Form(hfState.UniqueID)
Dim cityName As String = Request.Form(txtCity.UniqueID)
Dim cityId As String = Request.Form(hfCity.UniqueID)
Dim message As String = String.Format("Country: {0} {1}\n", countryName, countryId)
message += String.Format("State: {0} {1}\n", stateName, stateId)
message += String.Format("City: {0} {1}", cityName, cityId)
ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('" & message + "');", True)
End Sub
Screenshot
![Implement Cascading Autocomplete TextBoxes from database using jQuery in ASP.Net]()
Demo
Downloads
