Cascading DropDownList on GridView with Editing


Suprotim Agarwal posted a cascading dropdowonlist with gridview on this http://www.dotnetcurry.com/ShowArticle.aspx?ID=221

The key is to use FilterExpression. It is handy without any code needed. However, it will be more useful if the solution can apply to update gridview for the cascading dropdownlist. Sadly, we still need some code to handle the update values from these dropdownlist.

Here is a sample for the implementation:

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" OnRowUpdating="GridView1_RowUpdating"

AutoGenerateColumns="false" DataKeyNames="CustomerId" >

<Columns>

<asp:CommandField ShowEditButton="True" />

<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />

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

<EditItemTemplate>

<asp:DropDownList ID="ddlManufacture" runat="server" AutoPostBack="True" DataTextField="Manufacture"

DataValueField="Manufacture" Width="122px" SelectedValue=’<%# Bind("Manufacture") %>

DataSourceID="SqlDataSource2" >

</asp:DropDownList>

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:forumConnectionString %>"

SelectCommand="SELECT [Manufacture] FROM Manufactures"></asp:SqlDataSource>

</EditItemTemplate>

<ItemTemplate>

<asp:Label ID="Label2" runat="server" Text=’<%# Bind("Manufacture") %>‘></asp:Label>

</ItemTemplate>

</asp:TemplateField>

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

<EditItemTemplate>

Model:

<asp:DropDownList ID="ddlModel" runat="server" DataSourceID="SqlDataSource3" DataTextField="Model" DataValueField="Model" >

</asp:DropDownList>

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="ddlModel" InitialValue="" ErrorMessage="Select a Model" ></asp:RequiredFieldValidator>

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:forumConnectionString %>"

SelectCommand="SELECT [Model],[Manufacture] FROM [Models] " FilterExpression="[Manufacture]= ‘{0}’">

<FilterParameters><asp:ControlParameter ControlID="ddlManufacture" Name="Manufacture" PropertyName="SelectedValue" Type="String" />

</FilterParameters>

</asp:SqlDataSource>

</EditItemTemplate>

<ItemTemplate>

<asp:Label ID="Label1" runat="server" Text=’<%# Bind("Model") %>‘></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"

DeleteCommand="DELETE FROM [Customers] WHERE [CustomerId] = @CustomerId"

InsertCommand="INSERT INTO [Customers] ([Name], [Manufacture], [Model]) VALUES (@Name, @Manufacturer, @Model)"

SelectCommand="SELECT [CustomerId], [Name], [Manufacture], [Model] FROM [Customers] WHERE [Manufacture] IS NOT NULL AND [Model] IS NOT NULL"

UpdateCommand="UPDATE [Customers] SET [Name] = @Name, [Manufacture] = @Manufacture, [Model] = @Model WHERE [CustomerId] = @CustomerId">

<DeleteParameters>

<asp:Parameter Name="CustomerId" Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource>

protected

void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

//string strModel = ((DropDownList)((GridView)sender).Rows[e.RowIndex].FindControl("ddlModel")).SelectedItem.Value;

string strModel = ((DropDownList)((GridView)sender).Rows[e.RowIndex].FindControl("ddlModel")).SelectedValue;

string strManufacturer = ((DropDownList)((GridView)sender).Rows[e.RowIndex].FindControl("ddlManufacture")).SelectedItem.Value;

e.NewValues[

"Model"] = strModel;

e.NewValues[

"Manufacture"] = strManufacturer;

e.Cancel =

false;

GridView gv = (GridView)sender;

for (int i = 0; i < gv.Columns.Count; i++)

{

DataControlFieldCell cell = gv.Rows[e.RowIndex].Cells[i] as DataControlFieldCell;

gv.Columns[i].ExtractValuesFromCell(e.NewValues, cell,

DataControlRowState.Edit, true);

}

 

}

Advertisements

7 Comments on “Cascading DropDownList on GridView with Editing”

  1. SQL says:

    Thanks Jingyang, this is very close to what I was needing, however, it doesn\’t work for my situation. In my case, I would like to be able to add the following to a second dropdownlist: SelectedValue=\'<%# Bind("Model") %>\’ When I attempt to do this, it works initially, but as soon as I select a different value from the first dropdownlist, I get the following error:"Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control"I have done a lot of searching and I think that the solution is to handle all of this with Code-behind, for example, by creating the 2nd dropdownlist in the code-behind (C#) when the selectedindexchange event fires for the first dropdownlist. I haven\’t yet written the code, but I will probably base it on what I found here:http://www.webswapp.com/categories/ViewSourceCode.aspx?id=ASPNET20CascadingListsC-GridViewIf you know a simpler way, I would love to hear it. Thanks.

  2. ibrahim says:

    Thank you very much.Very usuful an article for me.Thanks.

  3. Pail Writer says:

    This example works however, when you click on “edit” it does not retain the current value of the model. It resets it to the value at the top of the drop-down list for that manufacturer.
    Is there a way to retain the selected values in the manufacturer and model list when you click on “edit”? Sometimes you just want to change the value in the “Name” column or other columns and keep the manufacturer and model values as they were.
    Any help on this would be greatly appreciated.
    Thanks.

    • Jingyang Li says:

      Hi Pail,
      Thanks for posting question. I am not working on this any more. Please post your question at asp.net forum and you will get some answers there. Thanks.

      • Pail Writer says:

        Thanks for the reply.
        As for the asp.net forum, been there – done that.

        The only reply I received back worked for keeping the drop-down list values during “edit”. However, it moved the SELECT, UPDATE and DELETE commands from the “sqlDataSource” on the default.aspx page and called it out on the default.aspx.cs page. This took the ability to edit other columns out of the picture.

        Since I have other columns, filters, etc.. in the sqlDataSource on the main page, I need the sqlconnection properties to stay there. Unlike the simple solutions on most posts, I am in the real world and my GridView does a lot more than just display a couple columns that cascade.
        That’s where I am having the trouble with this whole process.

        On a side note, I have Googled the heck out of this question and always get the same results; cascading in GridView not holding its current value on edit.

        I would buy a book on GridView if I could find one.
        Any idea who I could talk to directly about this issue.
        Thanks.

      • Jingyang Li says:

        Sorry I just don’t have the setup any more. If you have SQL Server questions, please let me know and I may be able to help. Thanks.

      • Pail Writer says:

        While we are on the subject of SQL Server, I am trying to study for the 70-461 exam.
        Currently I have the “Microsoft – Querying Microsoft SQL Server 2012 Exam 70-461 Training Kit” for studying. I noticed mixed reviews about the books ability to prepare you for the actual exam. A lot of reviews say the practice test on the CD are nowhere near like the actual exam.
        I also have the “Wrox” book “Beginning Microsoft SQL Server 2012”
        Is there any material you would recommend to actually prepare for (and pass) the exam?
        Thanks.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s