Dot Net Fever: Ajax Cascading DropDownList With Database Example in GridView

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.

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();

}

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

<title>Untitled Page</title>

</head>

<body>

<asp:ScriptManager ID="ScriptManager1" runat="server">

<asp:ServiceReference Path="AutoComplete.asmx" />

<asp:ServiceReference Path="CascadingDropDown.asmx" />

</Services>

</asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<asp:GridView ID="GridView1" runat="server"

AutoGenerateColumns="False"

DataSourceID="SqlDataSource1"

OnRowUpdating="GridView1_RowUpdating">

<asp:CommandField ShowEditButton="True" />

<asp:TemplateField HeaderText="ID" SortExpression="ID">

<asp:Label ID="lblID" runat="server"

Text='<%#Eval("ID") %>'>

</asp:Label>

</ItemTemplate>

<asp:Label ID="lblID" runat="server"

Text='<%#Bind("ID") %>'>

</asp:Label>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name"

SortExpression="Name">

<asp:Label ID = "lblName" runat="server"

Text='<%#Eval("Name") %>'>

</asp:Label>

</ItemTemplate>

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

<asp:Label ID="lblCountry" runat="server"

Text='<%#Eval("Country") %>'>

</asp:Label>

</ItemTemplate>

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

<asp:Label ID="lblCity" runat="server"

Text='<%#Eval("City") %>'>

</asp:Label>

</ItemTemplate>

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

<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 will helps you.

Dot Net Fever

Thursday, 26 July 2012

Ajax Cascading DropDownList With Database Example in GridView

2 comments:

Dot Net Lovers

Followers