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:


Database Schema


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


Index View 


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


Edit View


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.


 


GridView Markup


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:


 


DataSource Control Markup


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


Technorati Tags: ,
Comments (13)

  1. Thank you for submitting this cool story – Trackback from DotNetShoutout

  2. Dave Smith says:

    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 😉

  3. MikeOrmond says:

    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

  4. Dave Smith says:

    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.

  5. Dave Smith says:

    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

  6. MikeOrmond says:

    Hi Dave

    On the DetailsView, do you have the DefaultMode property set to "Insert"?

    Mike

  7. Dave Smith says:

    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>

  8. MikeOrmond says:

    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

  9. Chris says:

    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

  10. MikeOrmond says:

    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

  11. MikeOrmond says:

    Sorry about the formatting above – it didn’t look like that when I pasted it into the comments area :-). Mike

  12. Chris says:

    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!