Using the DataList ControlIn the following sections, you will examine in depth how to use the DataList control. You will learn how to use it to format and display data from a database table and create multicolumn menus . Finally, you will examine how to use the DataList to edit data in a database table. Displaying Data in a DataListYou can use a DataList like a Repeater control ”to display records from a database table. However, unlike a Repeater control, a DataList control's default behavior is to show the database records within an HTML table. The page in Listing 11.1, for example, illustrates how you can bind a DataList to a database table named Titles in the Pubs database. Listing 11.1 DataList.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand Dim dtrTitles As SqlDataReader conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select Title From Titles", conPubs ) conPubs.Open() dtrTitles = cmdSelect.ExecuteReader() dlstTitles.DataSource = dtrTitles dlstTitles.DataBind() dtrTitles.Close() conPubs.Close() End Sub </Script> <html> <head><title>DataList.aspx</title></head> <body> <form Runat="Server"> <asp:DataList ID="dlstTitles" Runat="Server"> <ItemTemplate> <%# Container.DataItem( "Title" )%> </ItemTemplate> </asp:DataList> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Each row from the Titles database table is displayed in a separate HTML table row when the DataList displays the records (see Figure 11.1). Figure 11.1. The DataList control.
If you prefer, you can modify the RepeatLayout property to display the items in a DataList within HTML <span> tags instead of HTML table cells . By default, the RepeatLayout property has the value Table . If you set RepeatLayout to Flow , each item is displayed in a <span> tag and is automatically separated by a <br> tag: <asp:DataList RepeatLayout="Flow" Runat="Server"> When RepeatLayout has the value Table , you can display lines around each cell by setting the GridLines property. For example, when GridLines has the value Both , both horizontal and vertical lines are drawn for each cell: <asp:DataList GridLines="Both" Runat="Server"> The other possible values for GridLines are Horizontal and Vertical . When RepeatLayout has the value Table , you also can control the padding in table cells and amount of spacing between table cells by assigning values to the CellPadding and CellSpacing properties: <asp:DataList GridLines="Both" CellPadding="20" CellSpacing="20" Runat="Server"> Using Templates with a DataListYou can use templates with a DataList to control how displayed items are formatted. For example, suppose that you want to display multiple database fields for each item displayed by the DataList . You can format the appearance of each DataList item by placing an HTML table in the ItemTemplate . The page in Listing 11.2 illustrates how to create an HTML table in the ItemTemplate (see Figure 11.2). Listing 11.2 DataListTemplate.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand Dim dtrAuthors As SqlDataReader conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select * From Authors", conPubs ) conPubs.Open() dtrAuthors = cmdSelect.ExecuteReader() dlstAuthors.DataSource = dtrAuthors dlstAuthors.DataBind() dtrAuthors.Close() conPubs.Close() End Sub </Script> <html> <head><title>DataListTemplate.aspx</title></head> <body> <form Runat="Server"> <asp:DataList ID="dlstAuthors" Runat="Server"> <HeaderTemplate> <h2>Author Phone Numbers:</h2> </HeaderTemplate> <ItemTemplate> <table> <tr> <td align="right">First Name:</td> <td><%# Container.DataItem( "au_fname" )%></td> </tr> <tr> <td align="right">Last Name:</td> <td><%# Container.DataItem( "au_lname" )%></td> </tr> <tr> <td align="right">Phone:</td> <td><%# Container.DataItem( "phone" )%></td> </tr> </table> </ItemTemplate> <SeparatorTemplate> <hr> </SeparatorTemplate> </asp:DataList> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.2. Using templates with a DataList .
Three templates are used in Listing 11.2: an ItemTemplate , a HeaderTemplate , and a SeparatorTemplate . The HeaderTemplate displays the heading Author Phone Numbers at the top of the DataList . The ItemTemplate displays each author's first name, last name, and phone number. The SeparatorTemplate renders a horizontal rule (an <hr> tag) between each item. Using Styles with a DataListThe DataList control supports seven style objects that you can use to format its output. Each style object is an instance of the TableItemStyle class, which has the following properties:
You can use any of these style properties with the AlternatingItemStyle , EditItemStyle , FooterStyle , HeaderStyle , ItemStyle , SelectedItemStyle , and SeparatorStyle styles of a DataList control. When you set a style property while declaring a control, you use a hyphen ( - ) instead of the normal period ( . ) to indicate a property of an object. The page in Listing 11.3, for example, displays each item in the DataList with a dashed border, and alternating items are displayed with a pink background (see Figure 11.3). Listing 11.3 DataListStyles.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conNorthwind As SqlConnection Dim cmdSelect As SqlCommand Dim dtrCategories As SqlDataReader conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) cmdSelect = New SqlCommand( "Select CategoryName From Categories", conNorthwind ) conNorthwind.Open() dtrCategories = cmdSelect.ExecuteReader() dlstCategories.DataSource = dtrCategories dlstCategories.DataBind() dtrCategories.Close() conNorthwind.Close() End Sub </Script> <html> <head><title>DataListStyles.aspx</title></head> <body> <form Runat="Server"> <asp:DataList id="dlstCategories" CellSpacing="20" CellPadding="10" ItemStyle-BorderStyle="Dashed" AlternatingItemStyle-BackColor="pink" Runat="Server"> <ItemTemplate> <%# Container.DataItem( "CategoryName" )%> </ItemTemplate> </asp:DataList> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.3. Using styles with a DataList .
Notice that you can set properties of the AlternatingItemStyle even when the DataList control does not include an AlternatingItemTemplate . Templates and styles are independent of each other. The Font property of the TableItemStyle class is a little more complicated than the other properties, so it merits separate discussion. The Font property represents an instance of the FontInfo class, which has the following properties:
So, to format the text displayed by each item in the DataList with a bold, italic, Script font, you would declare the DataList control like this: <asp:DataList ItemStyle-Font-Italic="True" ItemStyle-Font-Bold="True" ItemStyle-Font-Name="Script" Runat="Server"> Or, if you want to format alternating items with a blue, 14-point, Arial font, you would declare the DataList control like this: <asp:DataList AlternatingItemStyle-ForeColor="Blue" AlternatingItemStyle-Font-Name="Arial" AlternatingItemStyle-Font-Size="14pt" Runat="Server"> Creating Multiple Columns in a DataListOne nice feature of a DataList is that it enables you to display items in multiple columns. You control the column layout of a DataList by setting its RepeatColumns and RepeatDirection properties. The RepeatColumns property determines the number of columns to display. For example, if you want to display the items in a DataList in four columns, you would set this property to the value 4 . The RepeatDirection property determines whether columns should be repeated horizontally or vertically. By default, RepeatDirection has the value Vertical . So, if RepeatColumns has the value 4 , columns are displayed like this:
If you set RepeatDirection to the value Horizontal , and RepeatColumns has the value 4 , the columns are displayed like this:
Notice that four columns are displayed even when RepeatDirection has the value Vertical . RepeatColumns always refers to the number of columns to repeat, not the number of rows. The page in Listing 11.4 displays a menu of links in four columns, where the columns are repeated horizontally (see Figure 11.4). Listing 11.4 DataListMultiColumn.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim conNorthwind As SqlConnection Dim cmdSelect As SqlCommand Dim dtrProducts As SqlDataReader conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) cmdSelect = New SqlCommand( "Select ProductName From Products", conNorthwind ) conNorthwind.Open() dtrProducts = cmdSelect.ExecuteReader() dlstProducts.DataSource = dtrProducts dlstProducts.DataBind() dtrProducts.Close() conNorthwind.Close() End Sub </Script> <html> <head><title>DataListMultiColumn.aspx</title></head> <body> <form Runat="Server"> <asp:DataList ID="dlstProducts" RepeatColumns="4" RepeatDirection="Horizontal" CellSpacing="10" Runat="Server"> <ItemTemplate> <asp:LinkButton Text='<%# Container.DataItem( "ProductName" )%>' Runat="Server"/> </ItemTemplate> </asp:DataList> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.4. A multi-column DataList .
Capturing Events Raised in a DataList ControlAs mentioned in the overview section of this chapter, the DataList control supports event bubbling. You can capture events raised by controls contained within a DataList and handle them with a generic subroutine. The DataList control can raise the following five events in response to events raised by its child controls:
The Button , LinkButton , and ImageButton controls contained in a DataList can raise these events. For example, if you set a LinkButton control's CommandName property to the value delete , clicking the LinkButton raises the DeleteCommand event in the DataList . Notice that ItemCommand is the default event raised by the DataList . If a Button , LinkButton , or ImageButton control contained in a DataList is clicked and the control does not have a CommandName property, the DataList control's ItemCommand event is raised. The page in Listing 11.5 displays three LinkButton controls labeled Edit!, Delete!, and Update! for each item in the DataList . The three Button controls have different CommandName properties. When you click different Button controls, different events are raised in the DataList , and different subroutines are executed. Listing 11.5 DataListEvents.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not IsPostBack Then Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand Dim dtrTitles As SqlDataReader conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select Title From Titles", conPubs ) conPubs.Open() dtrTitles = cmdSelect.ExecuteReader() dlstTitles.DataSource = dtrTitles dlstTitles.DataBind() dtrTitles.Close() conPubs.Close() End If End Sub Sub DataList_ItemCommand( s As Object, e As DataListCommandEventArgs ) lblMessage.Text = "<li>Item Command!" End Sub Sub DataList_EditCommand( s As Object, e As DataListCommandEventArgs ) lblMessage.Text &= "<li>Editing Item!" End Sub Sub DataList_DeleteCommand( s As Object, e As DataListCommandEventArgs ) lblMessage.Text = "<li>Deleting Item!" End Sub Sub DataList_UpdateCommand( s As Object, e As DataListCommandEventArgs ) lblMessage.Text = "<li>Updating Item!" End Sub </Script> <html> <head><title>DataListEvents.aspx</title></head> <body> <form Runat="Server"> <asp:Label ID="lblMessage" BackColor="yellow" Runat="Server" /> <p> <asp:DataList ID="dlstTitles" OnItemCommand="DataList_ItemCommand" OnEditCommand="DataList_EditCommand" OnDeleteCommand="DataList_DeleteCommand" OnUpdateCommand="DataList_UpdateCommand" GridLines="Both" CellPadding="4" Runat="Server"> <ItemTemplate> <%# Container.DataItem( "Title" )%> <br> <asp:LinkButton Text="Edit!" CommandName="edit" Runat="Server"/> <asp:LinkButton Text="Delete!" CommandName="delete" Runat="Server"/> <asp:LinkButton Text="Update!" CommandName="update" Runat="Server"/> </ItemTemplate> </asp:DataList> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Each of the three LinkButton controls displayed with each title in the DataList is associated with a distinct subroutine. For example, when you click the LinkButton control named Delete! , the DeleteCommand event is raised in the DataList control. This event is associated with the DataList_DeleteCommand subroutine. The DataList_ItemCommand , DataList_EditCommand , DataList_DeleteCommand , and DataList_UpdateCommand subroutines don't actually do anything complicated. They simply assign different messages to a Label control. Notice that each subroutine associated with the DataList is declared with a DataListCommandEventArgs parameter. This parameter represents the information passed to the subroutine from the DataList . The DataListCommandEventArgs class has the following properties:
In the following sections, you learn how to take advantage of event bubbling to select and edit items in a DataList . Selecting Items in a DataList ControlYou can select items in a DataList and display the selected item with special formatting. This capability is useful, for example, when you want to display a menu of options and highlight the currently selected item in the menu. The selected item can be formatted by using either the SelectedItem template, the SelectedItemStyle object, or both. So, using these two options, you have a great deal of flexibility in controlling how a selected item is displayed. The page in Listing 11.6 uses a DataList to display a list of author last names from the Authors database table. Each of the author last names is rendered as a LinkButton control. When you select an author, the selected author is formatted with the SelectedItemTemplate (see Figure 11.5). Listing 11.6 DataListSelected.aspx<%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not IsPostBack BindDataList End If End Sub Sub BindDataList Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand Dim dtrAuthors As SqlDataReader conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select au_lname From Authors", conPubs ) conPubs.Open() dtrAuthors = cmdSelect.ExecuteReader() dlstAuthors.DataSource = dtrAuthors dlstAuthors.DataBind() dtrAuthors.Close() conPubs.Close() End Sub Sub DataList_ItemCommand( s As Object, e As DataListCommandEventArgs ) dlstAuthors.SelectedIndex = e.Item.ItemIndex BindDataList End Sub </Script> <html> <head><title>DataListSelected.aspx</title></head> <body> <form Runat="Server"> <asp:DataList ID="dlstAuthors" OnItemCommand="DataList_ItemCommand" Runat="Server"> <ItemTemplate> <asp:LinkButton Text='<%# Container.DataItem( "au_lname" )%>' Runat="Server" /> </ItemTemplate> <SelectedItemTemplate> <b><i><%# Container.DataItem( "au_lname" )%></i></b> </SelectedItemTemplate> </asp:DataList> </form> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 11.5. Selecting items in a DataList .
When you click one of the LinkButton controls displayed by the page in Listing 11.6, the ItemCommand event is raised. This event is wired up to the DataList_ItemCommand subroutine. The DataList_ItemCommand subroutine consists of the following two lines of code: dlstAuthors.SelectedIndex = e.Item.ItemIndex BindDataList The first statement assigns the index of the item selected to the SelectedIndex property of the DataList control. The next line rebinds the DataList control to the data source. When the DataList is rendered, the item that corresponds to the value of the SelectedIndex property is rendered with the SelectedItem template. Using the DataKeys Collection with the DataList ControlWhen you select an item in a DataList , you often need to retrieve the value of the primary key associated with the item. You can retrieve the value of the primary key associated with an item by using the DataKeys collection. Imagine that you want to display a database table named Products, which contains two columns named ProductID and ProductName , in a DataList . When a user selects an item in the DataList , you want to retrieve the value of the ProductID column associated with the selected item. To do so, you need to set the DataKeyField property of the DataList control like this: <asp:DataList ID="dlstProducts" DataKeyField="ProductID" OnItemCommand="dlstProducts_ItemCommand" Runat="Server"> If you assign the name of the primary key column of a database table to the DataKeyField property, a special collection called the DataKeys collection is populated automatically when you bind the DataList to the Products table. The DataKeys collection contains all the primary key values from the database table in the same order as the items in the DataList . NOTE You can use the DataKeys collection only when using a database table that has a single primary key column. (A primary key column cannot contain duplicate values or Null values.) After the DataKeys collection is created, you can retrieve the value of the primary key associated with any item in the DataList by passing the index number of the item to the DataKeys collection. For example, to retrieve the value of the primary key associated with the third item displayed by a DataList , you would use a statement like this: PrimaryKeyValue = dlstProducts.DataKeys( 2 ) Creating a Master/Detail Form with a DataList ControlIn this section, you build a master/detail form by using a DataList control to display a menu of categories and a Repeater control to display the products for the selected category (see Figure 11.6). Figure 11.6. Master/detail form with a DataList .
You use the Categories and Products tables from the Northwind database for the master and detail records. These tables share the CategoryID column, which you use to retrieve the products that match the selected category. The master/detail form is contained in Listing 11.7. Listing 11.7 DataListMasterDetail.aspx [View full width] <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not IsPostBack BindDataList End If End Sub Sub BindDataList Dim conNorthwind As SqlConnection Dim cmdSelect As SqlCommand Dim dtrCategories As SqlDataReader conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) cmdSelect = New SqlCommand( "Select CategoryID, CategoryName From Categories", conNorthwind ) conNorthwind.Open() dtrCategories = cmdSelect.ExecuteReader() dlstCategories.DataSource = dtrCategories dlstCategories.DataBind() dtrCategories.Close() conNorthwind.Close() End Sub Sub BindRepeater( intCatID As Integer ) Dim conNorthwind As SqlConnection Dim strSelect As String Dim cmdSelect As SqlCommand Dim dtrProducts As SqlDataReader conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) strSelect = "Select ProductName From Products Where CategoryID=@catID" cmdSelect = New SqlCommand( strSelect, conNorthwind ) cmdSelect.Parameters.Add( "@catID", intCatID ) conNorthwind.Open() dtrProducts = cmdSelect.ExecuteReader() rptProducts.DataSource = dtrProducts rptProducts.DataBind() dtrProducts.Close() conNorthwind.Close() End Sub Sub dlstCategories_ItemCommand( s As Object, e As DataListCommandEventArgs ) Dim intCatID As Integer dlstCategories.SelectedIndex = e.Item.ItemIndex BindDataList intCatID = dlstCategories.DataKeys( e.Item.ItemIndex ) BindRepeater( intCatID ) End Sub </Script> <html> <head><title>DataListMasterDetail.aspx</title></head> <body> <form Runat="Server"> <table width="100%"> <tr><td valign="top"> <asp:DataList ID="dlstCategories" OnItemCommand="dlstCategories_ItemCommand" DataKeyField="CategoryID" ItemStyle-BorderStyle="Solid" ItemStyle-BorderColor="Blue" Width="200" CellPadding="5" CellSpacing="10" BackColor="lightgrey" Runat="Server"> <ItemTemplate> <asp:LinkButton Text='<%# Container.DataItem( "CategoryName" )%>' Runat="Server" /> </ItemTemplate> <SelectedItemTemplate> <b><i><%# Container.DataItem( "CategoryName" )%></i></b> </SelectedItemTemplate> </asp:DataList> </td><td valign="top" width="100%"> <asp:Repeater ID="rptProducts" Runat="Server"> <ItemTemplate> <%# Container.DataItem( "ProductName" ) %> </ItemTemplate> <SeparatorTemplate> <hr> </SeparatorTemplate> </asp:Repeater> </td></tr> </table> </form> </body> </html> The C# version of this code can be found on the CD-ROM. When you click a LinkButton control in the DataList , the dlstCategories_ItemCommand subroutine is executed. This subroutine contains the following lines of code: dlstCategories.SelectedIndex = e.Item.ItemIndex BindDataList intCatID = dlstCategories.DataKeys( e.Item.ItemIndex ) BindRepeater( intCatID ) The first two statements set the SelectedIndex in the DataList and rebind the DataList to the data source. The next two statements retrieve the CategoryID of the selected category. The CategoryID is retrieved from the DataKeys collection. If you look closely at the declaration of the DataList in Listing 11.7, you notice that the DataList control's DataKeyField property is assigned the value CategoryID . This property causes the values from the CategoryID column from the Categories table to be automatically copied into the DataKeys collection when the Categories table is bound to the DataList . So, for any item in the DataList , you can look up the corresponding primary key value for the item by passing the index number of the item to the DataKeys collection. After the primary key associated with the selected item has been retrieved, it is passed to the BindRepeater subroutine, which displays all the products that match the CategoryID . Editing Items in a DataList ControlYou can use a DataList control to edit a set of records in a database table (see Figure 11.7). You should be warned , however, that it takes a little work to do so. In this section, you examine the process of editing records in a DataList control, step by step, and then tackle a complete example at the end of the section. Figure 11.7. Editing data with a DataList .
The DataList control includes a template called the EditItemTemplate . You can place form controls in the EditItemTemplate that enable you to edit a particular item in the DataList . The following DataList , for example, is declared with both an ItemTemplate , for displaying a record, and an EditItemTemplate , for editing a record: <asp:DataList ID="dlstAuthors" Runat="Server"> <ItemTemplate> <%# Container.DataItem( "au_lname" )%> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtLastName" Text='<%# Container.DataItem( "au_lname" )%>' Runat="Server" /> </EditItemTemplate> </asp:DataList> Normally, each record is displayed by the ItemTemplate . However, if you select a record for editing, the EditItemTemplate is displayed. In this example, the EditItemTemplate simply contains a TextBox control that has a field from the data source as its default value. You select a record for editing by using the EditItemIndex property of the DataList control. Only one item in a DataList can be selected for editing at a time. Typically, you select a record for editing by adding a LinkButton control to the ItemTemplate like this: <ItemTemplate> <%# Container.DataItem( "au_lname" )%> <br> <asp:LinkButton Text="Edit!" CommandName="edit" Runat="Server" /> </ItemTemplate> The LinkButton in this ItemTemplate has its CommandName property set to the value edit . When you click the LinkButton control, it raises an EditCommand event in the DataList control that contains it. You can associate a subroutine with the DataList that executes when the EditCommand event is raised like this: <asp:DataList ID="dlstAuthors" OnEditCommand="dlstAuthors_EditCommand" Runat="Server"> This DataList control associates the dlstAuthors_EditCommand subroutine with the EditCommand event. Within the dlstAuthors_EditCommand subroutine, you need to select the proper item in the DataList for editing. The following subroutine selects a record for editing by setting the EditItemIndex property of the DataList control: Sub dlstAuthors_EditCommand( s As Object, e As DataListCommandEventArgs ) dlstAuthors.EditItemIndex = e.Item.ItemIndex BindDataList End Sub When this subroutine executes, the item that raised the EditCommand event is selected for editing. The item is displayed with the EditItemTemplate rather than the normal ItemTemplate . You typically want to add Update , Delete , and Cancel commands to the EditItemTemplate . So, a more complete EditItemTemplate would look like this: <EditItemTemplate> <asp:TextBox ID="txtLastName" Text='<%# Container.DataItem( "au_lname" )%>' Runat="Server" /> <p> <asp:LinkButton Text="Update!" CommandName="update" Runat="Server" /> <asp:LinkButton Text="Delete!" CommandName="delete" Runat="Server" /> <asp:LinkButton Text="Cancel!" CommandName="cancel" Runat="Server" /> </EditItemTemplate> This EditItemTemplate contains three LinkButton controls labeled Update!, Delete!, and Cancel! The LinkButton control named Update! has its CommandName property set to update , the LinkButton control named Delete! has its CommandName property set to delete , and the LinkButton control named Cancel! has its CommandName property set to cancel . You can associate subroutines with the events raised by each of these commands by setting the OnUpdateCommand , OnDeleteCommand , and OnCancelCommand properties of the DataList control like this: <asp:DataList ID="dlstAuthors" OnEditCommand="dlstAuthors_EditCommand" OnUpdateCommand="dlstAuthors_UpdateCommand" OnDeleteCommand="dlstAuthors_DeleteCommand" OnCancelCommand="sdlstAuthors_CancelCommand" Runat="Server"> The EditCommand event triggers the dlstAuthors_EditCommand subroutine, the UpdateCommand event triggers the dlstAuthors_UpdateCommand subroutine, the DeleteCommand event triggers the dlstAuthors_DeleteCommand subroutine, and the CancelCommand event triggers the dlstAuthors_CancelCommand subroutine. Within the dlstAuthors_UpdateCommand subroutine, you need to write code to update the record selected for editing in the underlying database. Within the dlstAuthors_ DeleteCommand subroutine, you need to write code to delete the record selected for editing in the underlying database. Finally, in the dlstAuthors_CancelCommand subroutine, you need to write code to cancel the current editing operation. For example, the dlstAuthors_UpdateCommand subroutine might look like this: Sub dlstAuthors_UpdateCommand( s As Object, e As DataListCommandEventArgs ) Dim conPubs As SqlConnection Dim strUpdate As String Dim cmdUpdate As SqlCommand Dim strAuthorID As String Dim txtLastName As TextBox Dim txtPhone As TextBox strAuthorID = dlstAuthors.DataKeys( e.Item.ItemIndex ) txtLastName = e.Item.FindControl( "txtLastName" ) txtPhone = e.Item.FindControl( "txtPhone" ) conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) strUpdate = "Update Authors Set au_lname=@lastname, phone=@phone Where au_id=@authorID" cmdUpdate = New SqlCommand( strUpdate, conPubs ) cmdUpdate.Parameters.Add( "@authorID", strAuthorID ) cmdUpdate.Parameters.Add( "@lastname", txtLastName.Text ) cmdUpdate.Parameters.Add( "@phone", txtPhone.Text ) conPubs.Open() cmdUpdate.ExecuteNonQuery() conPubs.Close() dlstAuthors.EditItemIndex = -1 BindDataList End Sub Notice that the DataList does not handle updating the database table for you; you must write all the code yourself. In this subroutine, the values entered into the TextBox controls named txtLastName and txtPhone are retrieved by using the FindControl() method, which grabs these controls from the DataList item selected for editing. NOTE The FindControl() method retrieves a control by searching through a container for a control with a certain ID. If the FindControl() method fails to find the control, it returns the value . Finally, the underlying database is updated by executing a SqlCommand . The SqlCommand updates the Authors table, setting a new value for the au_lname and phone columns, where the author has a certain au_id . A complete example of editing records with a DataList is included in Listing 11.8. This example enables you to update and delete author last names and phone numbers from the Authors table. Listing 11.8 DataListEdit.aspx [View full width] <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load If Not IsPostBack Then BindDataList End If End Sub Sub BindDataList Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand Dim dtrAuthors As SqlDataReader conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) cmdSelect = New SqlCommand( "Select au_id, au_lname, phone From Authors Order by au_lname", conPubs ) conPubs.Open() dtrAuthors = cmdSelect.ExecuteReader() dlstAuthors.DataSource = dtrAuthors dlstAuthors.DataBind() dtrAuthors.Close() conPubs.Close() End Sub Sub dlstAuthors_EditCommand( s As Object, e As DataListCommandEventArgs ) dlstAuthors.EditItemIndex = e.Item.ItemIndex BindDataList End Sub Sub dlstAuthors_CancelCommand( s As Object, e As DataListCommandEventArgs ) dlstAuthors.EditItemIndex = -1 BindDataList End Sub Sub dlstAuthors_DeleteCommand( s As Object, e As DataListCommandEventArgs ) Dim conPubs As SqlConnection Dim strDelete As String Dim cmdDelete As SqlCommand Dim strAuthorID As String strAuthorID = dlstAuthors.DataKeys( e.Item.ItemIndex ) conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) strDelete = "Delete Authors Where au_id=@authorID" cmdDelete = New SqlCommand( strDelete, conPubs ) cmdDelete.Parameters.Add( "@authorID", strAuthorID ) conPubs.Open() cmdDelete.ExecuteNonQuery() conPubs.Close() dlstAuthors.EditItemIndex = -1 BindDataList End Sub Sub dlstAuthors_UpdateCommand( s As Object, e As DataListCommandEventArgs ) Dim conPubs As SqlConnection Dim strUpdate As String Dim cmdUpdate As SqlCommand Dim strAuthorID As String Dim txtLastName As TextBox Dim txtPhone As TextBox strAuthorID = dlstAuthors.DataKeys( e.Item.ItemIndex ) txtLastName = e.Item.FindControl( "txtLastName" ) txtPhone = e.Item.FindControl( "txtPhone" ) conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Pubs" ) strUpdate = "Update Authors Set au_lname=@lastname, phone=@phone Where au_id=@authorID" cmdUpdate = New SqlCommand( strUpdate, conPubs ) cmdUpdate.Parameters.Add( "@authorID", strAuthorID ) cmdUpdate.Parameters.Add( "@lastname", txtLastName.Text ) cmdUpdate.Parameters.Add( "@phone", txtPhone.Text ) conPubs.Open() cmdUpdate.ExecuteNonQuery() conPubs.Close() dlstAuthors.EditItemIndex = -1 BindDataList End Sub </Script> <html> <head><title>DataListEdit.aspx</title></head> <body> <form Runat="Server"> <asp:DataList ID="dlstAuthors" DataKeyField="au_id" OnEditCommand="dlstAuthors_EditCommand" OnCancelCommand="dlstAuthors_CancelCommand" OnDeleteCommand="dlstAuthors_DeleteCommand" OnUpdateCommand="dlstAuthors_UpdateCommand" RepeatColumns="4" GridLines="Both" CellPadding="10" EditItemStyle-BackColor="lightgrey" Runat="Server"> <ItemTemplate> <%# Container.DataItem( "au_lname" )%> - <%# Container.DataItem( "phone" )%> <br> <asp:LinkButton Text="Edit!" CommandName="edit" Runat="Server" /> </ItemTemplate> <EditItemTemplate> <b>Last Name:</b> <br> <asp:TextBox ID="txtLastName" Text='<%# Container.DataItem( "au_lname" )%>' Runat="Server" /> <p> <b>Phone:</b> <br> <asp:TextBox ID="txtPhone" Text='<%# Container.DataItem( "phone" )%>' Runat="Server" /> <p> <asp:LinkButton Text="Update!" CommandName="update" Runat="Server" /> <asp:LinkButton Text="Delete!" CommandName="delete" Runat="Server" /> <asp:LinkButton Text="Cancel!" CommandName="cancel" Runat="Server" /> </EditItemTemplate> </asp:DataList> </form> </body> </html> The C# version of this code can be found on the CD-ROM. |