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

If you like this post than join us or share

28 comments:

raviraj said...

ok


RRave said...

Dear Sir,

I have a launched new web site for .NET programming resources. www.codegain.com. I would like to invite to the codegain.com as author and supporter. I hope you will joins with us soon.

Thank You
RRaveen
Founder www.codegain.com


keyur said...

hello, Mr.Amit i have used this cascading dropdownlist sample code with webservice,it works fine if dropdownlist bound with 800 records,But when i have a 1 Lac Records then it shows [Method Error] after -select- in dropdownlist.so, kindly help me by some guid.


amiT jaiN said...

@keyur:

Hi keyur you need to add below mentioned code in your web.config file to get rid of record limit of cascading dropdown extender


<system.web.extensions>
<scripting>
<webServices>
<!-- Customize maxJsonLength -->
<jsonSerialization maxJsonLength="1000000"></jsonSerialization></webServices>
<!-- Optional -->
<scriptResourceHandler enableCompression="true" enableCaching="true" />
</scripting>
</system.web.extensions>


Hope this helps , do let me know whether it fix ur problem or not


keyur said...

hello,sir i have done all possible change according to you. but still same problem exist.

i have tried a lot.
please if you can,give me some other solution which fulfill cascading dropdown and selected index changed of that dropdown in grid view's all template.

Thanks
Keyur Gohel


amiT jaiN said...

@Keyur:

This is known issue of error 500 when cascading dropdown items are more than 800 but it get fixed by writing the code in web.config to increase JSON limit

<system.web.extensions>
<scripting>
<webServices>
<!-- Customize maxJsonLength -->
<jsonSerialization maxJsonLength="5000000"></jsonSerialization></webServices>
<!-- Optional -->
<scriptResourceHandler enableCompression="true" enableCaching="true" />
</scripting>
</system.web.extensions>

If this doesn't fix your problem than i would suggest you to get the latest version of AjaxControlToolkit.dll depending upon ur framework version from codeplex site


Sonu Pinghal said...

Hello sir,
i m using yours above code with some changes but get following error when i click on edit link button.

Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during the previous request. For example, when adding controls dynamically, the controls added during a post-back must match the type and position of the controls added during the initial request.


------------------------------------------------

i have done changes in Default.aspx.cs like this---->
public void bind()
{

SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=D:\\project\\Ajax_Cascading_Grid\\App_Data\\Cascading.mdf;Integrated Security=True;User Instance=True");
SqlDataAdapter da = new SqlDataAdapter("SELECT [ID], [Name], [City],[Country] FROM [Location]", con);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
bind();
}
protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
{
//lblMessage.Text = ddlCity.SelectedItem.Text;
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
string abc = GridView1.DataKeys[e.RowIndex].Value.ToString();

string name = ((TextBox)row.FindControl("txtName")).Text.ToString();
string country = ((DropDownList)row.FindControl("ddlCountry")).SelectedItem.Value.ToString();
string city = ((DropDownList)row.FindControl("ddlCity")).SelectedItem.Value.ToString();

SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=D:\\project\\Ajax_Cascading_Grid\\App_Data\\Cascading.mdf;Integrated Security=True;User Instance=True");
SqlCommand cmd = new SqlCommand("Update Location set [Name] = @Name,[City] = @City,[Country] = @Country where ID = @ID", con);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = abc;
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = name.Trim();
cmd.Parameters.Add("@City", SqlDbType.VarChar, 50).Value = country.Trim();
cmd.Parameters.Add("@Country", SqlDbType.VarChar, 50).Value = city.Trim();
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
bind();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
}





-----------------------------------------------
and Default.aspx like this---->
i just comment SqlDataSource code in Default.aspx



-----------------------------------------------

Plz. give me solution for it.
thnx.


amiT jaiN said...

@Sonu Pinghal:

Hi sonu , follow steps i mentioned below

Set EventValidation to false in page directive of your aspx page

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



Do let me know if it fixes your problem or not


Anonymous said...

Hello Amit, I tried your example in ASP.NET 3.5. Everything works fine, ie:data is bound, edit appears, but the GridView1_RowUpdating event is not fired. I tried to redo the solution in aspx code behind file, still the problem happens. Please help me...!


--Bijoy Viswanath


Bijoy Viswanath said...

Hello Amit,
Thanks for the article..i set the EnableEventValidation property in the @Page Directive to False and it worked. Since I created a new project i never noticed this property. When a new page is created this property is by default set to true, I suppose.

Thank You.


Anonymous said...

Hello, is there anywhere I can find this code in VB?


Anonymous said...

Keep posting stuff like this i really like it


Anonymous said...

Nice brief and this fill someone in on helped me alot in my college assignement. Thanks you seeking your information.


Laurent said...

Hi,

I have integrated your exemple in my apsx pages.
bu I have the error above:
Exception Details: System.Web.HttpException: The GridView 'GV_FACT' fired event RowEditing which wasn't handled.

I have set EnableEventValidation to "false" but the error still occurs.

Do you have any idea to solve that ?

Thanks


Dhananjay said...

Hey
thnx sir
really nice and simple
keep it up
have a good future!

Dhananjay
Aurangabad (Bihar),Faridabad (hr)


Anonymous said...

Genial brief and this mail helped me alot in my college assignement. Thank you seeking your information.


Anonymous said...

super sir


FishTBone said...

Thank you very much for the information. The code works great!

Sorry to bother you with a question, however I could use your help.

My grid view displays dropdownlists and textboxes for editing in the itemtemplates.

I can get the Country to display the default value but I cannot get the second ddl to display it's default value.

Any thoughts? Thank you for your help.


Anonymous said...

Hack again?!


Anonymous said...

Hi !!! Good job!


Anonymous said...

i have created,connected to sqlserver in dropdownlist. now i want coding

1. how to update dropdownlist and it affect database without using update button


Anonymous said...

Hi !!! Good job!
Wuzzap?


Anonymous said...

Thank you!!! You often have interesting posts! They put me in good spirits )


Anonymous said...

Hello. It is great that at least you pour more light on this issue. Thanks.


Anonymous said...

I think this post is interesting to read. Keep it going blogger


Anonymous said...

How can we select value in dropdown, in your example when user clicks update the dropdown should by default have amit as selected text.

Any idea, how to do this?


Anonymous said...

ASsxsa


Anonymous said...

Good one. I tried the examples in other sites, but this is easy to understand :)


Find More Articles