.png)
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 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
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
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
Now in html source of aspx page I've put two dropdowns in EditItemTemaplate of gridview
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
The complete html source is like this
Finally write this code in code behind of aspx page to update record
Hope this helps
I wrote similar article in case you don't use ajax , than u can go through this
Download the sample code attached
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
<%@ 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.
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
Download the sample code attached
0 comments: