Ajax Cascading DropDownList With Database Example in GridView

This example implements Ajax Cascading DropDownList With Database In GridView Using Asp.Net 2.0,3.5,4.0. There are several cases when you have two or three dropdowns in gridview and want second one (and third one) to be populated based on selection of first or second dropdownlist.

I've used Ajax cascading dropdownlist in EditItemTemaplete of GridView for updation of records in grid by fetching data from database to populate dropdowns,I've also implemented ajax auto complete extender textbox in it to edit name field

Make sure you have created ajax enabled website and installed ajax toolkit and ajax web extensions properly

Gridview displays Name, City, and Country on page load,



City and Country field turns into cascading dropdown list when user clicks on Edit link


Ajax cascading dropdown extender uses webservice to fetch data from database and populate dropdowns, city dropdown is populated based on country selected in country dropdown

Important points to remember

1. Put AjaxControlToolkit.dll in bin folder of your application.
2. Set EventValidation to false in page directive of your aspx page

<%@ Page Language="C#" EnableEventValidation="false"
AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="_Default" %>

Here is explanation why you need to turn it off ?
in order for the values to be submitted, EventValidation needs to be disabled for the page. EventValidation ensures that the values in each control match the values that were present when the page was rendered, but since these drop downs are populating on the client side, this is never true.

3. If you are getting Error method 500 or 12031 than read this to resolve this error

4. Webservice must have the webmethod with following signature and exact parameters

[WebMethod]
public CascadingDropDownNameValue[] GetColorsForModel(
string knownCategoryValues,
string category)

You can change the method name but return type must be CascadingDropDownNameValue[] with knownCategoryValues,category as parameters

First of all add a new webservice and name it CascadingDropDown.asmx
In code behind of this asmx file write following code
Add these namespaces

using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using AjaxControlToolkit;
using System.Collections.Specialized;

/// <summary>
/// Summary description for CascadingDropDown
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService()]
public class CascadingDropDown : System.Web.Services.WebService
{
//Create global Connection string
string strConnection = ConfigurationManager.ConnectionStrings
["dbConnectionString"].ConnectionString;

public CascadingDropDown () {

//Uncomment the following line if using designed components 
//InitializeComponent(); 
}
/// <summary>
/// WebMethod to populate country Dropdown
/// </summary>
/// <param name="knownCategoryValues"></param>
/// <param name="category"></param>
/// <returns>countrynames</returns>
[WebMethod]
public CascadingDropDownNameValue[] GetCountries
(string knownCategoryValues, string category)
{
//Create sql connection and sql command
SqlConnection con = new SqlConnection(strConnection);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "Select * from Country";

//Create dataadapter and fill the dataset
SqlDataAdapter dAdapter = new SqlDataAdapter();
dAdapter.SelectCommand = cmd;
con.Open();
DataSet objDs = new DataSet();
dAdapter.Fill(objDs);
con.Close();

//create list and add items in it 
//by looping through dataset table
List<CascadingDropDownNameValue> countryNames
= new List<CascadingDropDownNameValue>();
foreach (DataRow dRow in objDs.Tables[0].Rows)
{
string countryID = dRow["CountryID"].ToString();
string countryName = dRow["CountryName"].ToString();
countryNames.Add(new CascadingDropDownNameValue
(countryName, countryID));
}
return countryNames.ToArray();


}

[WebMethod]
public CascadingDropDownNameValue[] GetCities
(string knownCategoryValues, string category)
{
int countryID;
//this stringdictionary contains has table with key value
//pair of cooountry and countryID
StringDictionary countryValues =
AjaxControlToolkit.CascadingDropDown.
ParseKnownCategoryValuesString(knownCategoryValues);
countryID = Convert.ToInt32(countryValues["Country"]);

SqlConnection con = new SqlConnection(strConnection);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@CountryID", countryID);
cmd.CommandText =
"Select * from City where CountryID = @CountryID";

SqlDataAdapter dAdapter = new SqlDataAdapter();
dAdapter.SelectCommand = cmd;
con.Open();
DataSet objDs = new DataSet();
dAdapter.Fill(objDs);
con.Close();
List<CascadingDropDownNameValue> cityNames =
new List<CascadingDropDownNameValue>();
foreach (DataRow dRow in objDs.Tables[0].Rows)
{
string cityID = dRow["CityID"].ToString();
string cityName = dRow["CityName"].ToString();
cityNames.Add(new CascadingDropDownNameValue
(cityName, cityID));
}
return cityNames.ToArray();
}

}

Now in html source of aspx page I've put two dropdowns in EditItemTemaplate of gridview

<EditItemTemplate>
<asp:DropDownList ID="ddlCountry" runat="server">
</asp:DropDownList><br />
<ajaxToolkit:CascadingDropDown ID="CascadingDropDown1"
runat="server"
Category="Country"
TargetControlID="ddlCountry"
PromptText="-Select Country-"
LoadingText="Loading Countries.."
ServicePath="CascadingDropDown.asmx"
ServiceMethod="GetCountries">
</ajaxToolkit:CascadingDropDown>
</EditItemTemplate>

Here TargetControlID is id of dropdown on which cascading dropdown is to be implemented ,Service path is path to webservice and ServiceMethod is method to fetch the data from databse and populate dropdown

You also need to add reference to webservive in script manager

<asp:ScriptManager ID="ScriptManager1" runat="server">
<Services>
<asp:ServiceReference Path="CascadingDropDown.asmx" />
</Services>
</asp:ScriptManager>

The complete html source is like this

<%@ Page Language="C#" EnableEventValidation="false"
AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
<Services>
<asp:ServiceReference Path="AutoComplete.asmx" />
<asp:ServiceReference Path="CascadingDropDown.asmx" />
</Services>
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
DataSourceID="SqlDataSource1"
OnRowUpdating="GridView1_RowUpdating">

<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:TemplateField HeaderText="ID" SortExpression="ID">
<ItemTemplate>
<asp:Label ID="lblID" runat="server"
Text='<%#Eval("ID") %>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblID" runat="server"
Text='<%#Bind("ID") %>'>
</asp:Label>
</EditItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Name"
SortExpression="Name">
<ItemTemplate>
<asp:Label ID = "lblName" runat="server"
Text='<%#Eval("Name") %>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtName" runat="server"
Text='<%#Bind("Name") %>' >
</asp:TextBox>
<ajaxToolkit:AutoCompleteExtender
runat="server"
ID="autoComplete1"
TargetControlID="txtName"
ServicePath="AutoComplete.asmx"
ServiceMethod="GetCompletionList"
MinimumPrefixLength="1"
CompletionInterval="10"
EnableCaching="true"
CompletionSetCount="12" />
</EditItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Country"
SortExpression="Country">
<ItemTemplate>
<asp:Label ID="lblCountry" runat="server"
Text='<%#Eval("Country") %>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddlCountry" runat="server">
</asp:DropDownList>
<ajaxToolkit:CascadingDropDown
ID="CascadingDropDown1"
runat="server"
Category="Country"
TargetControlID="ddlCountry"
PromptText="-Select Country-"
LoadingText="Loading Countries.."
ServicePath="CascadingDropDown.asmx"
ServiceMethod="GetCountries">
</ajaxToolkit:CascadingDropDown>
</EditItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="City"
SortExpression="City">
<ItemTemplate>
<asp:Label ID="lblCity" runat="server"
Text='<%#Eval("City") %>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddlCity" runat="server"
OnSelectedIndexChanged="ddlCity_SelectedIndexChanged">
</asp:DropDownList><br />
<ajaxToolkit:CascadingDropDown
ID="CascadingDropDown2"
runat="server"
Category="City"
TargetControlID="ddlCity"
ParentControlID="ddlCountry"
PromptText="-Select City-"
LoadingText="Loading Cities.."
ServicePath="CascadingDropDown.asmx"
ServiceMethod="GetCities">
</ajaxToolkit:CascadingDropDown>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:dbConnectionString %>"
SelectCommand="SELECT [ID], [Name], [City],[Country]
FROM [Location]"
UpdateCommand="Update Location set [Name] = @Name,
[City] = @City,[Country] = @Country
where ID = @ID">
<UpdateParameters>
<asp:Parameter Name="Name" />
<asp:Parameter Name="ID" />
<asp:Parameter Name="Country"/>
<asp:Parameter Name="City"/>
</UpdateParameters>
</asp:SqlDataSource>

</div>
</form> 
</body>
</html>

Finally write this code in code behind of aspx page to update record

protected void GridView1_RowUpdating
(object sender, GridViewUpdateEventArgs e)
{
//Find dropdown to get selected Item text  
DropDownList ddlGridCountry = (DropDownList)
GridView1.Rows[e.RowIndex].FindControl("ddlCountry");
string strCountry =
ddlGridCountry.SelectedItem.Text.ToString();

DropDownList ddlGridCity = (DropDownList)
GridView1.Rows[e.RowIndex].FindControl("ddlCity");
string strCity =
ddlGridCity.SelectedItem.Text.ToString();

SqlDataSource1.UpdateParameters.Clear();
SqlDataSource1.UpdateParameters.Add
("Country", strCountry);
SqlDataSource1.UpdateParameters.Add("City", strCity);
}

Hope this helps

I wrote similar article in case you don't use ajax , than u can go through this

1. C#.NET Articles -Cascading DropDownList Populate dropdown based on selection of other dropdown in ASP.NET

2. Populating dropdown based on the selection of first drop down in DetailsView using FindControl and ItemTemplate

Download the sample code attached

Tweet

If you like this post than join us or share