- Displaying Related Data in Nested DataGrid Controls
- A Master/Detail Display with DataList and DataGrid Controls
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
A Master/Detail Display with DataList and DataGrid Controls
So far, you've only seen pages that display related data and you've only used the DataGrid control. To demonstrate some different techniques when using nested list controls, this section shows an example that provides a collapsible master/detail display, using two different list controls; it also allows the child rows to be edited.
Figure 4.7 shows the completed sample page. You can see the same list of customers as in the previous examples in this chapter. For each one there is a drop-down button that, when clicked, opens a list of the orders for that customer and allows them to be edited. At the same time, the button changes to an "up" button that closes the list of orders. Selecting a different customer while one list is open closes that list and opens the selected one, to provide a compact display that reduces bandwidth requirements and provides faster page load times.
)
Figure
4.7 Creating a collapsible master/detail display for related row
sets.
Declaring the DataList and DataGrid Controls
The sample page consists of a DataList control that generates the list of customers, to which you apply various formatting and style attributes. This is bound to a row set extracted from the Customers table in the Northwind database through a DataReader instance.
However, when a row in the DataList control is switched to selected mode, that row also displays a DataGrid control containing the customer's order details. These rows are extracted from the Orders table of the database through another DataReader instance.
Finally, when the Edit link in one of the order rows for the selected customer is clicked, that row is placed into edit mode. It then displays the data in the row set that is not read-only in text boxes and provides the Update and Cancel links. Listing 4.10 shows the complete declaration of the DataList control and the nested DataGrid controls.
Listing 4.10—The Declaration of the DataList and DataGrid Controls
<asp:DataList id="dtl1" Width="95%" runat="server"
CellPadding="3" CellSpacing = "2"
DataKeyField="CustomerID"
OnItemCommand="DoItemSelect"
OnItemDataBound="BindOrdersGrid" >
<HeaderStyle Font-Bold="True" ForeColor="#ffffff"
BackColor="#b50055" />
<FooterStyle Font-Bold="True" ForeColor="#ffffff"
BackColor="#b50055" />
<ItemStyle BackColor="#FFF7E7" VerticalAlign="Top" />
<AlternatingItemStyle BackColor="#FFFFC0" />
<HeaderTemplate>
<b>Customer List</b>
</HeaderTemplate>
<ItemTemplate>
<asp:ImageButton CommandName="Select"
ImageUrl="~/images/click-down.gif"
Width="16" Height="17" runat="server"
AlternateText="Click to view orders" />
<%# Container.DataItem("CustomerID") %>
<b><%# Container.DataItem("CompanyName") %></b>
<%# Container.DataItem("City") %>
<%# Container.DataItem("Country") %>
Phone: <%# Container.DataItem("Phone") %>
</ItemTemplate>
<SelectedItemTemplate>
<asp:ImageButton CommandName="UnSelect"
ImageUrl="~/images/click-up.gif"
Width="16" Height="17" runat="server"
AlternateText="Click to hide orders" />
<%# Container.DataItem("CustomerID") %>
<b><%# Container.DataItem("CompanyName") %></b>
<%# Container.DataItem("City") %>
<%# Container.DataItem("Country") %>
Phone: <%# Container.DataItem("Phone") %>
<asp:DataGrid id="dgr1" runat="server"
BorderStyle="None" BorderWidth="0" BackColor="#DEBA84"
CellPadding="3" CellSpacing="0" Width="100%"
DataKeyField="OrderID"
OnEditCommand="DoItemEdit"
OnUpdateCommand="DoItemUpdate"
OnCancelCommand="DoItemCancel"
AutoGenerateColumns="False" >
<HeaderStyle BackColor="#c0c0c0" />
<Columns>
<asp:EditCommandColumn EditText="Edit"
CancelText="Cancel" UpdateText="Update" />
<asp:BoundColumn DataField="OrderID" HeaderText="Number"
ReadOnly="True" />
<asp:TemplateColumn HeaderText="Ordered">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "OrderDate", _
"{0:dd MMM yyyy}") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Columns="8" id="txtOrderDate"
runat="server"
Text='<%# DataBinder.Eval(Container.DataItem, _
"OrderDate", "{0:dd MMM yyyy}") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Required">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "RequiredDate", _
"{0:dd MMM yyyy}") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Columns="8" id="txtRequiredDate"
runat="server"
Text='<%# DataBinder.Eval(Container.DataItem, _
"RequiredDate", "{0:dd MMM yyyy}") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Shipped">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "ShippedDate", _
"{0:dd MMM yyyy}") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Columns="8" id="txtShippedDate"
runat="server"
Text='<%# DataBinder.Eval(Container.DataItem, _
"ShippedDate", "{0:dd MMM yyyy}") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Freight"
HeaderStyle-HorizontalAlign="Right"
ItemStyle-HorizontalAlign="Right">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, _
"Freight", "${0:f2}") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Columns="3" id="txtFreight" runat="server"
Text='<%# Container.DataItem("Freight") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="ShipperName"
HeaderText="Via" ReadOnly="True"/>
</Columns>
</asp:DataGrid>
</SelectedItemTemplate>
<FooterTemplate>
</FooterTemplate>
</asp:DataList>
The Important Points of the DataList Control Declaration
The DataList control displays the list of customers, and you add to it three attributes that control its behavior in terms of viewing the order list for each customer. You set the DataKeyField attribute to the CustomerID column in the source row set so that you can easily get the ID of the customer for the current row:
DataKeyField="CustomerID"
You also specify the names of two event handlers. The routine named DoItemSelect will be executed when any control within the DataList control causes a postback, and the routine named BindOrdersGrid will be executed each time a row in the DataList control is bound to its source data:
OnItemCommand="DoItemSelect" OnItemDataBound="BindOrdersGrid"
The DataList control declaration uses a header and a footer row to achieve the appearance of the dark bands above and below the list, with the header containing just the plain text "Customer List" and the footer containing a nonbreaking space character ( ) to preserve the row height.
In the <ItemTemplate> section, you use an ImageButton control to generate the drop-down button. The declaration of the ImageButton control sets CommandName to "Select"; this value is used to detect whether the ImageButton button was clicked when the ItemCommand event was raised. You also specify the image file for the button (in the images subfolder of the application root), the size, and the alternate text that will provide the pop-up ToolTip:
<asp:ImageButton CommandName="Select" ImageUrl="~/images/click-down.gif" Width="16" Height="17" runat="server" AlternateText="Click to view orders" />
The remainder of the <ItemTemplate> content is made up of the usual Container.DataItem("column-name") data binding statements that display values from the customer row.
The <SelectedItemTemplate> section of the DataList control declaration comes next. This contains the content that will only be displayed for the single row that is in selected mode when the DataList control is bound to its data source. (If no row is selected, this content will not be displayed.) In this template, you provide another ImageButton control that allows the user to close the list. You use a different CommandName setting this time ("UnSelect"), and you use a different image and alternate text (see Figure 4.8):
<asp:ImageButton CommandName="UnSelect" ImageUrl="~/images/click-up.gif" Width="16" Height="17" runat="server" AlternateText="Click to hide orders" />
)
Figure
4.8 The buttons to open and close the lists of orders.
Then, after the same set of Container.DataItem("column-name") data binding statements as in the <ItemTemplate> section (because you want to display the customer details in both modes) comes the declaration of the nested DataGrid control.
The Important Points of the DataGrid Control Declaration
The DataGrid control that displays the order details for the selected customer is placed in the <SelectedItemTemplate> element of the DataList control, so it will be generated and displayed only for the row (if any) that is currently in selected mode.
In the opening tag, you add the attributes that wire up event handlers for the three events you want to handle: the EditCommand event that occurs when an Edit link is clicked, the UpdateCommand event that occurs when an Update link is clicked, and the CancelCommand event that occurs when a Cancel link is clicked. You also specify the OrderID column from the source row set as the DataKeyField value and turn off autogeneration of columns in the DataGrid control:
DataKeyField="OrderID" OnEditCommand="DoItemEdit" OnUpdateCommand="DoItemUpdate" OnCancelCommand="DoItemCancel" AutoGenerateColumns="False"
To create the Edit, Update, and Cancel links in each row, you declare the first column within the <Columns> element of the DataGrid control as an <EditCommandColumn> element. In it, you can set the text that will be displayed for the three links:
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
The rest of the columns for the DataGrid control are declared either as read-only BoundColumn controls like this:
<asp:BoundColumn DataField="column-name" HeaderText="column-heading" ReadOnly="True" />
or as <TemplateColumn> elements that display the value as text when in normal mode or in a TextBox control when in edit mode:
<asp:TemplateColumn HeaderText="Ordered">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "OrderDate", _
"{0:dd MMM yyyy}") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Columns="8" id="txtOrderDate"
runat="server"
Text='<%# DataBinder.Eval(Container.DataItem, _
"OrderDate", "{0:dd MMM yyyy}") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
Populating the DataList Control
You'll recognize much of the code used to populate the DataList control and the nested DataGrid controls because it is very similar to the code in the previous example, where you populate nested DataGrid controls using a DataReader instance. However, one major change in this example is that you are supporting postbacks, to allow the user to show or hide order details and edit them.
The first consequence of this, taking into account the fact that you have enabled viewstate for this page, is that you must be sure to populate the DataList control only when the page first loads and not following a postback.
Using Viewstate with List Controls
Not enabling viewstate is a common error newcomers make when using data binding and postbacks with the list controls in ASP.NET. If viewstate is not enabled, the list control will not maintain its state; there will be no values in it after a postback. However, if you repopulate it in the Page_Load event after every postback, the list control may not behave properly. For example, it may not display the selected row or raise events on the server when controls in the grid (such as the Edit links) are activated. The solution is to enable viewstate and only populate the list control in the Page_Load event handler the first time the page is loaded. Afterward, you repopulate the list control only when you change a property such as SelectedIndex or EditIndex, in order to display the rows in the appropriate modes. And you only do so in the event handler that handles the mode change, as you'll see in this example.
Listing 4.11 shows the Page_Load event handler for this example, and it contains the functions that create the DataReader instance required to provide the data for the DataList and DataGrid controls. This time, you only need two row sets—the lists of customers and orders—and these are provided by the two functions named GetCustomers and GetOrders. Each one uses the same GetReader function as in the previous example to generate the DataReader instance and return it.
Listing 4.11—The Page_Load Event Handler and Functions That Generate the Row Sets from the Database
Sub Page_Load()
If Not Page.IsPostback Then
dtl1.DataSource = GetCustomers()
dtl1.DataBind()
End If
End Sub
Function GetCustomers() As OleDbDataReader
Dim sSelect As String _
= "SELECT CustomerID, CompanyName, City, Country, Phone " _
& "FROM Customers WHERE CustomerID LIKE 'c%'"
Return GetReader(sSelect)
End Function
Function GetOrders(sKey As String) As OleDbDataReader
Dim sSelect As String _
= "SELECT Orders.OrderID, Orders.OrderDate, " _
& "Orders.RequiredDate, Orders.ShippedDate, Orders.Freight, " _
& "Shippers.CompanyName As ShipperName " _
& "FROM Orders JOIN Shippers " _
& "ON Orders.ShipVia = Shippers.ShipperID " _
& "WHERE CustomerID='" & sKey & "'"
Return GetReader(sSelect)
End Function
Function GetReader(sSQL As String) As OleDbDataReader
' get DataReader for rows from Northwind tables
Dim sConnect As String _
= ConfigurationSettings.AppSettings("NorthwindOleDbConnectString")
Dim oConnect As New OleDbConnection(sConnect)
Try
oConnect.Open()
Dim oCommand As New OleDbCommand(sSQL, oConnect)
Return oCommand.ExecuteReader(CommandBehavior.CloseConnection)
Catch oErr As Exception
' be sure to close connection if error occurs
If oConnect.State <> ConnectionState.Closed Then
oConnect.Close()
End If
' display error message in page
lblErr.Text = oErr.Message & "<p />"
End Try
End Function
Populating the DataGrid Control
As each row in the DataList control is bound to its source data, the ItemDataBound event is raised. This causes the BindOrdersGrid event handler that you specified for the OnItemDataBound attribute of the DataList control to execute. Listing 4.12 shows the BindOrdersGrid event handler, and you can see that the first task is (as usual) to examine the row type.
However, in this case, the nested DataGrid control will exist only if the current row in the DataList control is in selected mode, so you check to see whether the row type is ListItemType.SelectedItem. If it is, you get the customer ID from the DataKeys collection, get a reference to the nested DataGrid control in this row, and then bind the DataGrid control to the result of the GetOrders function shown in Listing 4.11. The customer ID is passed to the GetOrders function so that it returns only the order rows for the current customer.
Listing 4.12—The BindOrdersGrid Event Handler for the ItemDataBound Event
Sub BindOrdersGrid(sender As Object, e As DataListItemEventArgs)
' see what type of row (header, footer, item, etc.) caused the event
Dim oType As ListItemType = CType(e.Item.ItemType, ListItemType)
' only process it if it's the Selected row
If oType = ListItemType.SelectedItem Then
' get value of CustomerID for this row from DataKeys collection
Dim sKey As String = dtl1.DataKeys(e.Item.ItemIndex)
' get a reference to the DataGrid control in this row
Dim oGrid As DataGrid = CType(e.Item.FindControl("dgr1"), DataGrid)
' bind nested "orders" DataGrid to DataReader
oGrid.DataSource = GetOrders(sKey)
oGrid.DataBind()
End If
End Sub
Selecting a Row in the DataList Control
You've seen how the nested DataGrid control is populated for the row that is in selected mode. To put the row into this mode, you handle the ItemCommand event of the DataList control. Recall that you included the attribute OnItemCommand="DoItemSelect" in the declaration of the DataList control, so any postback that is initiated by a control within the DataList control will raise the ItemCommand event and execute the DoItemSelect event handler routine.
Listing 4.13 shows the DoItemSelect event handler. The first step is to determine which control caused the postback, and you do this by examining the CommandName property of the control referenced by the sender argument passed to the event handler. You set this property on the two ImageButton controls that display the up and down images in the first column of the DataList control.
Listing 4.13—The Event Handler for the ItemCommand Event of the DataList Control
Sub DoItemSelect(sender As Object, e As DataListCommandEventArgs) ' see if it was the Select button that was clicked If e.CommandName = "Select" Then ' set the SelectedIndex property of the list to this item's index dtl1.SelectedIndex = e.Item.ItemIndex dtl1.DataSource = GetCustomers() dtl1.DataBind() End If ' see if it was the Un-Select button that was clicked If e.CommandName = "UnSelect" Then ' set the SelectedIndex property of the list to -1 dtl1.SelectedIndex = -1 dtl1.DataSource = GetCustomers() dtl1.DataBind() End If End Sub
If the down image was clicked (CommandName="Select"), you want to put that row into selected mode by setting the SelectedIndex property of the DataList control to the index of the row. You get the index of the current row from the ItemIndex property of the current DataListItem instance, set the SelectedIndex property, and then repopulate the DataList control. The control will automatically display the current row in selected mode by using the contents of the <SelectedItemTemplate> element instead of the <ItemTemplate> element.
Alternatively, if the CommandName property of the control that caused the postback is set to "UnSelect", you know that the user clicked the up button in this row. In this case, you just set the SelectedIndex property to -1 and repopulate the DataList control to display all the rows in normal mode.
Accessing the Controls in a Row in the DataList Control
Each row in a DataList control is represented by a DataListItem instance in the DataListCommandEventArgs object that is passed to the ItemDataBound and ItemCreated event handlers. The DataListItem object is very similar to the DataGridItem object discussed earlier in this chapter. It has the same commonly used members shown in Table 4.1 for the DataGridItem object, with the exception of the DataSetIndex property and the Cells collection (because the individual values in a DataList control are not output as HTML table cells). Likewise, the individual rows in a Repeater control are represented by the RepeaterItem object, which provides a slightly more restricted set of properties.
Editing a Row in the DataGrid Control
If a row in the DataList control is in selected mode, the DataGrid control that displays the orders for the selected customer is visible. The first column of this DataGrid control contains the three links, Edit, Update, and Cancel, depending on whether that DataGrid control row is currently in edit mode. So you have to handle three events that can be raised by the DataGrid control. You specified the event handlers as attributes when you declared the DataGrid control:
OnEditCommand="DoItemEdit" OnUpdateCommand="DoItemUpdate" OnCancelCommand="DoItemCancel"
The event handlers for the EditCommand event, named DoItemEdit, and the CancelCommand event, named DoItemCancel, are shown in Listing 4.14. The one issue you have to contend with is that the DataGrid control is nested within one of the rows of the parent DataList control. So to get a reference to it, you can search for it within the Controls collection of the row in the DataList control that is currently selected.
Listing 4.14—The Event Handlers for Switching Into and Out of Edit Mode
Function GetDataGridRef() As DataGrid
' get a reference to the DataGrid in the selected DataList row
Dim oRow As DataListItem = dtl1.Items(dtl1.SelectedIndex)
Return CType(oRow.FindControl("dgr1"), DataGrid)
End Function
Sub DoItemEdit(sender As Object, e As DataGridCommandEventArgs)
' get a reference to the DataGrid control in this row
Dim oGrid As DataGrid = GetDataGridRef()
' set the EditItemIndex of the grid to this item's index
oGrid.EditItemIndex = e.Item.ItemIndex
' bind grid to display row in new mode
' get CustomerID from the DataKeys collection of the DataList
oGrid.DataSource = GetOrders(dtl1.DataKeys(dtl1.SelectedIndex))
oGrid.DataBind()
End Sub
Sub DoItemCancel(sender As Object, e As DataGridCommandEventArgs)
' get a reference to the DataGrid control in this row
Dim oGrid As DataGrid = GetDataGridRef()
' set EditItemIndex of grid to -1 to switch out of Edit mode
oGrid.EditItemIndex = -1
' bind grid to display row in new mode
' get CustomerID from the DataKeys collection of the DataList
oGrid.DataSource = GetOrders(dtl1.DataKeys(dtl1.SelectedIndex))
oGrid.DataBind()
End Sub
The function named GetDataGridRef shown at the start of Listing 4.14 does this by first getting a reference to the DataListItem object that represents the selected row in the DataList control, using the current SelectedIndex property of the DataList control to locate it. You know that one row must be selected; otherwise, the DataGrid control would not be visible and the user could not have clicked the Edit link or the Cancel link. Then you can use the FindControl method exposed by the selected DataListItem object to locate the DataGrid control.
Then, in the DoItemEdit routine, you can use the GetDataGridRef function to get a reference to the DataGrid control and set EditItemIndex to the index of the row containing the Edit link that was clicked. To display the grid with this row in edit mode, you repopulate it, using the GetOrders routine shown in Listing 4.11. This requires the ID of the currently selected customer, and you can get that easily enough from the DataList control's DataKeys collection—by specifying the current SelectedIndex value of the DataList control as the row index for the DataKeys collection.
To switch the row out of edit mode when the user clicks the Cancel link, you just get a reference to the DataGrid control (again using the GetDataGridRef function), set EditItemIndex to -1, and repopulate the grid.
Using the Sender Argument As a Reference to the Source Control
You may have realized that there is a simpler approach to getting a reference to the nested DataGrid control than is used in this example. In fact, you saw the alternative technique in previous examples in this chapter. You can use the sender argument passed to the event handler instead; this argument is, of course, a reference to the control that raised the event. However, the function provided in this example is intended to demonstrate another way that you can achieve the same result, and it may come in handy in other situations.
The remaining event handler, named DoItemUpdate, is executed when the user clicks the Update link after changing some values in the text boxes within the grid. This is a more complicated routine, although much of the code is concerned with trapping data input errors.
Listing 4.15 shows the complete event handler, and you can see that the first task is to get a reference to the DataGrid control. Then you can get references to each of the TextBox controls in the row by using the FindControl method of the current DataGridItem instance.
Using the UpdateCommand Event
Notice that you don't have to worry about what type of row you're dealing with here, as you do when handling the ItemDataBound and ItemCreated events. The UpdateCommand event is only raised for the row that is already in edit mode, so you know that the controls defined in the <EditItemTemplate> section will be present in this row.
Listing 4.15—The Event Handler for the UpdateCommand Event of the DataGrid Control
Sub DoItemUpdate(sender As Object, e As DataGridCommandEventArgs)
' get a reference to the DataGrid control in this row
Dim oGrid As DataGrid = GetDataGridRef()
' get a reference to the text boxes
Dim oOrdered As TextBox _
= CType(e.Item.FindControl("txtOrderDate"), TextBox)
Dim oRequired As TextBox _
= CType(e.Item.FindControl("txtRequiredDate"), TextBox)
Dim oShipped As TextBox _
= CType(e.Item.FindControl("txtShippedDate"), TextBox)
Dim oFreight As TextBox _
= CType(e.Item.FindControl("txtFreight"), TextBox)
' verify that the values are valid
Dim dOrderDate, dRequDate, dShipDate As DateTime
Dim cFreight As Decimal
Try
dOrderDate = DateTime.Parse(oOrdered.Text)
Catch
lblErr.Text = "ERROR: Invalid value entered for Order Date"
Exit Sub
End Try
Try
dRequDate = DateTime.Parse(oRequired.Text)
Catch
lblErr.Text = "ERROR: Invalid value entered for Required Date"
Exit Sub
End Try
Try
dShipDate = DateTime.Parse(oShipped.Text)
Catch
lblErr.Text = "ERROR: Invalid value entered for Shipped Date"
Exit Sub
End Try
Try
cFreight = Decimal.Parse(oFreight.Text)
Catch
lblErr.Text = "ERROR: Invalid value entered for Freight Cost"
Exit Sub
End Try
' create a suitable SQL statement and execute it
Dim sSQL As String
sSQL = "UPDATE Orders SET OrderDate='" _
& dOrderDate.ToString("yyyy-MM-dd") & "', " _
& "RequiredDate='" _
& dRequDate.ToString("yyyy-MM-dd") & "', " _
& "ShippedDate='" _
& dShipDate.ToString("yyyy-MM-dd") & "', " _
& "Freight=" & cFreight.ToString() & " " _
& "WHERE OrderID=" & oGrid.DataKeys(e.Item.ItemIndex)
ExecuteSQLStatement(sSQL)
' set EditItemIndex of grid to -1 to switch out of Edit mode
oGrid.EditItemIndex = -1
' bind grid to display row in new mode
' get CustomerID from the DataKeys collection of the DataList
oGrid.DataSource = GetOrders(dtl1.DataKeys(dtl1.SelectedIndex))
oGrid.DataBind()
End Sub
The code in Listing 4.15 extracts the values from the four TextBox controls, using a Try...Catch construct to detect invalid values and catch errors. If an invalid data type conversion occurs for the Parse method, the Catch section of each construct displays the error message in a Label control located above the DataList control in the page and prevents further processing by exiting from the event handler routine. Figure 4.9 shows the result when an invalid value is detected.
)
Figure
4.9 Catching data entry errors and invalid values in the master/detail
sample page.
Next, the routine builds up a SQL statement. It uses the values from the TextBox controls, together with the current order ID extracted from the DataKeys collection of the current DataGrid control. This SQL statement is passed to a separate routine named ExecuteSQLStatement, which we'll look at shortly. Of course, you could use a stored procedure to update the database if preferred.
Finally, you switch the current row in the DataGrid control out of edit mode and repopulate it to display the updated values.
Updating the Original Data in the Database
The final section of code in the sample page is the ExecuteSQLStatement routine, shown in Listing 4.16. There's nothing new or exciting here: You just create a Connection instance and a Command instance, open the Connection instance, and execute the SQL statement by calling the ExecuteNonQuery method. If it doesn't update just one row, or if an error occurs, you display a suitable error message.
Listing 4.16—The Routine to Push the Updates Back into the Database
Sub ExecuteSQLStatement(sSQL)
' execute SQL statement against the original data source
Dim sConnect As String = ConfigurationSettings.AppSettings( _
"NorthwindOleDbConnectString")
Dim oConnect As New OleDbConnection(sConnect)
Try
oConnect.Open()
Dim oCommand As New OleDbCommand(sSQL, oConnect)
If oCommand.ExecuteNonQuery() <> 1 Then
lblErr.Text &= "ERROR: Could not update the selected row"
End If
oConnect.Close()
Catch oErr As Exception
' be sure to close connection if error occurs
If oConnect.State <> ConnectionState.Closed Then
oConnect.Close()
End If
' display error message in page
lblErr.Text &= "ERROR: " & oErr.Message & "<p />"
End Try
End Sub
Concurrent Update Checking
Notice that you don't perform full concurrent update error checking here. If the data is updated by another user while the page is displayed, the second user's changes will be overwritten. To avoid this, you would have to check the existing value in every column of the row in the database against its original value when the page was first displayed. This is easier to do when the data you use to populate the page is held in a DataSet instance. With a DataReader instance (as in this example), you would probably decide to store the original values in hidden controls in the row that is in edit mode or use a timestamp or GUID column in the database that indicates whether the row has been changed concurrently.