Wednesday, 07 February 2007

In the previous GridView post, we figured out how to create a simple updating GridView without a lick of code.  But it only updated a value from a TextBox, nothing too difficult about that.  What would happen if one of the columns needed a DropDownList for editing purposes?  This is something that required code in ASP.NET 1.1. The DropDownList needed to be bound in the ItemDataBound event, and that was only for display purposes.  Updating required even more code.  Can the declarative code model handle this twist without resulting to actual -- gasp -- coding?

I'll save the suspense and admit that, in fact, it is possible to create an Editable, Updating GridView with a DropDownList  without writing any code.  Here it is.   We'll talk about the interesting bits afterwards.

       <asp:GridView ID="GridViewDropDown" runat="server" DataSourceID="TestSqlSource" AutoGenerateEditButton="True" DataKeyNames="NameID" AutoGenerateColumns="False" >
        <asp:BoundField HeaderText="Name" DataField="Name"  />

           <asp:TemplateField HeaderText="Popularity Trend">
                   <asp:DropDownList ID="PopularityTrendDropDown" runat="server"   SelectedValue='<%# Bind("PopularityID") %>' DataSourceID="PopularitySqlSource" DataTextField="Description"  DataValueField="PopularityID" ></asp:DropDownList>
                   <asp:Label ID="PopularityTrendLabel" runat="server" Text='<%# Eval("Description") %>'></asp:Label>
        <asp:SqlDataSource ID="TestSqlSource" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" SelectCommand="SELECT Name, NameID, PopularityID, Description FROM [BabyName] bn Left join Popularity p on bn.PopularityID = p.PopularityID" UpdateCommand="UPDATE BabyName SET Name = @Name, PopularityID = @PopularityID WHERE NameID = @NameID"  >
         <asp:SqlDataSource ID="PopularitySqlSource" runat="Server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" SelectCommand="SELECT PopularityID, [Description] FROM [Popularity]" >


There are now 2 data sources, one for the GridView, and the PopularitySqlSource for the PopularityTrendDropDown. The PopularitySqlSource just provides a simple list of the possible values to display in the dropdown. TestSqlSource's SelectCommand has a new join and and an extra column, Description.  This is so that the Item template can display the user friendly text value in the label.  I was searching for a sexier way of doing this, so I didn't have to include the join and column in the statement, but heck, as long as we're polluting the aspx with SQL statements might as well use all the SQL we can.

In order to use a DropDown in the GridView, we need to stop autogenerating the columns and create a TemplateField for the column that has the DropDown.   The TemplateField has two parts: the ItemTemplate and the EditItemTemplate.  The ItemTemplate simply shows the user friendly description of the PopularityTrend.  The EditItemTemplate holds the DropDownList for selecting the proper PopularityID. Whatever is in the EditItemTemplate is what shows up when the user clicks on the Edit button.

There is a little bit of data binding script syntax needed to properly bind the underlying values into the controls.  For the DropDownList we bind the SelectedValue to the PopularityID...

SelectedValue='<%# Bind("PopularityID") %>'

The bit bewteen <%# and %> is actually a call to the Bind method which sets up the control to do two-way binding so that it can keep track of updates.  This is used behind the scenes for the BoundField too.  Technically this is coding, but since we are not creating a separate code file or breaking out the <script> tags, we'll ignore it.

Similarly the Text property of the label is bound using the Eval method. 

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

The difference between Eval and Bind, is that Eval is a read-only binding, it can't be used to update the underlying datasource.

So all that is needed to create a GridView that uses a DropDownList to edit a column value is:

  1. A TemplateField to hold the DropDown when a row is being edited, and a label when it is not
  2. A DataSource for the list portion of the DropDown control
  3. A little extra SQL to get the friendly description for the label
  4. A wee bit of binding code syntax

So, still no code, but our GridView and DataSource declarations are getting heftier.  Probably still slimmer than the analogous code, but not nearly as compact as the simple GridView from the previous example.

Actually, if there were a DropDownField available the declarative syntax could be almost as slim as it was for the simple GridView.  I'm surprised that there wasn't a DropDownField class created for ASP.NET 2.0 given their frequent use for editing grid columns.  I'm sure someone has created one (or is is creating one) that could be added in. Could be a fun little project.

Next time, we'll see if we can add in multi-row editing without code.  I have my doubts.



Wednesday, 07 February 2007 23:20:59 (Eastern Standard Time, UTC-05:00)   #     Comments [0]  | 
Tuesday, 30 January 2007

Starting to get more questions about GridViews and SqlDataSources on the forums I haunt.  On the surface, these two components seem to offer a quick path to application development with no coding. 

And sure enough, once you figure them out, you don't have to do much to get an updatable GridView on the page.

Here's an example of a fully updating GridView...

<asp:GridView ID="NoCodeGridView" runat="server" DataSourceID="TestSqlSource" AutoGenerateEditButton="True"   DataKeyNames="NameID">

<asp:SqlDataSource ID="TestSqlSource" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" SelectCommand="SELECT * FROM [BabyName]" UpdateCommand="UPDATE BabyName SET Name = @Name WHERE NameID = @NameID">
                <asp:Parameter Name="Name" Size="50" Type="String" />
                <asp:Parameter Name="NameID" Type="Int32" />

The code behind file is strangely empty, yet clicking on a row's edit button brings up an editable row with an update and cancel button.  And clicking on the update button saves the altered baby name.  This scares and frightens me, because I know it won't be this easy once I need to do something real. While I appreciate that the details of implementation have been hidden from me, the fact that I can't see them also worries me. I'll leave that aside for the moment. I will also ignore the fact that I have embedded SQL statements in an ASPX page, and we'll move on and examine what's needed so the snippet above works.

The key in the GridView is this attribute:


This tells the GridView to generate the Edit button and Update/Cancel button. There are other ways to do this, but we're looking for the quickest, dirtiest way at the moment.

The real keys are in the SqlDataSource.  Without a select command nothing would be bound to the grid. And it obviously needs an update command, otherwise you get this exception...

Updating is not supported by data source 'TestSqlSource' unless UpdateCommand is specified.

Reading the stack trace for the exception shows you what ASP.NET is doing behind the scenes. 

   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +1828259
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues,    DataSourceViewOperationCallback callback) +78
   System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1215
System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +858
   System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +117
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

The last three are the interesting ones (the stack reads down from the last method called to the first).  The GridView's HandleEvent figures out an Update is going on and calls HandleUpdate, which in turn calls the Update method on the DataSourceView, which in turn calls the ExecuteUpdate on the SqlDataSourceView which does the real work. 

Let's take a little side trip and see what we can find out about these methods.

GridView.HandleUpdate is not a public method and hence does not appear in the documentation.

DataSourceView.Update is public.  DataSourceView is an abstract base class that alll the DataSourceView implementations are derived from.  This method calls the protected ExecuteUpdate method on that has been implemented by the inheriting class, in this case SqlDataSourceView.ExecuteUpdate. All pretty straightforward, and the SqlDataSourceView has some event handlers so it looks like there are places to hook in when actual programming is required.

Let's end our little side trip and get back into seeing what's needed for the little snippet to work.

One thing I noted was the documentation frequently mentions parameters, so they must be needed to make this work.  Let's pull out the <UpdateParameters> element and see what happens.

Begorra! The GridView does not throw an error and still updates.  It seems everything will be peachy if the update command parameter names correspond to columns in the select command. Let's run a wee test and change a parameter name in the update command. @Name will become @Name1

Didn't like that all

Must declare the variable '@Name1'.

So this is all you actually need...

     <asp:GridView ID="NoCodeGridView" runat="server" DataSourceID="TestSqlSource" AutoGenerateEditButton="True" DataKeyNames="NameID">
     <asp:SqlDataSource ID="TestSqlSource" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" SelectCommand="SELECT * FROM [BabyName]" UpdateCommand="UPDATE BabyName SET Name = @Name WHERE NameID = @NameID">

As long as the SqlDataSource's SelectCommand returns column names that correspond to the parameter names used in the UpdateCommand it all works.

Next time we'll see what happens when you need to do something a little fancier.

Tuesday, 30 January 2007 22:18:17 (Eastern Standard Time, UTC-05:00)   #     Comments [1]  | 

Theme design by Dean Fiala

Pick a theme: