ASP.NET – Using a DropDownList for Editing in a Data Control

Maybe it's just me but every time I come to do this (usually about every 6 months as I build an ASP.NET demo) I run into problems so I thought I'd give an example here so I can use it again in September :-).

Imagine I have a page that displays a list of houses for sale in a Data Control (I'll use a GridView for simplicity). Perhaps each house has certain properties for which there is a discrete set of acceptable values, eg in my case the "Property Type" (Flat | Bungalow | Castle etc) and the "Town" (Maidenhead | Slough | Windsor etc). When a user switches the Data Control into Edit mode, I want to display the valid options in a DropDownList to assist the user's selection.

The data is coming from a database, the schema for which looks like this:

And I'd like an "Index" view that looks like this (thanks to Andreas Viklund for the template I'm using):

 

And an "Edit" view that looks like this (note the DropDownLists in the row being edited):

Of course it would be possible to do this in code but it's also possible to do this declaratively in ASP.NET. I've used SqlDataSource controls for simplicity but take your pick from any of the DataSource controls or roll your own query code. Here's what my GridView looks like – I've highlighted the interesting bits.

 

In the EditItemTemplates the DropDownLists point to a DataSource dedicated to querying for the relevant data (ie the list of Towns or HomeTypes). DataTextField / DataValueField allow us to display the TownName / TypeName but use the relevant ID as the DropDownList value. Finally we need to bind the SelectedValue so the initial value of the DropDownList is set correctly and changes are reflected on submit. In the ItemTemplate I just display the field value as text.

My DataSource controls are below:

 

Hope that serves as a useful reference for me at least.

Technorati Tags: asp.net,databinding

Comments

• Anonymous
  March 10, 2009
  PingBack from http://www.anith.com/?p=17155

• Anonymous
  March 11, 2009
  Thank you for submitting this cool story - Trackback from DotNetShoutout

• Anonymous
  March 16, 2009
  This is exactly what I've been searching for the last couple of weeks in my quest as a PHP developer to build my useful asp.net site for a client, can you apply this to the DetailsView as well to insert the data in the first place - seems like most tutorials and notes for asp.net both online and in books concentrate on how great and easy it is to view and update data with the GridView without starting with an example data entry form ;)

• Anonymous
  March 17, 2009
  Hi Dave. Yes, you should be able to take exactly the same approach with a DetailsView. Let me know if you run into problems with that and I'll post an update. Mike

• Anonymous
  March 17, 2009
  Hi Mike, Thanks very much, I think I'm just about there with it, I notice you can achieve the same with a FormView control.. I can't help but feel that it seems almost too easy ;) Dave.

• Anonymous
  March 19, 2009
  Hi Mike, The one problem I have had is with creating a data entry form that will appear when there is no previous data in the table, I note that the detailsview seems to disappear completely rather than leave an empty table retaining the "New" link at the bottom, is there any tutorial you can recommend that would explain how to get round this problem? ;) any help would be very much appreciated thanks and regards, Dave

• Anonymous
  March 19, 2009
  Hi Dave On the DetailsView, do you have the DefaultMode property set to "Insert"? Mike

• Anonymous
  March 19, 2009
  Hi Mike, Thanks for that, I have tried setting this, however it now doesn't display the record in the detailsview if the record exists lol        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server"            OldValuesParameterFormatString="original_{0}" SelectMethod="GetDailyEntries"            TypeName="TCS.DailyBerthTableAdapters.dailysituation2TableAdapter"            DeleteMethod="Delete" UpdateMethod="Update">            <DeleteParameters>                <asp:Parameter Name="Original_id" Type="Int32" />                <asp:Parameter Name="Original_date" Type="DateTime" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="date" Type="DateTime" />                <asp:Parameter Name="Original_id" Type="Int32" />                <asp:Parameter Name="Original_date" Type="DateTime" />            </UpdateParameters>        </asp:ObjectDataSource>        <asp:ObjectDataSource ID="BerthDataSource" runat="server"            OldValuesParameterFormatString="original_{0}" SelectMethod="GetDailyBerthById"            TypeName="TCS.DailyBerthTableAdapters.BerthEntriesTableAdapter"            UpdateMethod="GetDailyBerthById" InsertMethod="GetDailyBerthById">            <UpdateParameters>                <asp:Parameter Name="date" Type="String" />            </UpdateParameters>            <SelectParameters>                <asp:QueryStringParameter DefaultValue="1" Name="id"                    QueryStringField="id" Type="String" />            </SelectParameters>            <InsertParameters>                <asp:Parameter Name="date" Type="String" />            </InsertParameters>        </asp:ObjectDataSource>        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"            DeleteMethod="Delete" InsertMethod="Insert"            OldValuesParameterFormatString="original_{0}" SelectMethod="GetBerths"            TypeName="TCS.DailyBerthTableAdapters.shipsideberthsTableAdapter"            UpdateMethod="Update">            <DeleteParameters>                <asp:Parameter Name="Original_id" Type="Int32" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="berthname" Type="String" />                <asp:Parameter Name="Original_id" Type="Int32" />            </UpdateParameters>            <InsertParameters>                <asp:Parameter Name="berthname" Type="String" />            </InsertParameters>        </asp:ObjectDataSource>    </div>    <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"        DataKeyNames="id" DataSourceID="BerthDataSource" Height="50px"        Width="125px" DefaultMode="Insert">        <Fields>            <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False"                ReadOnly="True" SortExpression="id" />            <asp:TemplateField>                <HeaderTemplate>Date</HeaderTemplate>                <EditItemTemplate>                    <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="ObjectDataSource2" DataTextField="date" DataValueField="id" SelectedValue='<%# Bind("dailyid") %>'>                    </asp:DropDownList>                </EditItemTemplate>                <ItemTemplate><%# Eval("date") %></ItemTemplate>            </asp:TemplateField>            <asp:TemplateField>                <HeaderTemplate>Berth</HeaderTemplate>                <EditItemTemplate>                    <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ObjectDataSource1" DataTextField="berthname" DataValueField="id" SelectedValue='<%# Bind("berthid") %>'>                    </asp:DropDownList>                </EditItemTemplate>                <ItemTemplate><%# Eval("berthname") %></ItemTemplate>            </asp:TemplateField>            <asp:BoundField DataField="name" HeaderText="Name"                SortExpression="name" />            <asp:BoundField DataField="expectedarrival" HeaderText="Expected Arrival"                SortExpression="expectedarrival" />            <asp:BoundField DataField="expecteddeparture" HeaderText="Expected Departure"                SortExpression="expecteddeparture" />            <asp:CommandField ShowEditButton="True" ShowInsertButton="True" />        </Fields>    </asp:DetailsView>

• Anonymous
  March 19, 2009
  Hi Dave So what you want is editing and insert in the same details view but if there are no records in the DB then nothing displays (in Edit mode) is that right? How about you hook into the Selected event on your DataSource and check the number of records returned then switch the DetailsView mode based on that?    protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)    {      if (e.AffectedRows == 0)      {        DetailsView1.ChangeMode(DetailsViewMode.Insert);      }      else      {        DetailsView1.ChangeMode(DetailsViewMode.Edit);      }    } Mike

• Anonymous
  April 07, 2009
  Mike, this looks like exactly what I've been looking for. however, I'd love to see your Sproc for the select and update. I'm having trouble displaying my ID as the text so it can be updated without throwing an execption. Again, thanks for the good work! Chris

• Anonymous
  April 07, 2009
  Here you go. I don't have the ASP.NET code to hand (was a bit premature clearing out my temp folder) so haven't re-tested but my sprocs look like this: CREATE PROCEDURE [dbo].[spSelectHomesAll] AS BEGIN    SET NOCOUNT ON; SELECT [HomeID],    [Homes].[TypeID], [TypeName] AS [Type], [Bedrooms],        [Homes].[TownID], [TownName] AS [Town], [Lat], [Lon], [Description], [ImageURL], [Price], [Available], [SoldSTC]    FROM [Homes]    JOIN [HomeTypes] ON [Homes].[TypeID] = [HomeTypes].[TypeID]    JOIN [Towns] ON [Homes].[TownID] = [Towns].[TownID] END CREATE PROCEDURE [dbo].[spUpdateHomeUsingIDs] @HomeID int, @Bedrooms int, @Lat float, @Lon float, @ImageURL nvarchar(100), @Price int, @Description nvarchar(1000), @TypeID int, @TownID int, @Available bit, @SoldSTC bit AS BEGIN SET NOCOUNT ON; UPDATE Homes SET [Bedrooms] = @Bedrooms, [Lat] = @Lat, [Lon] = @Lon, [ImageURL] = @ImageURL, [Price] = @Price, [Description] = @Description, [TypeID] = @TypeID, [TownID] = @TownID, [Available] = @Available, [SoldSTC] = @SoldSTC WHERE HomeID = @HomeID END

• Anonymous
  April 07, 2009
  Sorry about the formatting above - it didn't look like that when I pasted it into the comments area :-). Mike

• Anonymous
  April 08, 2009
  Mike, you are my HERO! I've lost so much time in my life trying to do this via sql and you showed me the easy and best way. I still have to clean up my code but it works! I don't know what else to say except THANK YOU! I'll be posting a link to your blog when I see this question come up in the forums. Much appreciated!