Visual Basic 2005: A Developer's Notebook

Display Records One at a Time

While the GridView control is a perfect tool for presenting the records of a database as rows of data in a table, it becomes less convenient when you have records with many fields (especially if some fields are quite long), and you want to let users manipulate or add to the data they contain. One solution is to show only selected fields in a grid, but there are times when you need to display an entire record on a page and give the user the ability both to edit individual records and to add new records to the database. In ASP.NET, the handy new DetailsView gives you all the functionality you need to deal with individual records for free (i.e., without having to write your own code).

Note

The new DetailsView control gives you a convenient way to let users view, edit, insert, and delete individual records.

How do I do that?

The new DetailsView control works in much the same way as the GridView control described in the previous lab, "Display Interactive Tables Without Writing Code." The difference is that the DetailsView displays a single record at a time. By default, all the fields are displayed in a table, each field in a row of its own, listing from top to bottom.

To add a DetailsView to a web page, simply drag it onto the design surface from the Visual Studio Toolbox Data tab. Next, click its smart tag and select Configure Data Source to attach it to a data source control. You can also use the Auto Forms link in the smart tag to apply a rich set of styles to the grid it displays.

Because the DetailsView can only show a single record, you need to take extra steps to make sure it shows the right one. To do this, you need to use a filter expression (a SQL expression that limits the records you see according to the criteria you specify). You add the filter expression to the data source by setting the FilterExpression and FilterParameters properties of the DetailsView.

For example, consider the page that is shown in Figure 4-7. GridAndDetails.aspx contains both a GridView showing select information about the first five records and a DetailsView showing all fields of the selected record.

Figure 4-7. Connecting a GridView and DetailsView

This page needs two data sources, one for the GridView (which is defined in the same way as described in the lab "Display Interactive Tables Without Writing Code.") and one for the DetailsView. The DetailsView data source definition looks like this:

<asp:SqlDataSource ID="SingleCustomerSource" Runat="server"
  SelectCommand="SELECT CustomerID, CompanyName, ContactName, ContactTitle,
Address, City, Country FROM Customers WHERE CustomerID=@CustomerID"
  ConnectionString=
"Data Source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=Northwind"
>
    
  <SelectParameters>
               <asp:ControlParameter Name="CustomerID" ControlID="GridView1"
      PropertyName="SelectedValue">
               </asp:ControlParameter>
               </SelectParameters>

</asp:SqlDataSource>

This SELECT query selects only the single row that matches the CustomerID that's selected in the GridView control.

It's easy to hook up a basic DetailsView like the one shown in Figure 4-7. But life becomes even better if you do the work to add editing, deleting, and inserting abilities to the DetailsView. You can add all of these frills with the click of a button, provided you first make sure the connected data source has all the information it needs. For example, if you want to create a SqlDataSource that supports deleting, you need to configure the DeleteCommand and DeleteParameters properties. To create a data source that supports inserting new records, you need to add an InsertCommand and InsertParameters.

Adding these extra details is surprisingly easy. All you need to do is understand a few rules:

• All updates are performed through parameterized commands that use named placeholders instead of values.

• The parameter name is the same as the field name, with a preceding @ symbol. For example, the ContactName field becomes the @ContactName parameter.

• When you write the Where clause for your query, you need to precede the parameter name with the text original_. This indicates that you want to use the original value (which ignores any changes the user may have made). For example, @CustomerID becomes @original_CustomerID.

If you follow these rules, the DetailsView control will hook up the parameter values automatically. To try this out, follow these steps.

First, write a parameterized command that uses named placeholders instead of values. For example, here's a parameterized DeleteCommand for deleting the currently selected record, which follows the list of rules above:

DELETE Customers WHERE CustomerID=@original_CustomerID

This command deletes the currently selected record. The amazing thing about this command is that because it follows the naming rules listed above, you don't have to worry about supplying a value. Instead, you simply define the parameter as shown below, and the DetailsView will use the CustomerID from the currently displayed record:

<asp:SqlDataSource ID="SingleCustomerSource" Runat="server"
 DeleteCommand="DELETE Customers WHERE CustomerID=@original_CustomerID"
 ... >
  <DeleteParameters>
               <asp:Parameter Name="CustomerID">
               </asp:Parameter>
               </DeleteParameters>
  ...
</asp:SqlDataSource>

Example 4-2 shows a completed SqlDataSource that defines commands for update, insert, and delete operations in this way.

Example 4-2. A SqlDataSource tag

<asp:SqlDataSource ID="SingleCustomerSource" Runat="server"
  ConnectionString=
"Data Source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=Northwind"
  SelectCommand=
"SELECT CustomerID,CompanyName,ContactName,ContactTitle,Address,
City,Country FROM Customers"
  FilterExpression="CustomerID='@CustomerID'"
  DeleteCommand="DELETE Customers WHERE CustomerID=@original_CustomerID"
  InsertCommand=
"INSERT INTO Customers (CustomerID,CompanyName,ContactName,ContactTitle,Address,
City,Country) VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTile,@Address,
@City,@Country)"
  UpdateCommand=
  "UPDATE Customers SET CompanyName=@CompanyName,ContactName=@ContactName,
ContactTitle=@ContactTitle,Address=@Address,City=@City,Country=@Country WHERE
CustomerID=@original_CustomerID">
    
  <FilterParameters>
    <asp:ControlParameter Name="CustomerID" Type="String" ControlID="GridView1"
     PropertyName="SelectedValue">
    </asp:ControlParameter>
  </FilterParameters>
    
  <DeleteParameters>
    <asp:Parameter Name="CustomerID">
    </asp:Parameter>
  </DeleteParameters>
    
  <InsertParameters>
    <asp:Parameter Name="CustomerID"></asp:Parameter>
    <asp:Parameter Name="CompanyName"></asp:Parameter>
    <asp:Parameter Name="ContactName"></asp:Parameter>
    <asp:Parameter Name="ContactTile"></asp:Parameter>
    <asp:Parameter Name="Address"></asp:Parameter>
    <asp:Parameter Name="City"></asp:Parameter>
    <asp:Parameter Name="Country"></asp:Parameter>
  </InsertParameters>
    
  <UpdateParameters>
    <asp:Parameter Name="CompanyName"></asp:Parameter>
    <asp:Parameter Name="ContactName"></asp:Parameter>
    <asp:Parameter Name="ContactTitle"></asp:Parameter>
    <asp:Parameter Name="Address"></asp:Parameter>
    <asp:Parameter Name="City"></asp:Parameter>
    <asp:Parameter Name="Country"></asp:Parameter>
    <asp:Parameter Name="CustomerID"></asp:Parameter>
  </UpdateParameters>
    
</asp:SqlDataSource>

This tag is a long one, but the parameter definitions are surprisingly simple. Even better, Visual Studio wizards can help you build insert, update, and delete commands quickly. Just click the ellipsis next to the property name in the Properties window (e.g., the DeleteCommand property), and then type in the parameterized command and click Refresh Parameters. Refreshing automatically generates all the parameter tags based on your command.

To configure the DetailsView so that it uses these commands, just click the smart tag and add a checkmark next to the options Enable Inserting, Enable Deleting, and Enable Updating. This sets Boolean properties like AutoGenerateInsertButton, AutoGenerateDeleteButton, and AutoGenerateEditButton.

Figure 4-8 shows a DetailsView in edit mode.

Figure 4-8. Editing a record with the DetailsView

What about...

...updating the GridView so it stays synchronized with the DetailsView? If you don't take any extra steps you'll notice a little inconsistency; changes you make editing, inserting, or deleting records with the DetailsView won't appear in the GridView until you manually refresh the page. To get around this problem, you need to add a little event-handling code. In this case, the important DetailsView events are ItemInserted, ItemDeleted, and ItemUpdated, which fire after each of these edit operations has completed. Here's code you can add to each event handler to refresh the grid when an item is inserted, deleted, or updated:

Sub DetailsView1_ItemUpdated(ByVal sender As Object, _
  ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs)
    GridView1.DataBind( )
End Sub

The DetailsView has much more functionality that you can harness. For example, you can handle the ItemInserting, ItemDeleting, and ItemUpdating events to check the requested change, perform data validation, and stop the update from being committed. You can also create your own edit controls using templates. For more information about these techniques, look up the index entry "DetailsView control" in the MSDN Help.