Soure Code: Ajax Cascading DropDownList in GridView with database ASP.NET sample

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 and second dropdownlist.

In this example i've implemented Ajax cascading drop down list 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.
In this example gridview displays Name, City, and Country on page load.

And 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" %>

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 Edit Item Templates of grid view

<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 webservice 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 !

Soure Code

Ajax Cascading DropDownList in GridView with database ASP.NET sample

1 comments:

Categories

Blog Archive