| In order to allow users to edit all the information about a particular product, you might like them to be able to click a link containing the product name and have the link navigate the users to an editing page (ProductEdit.aspx, in this case), with the correct item selected. Adding this functionality will require solving a few problems. You'll need to be able to do the following: -
Add a column to the DataGrid control, containing a link to another page -
Add information to each link, indicating the correct product to display on the detail page
Adding a Hyperlink Column Visual Studio .NET makes both these problems simple to solve via the Property Builder dialog box you saw in an earlier chapter. To add the hyperlink column, follow these steps. -
Load ProductsEdit.aspx in the Visual Studio .NET page designer. -
Right-click the DataGrid control and select Property Builder from the context menu. -
Select the Columns tab to display information about the columns displayed in the DataGrid control. -
From the Available columns list, select HyperLink Column and then click the > button to add a new Hyperlink column to the Selected Columns list. -
Click the up-arrow button twice to place the new column as the second column in the Selected Columns list. -
Set the properties of the column as shown in Table 17.2. Figure 17.2 shows how the Properties dialog box should look once you're done. Figure 17.2. The finished hyperlink column's properties.
Table 17.2. Set These Properties for the Hyperlink Column | Property | Value | Description | | Header Text | Product Description | This text appears at the top of the column in the DataGrid control as a column header. | | Text Field | ProductName | Each link in the grid displays the value of this field from the grid's data source. Make sure you don't fill in the Text property, which would just insert static text. | | URL Field | ProductID | Each link uses this field from the data source as a replacement value in the URL associated with the link. (See the URL format string description.) | | URL Format String | ProductDetail.aspx?ID={0} | This value becomes the URL associated with the link, after the grid replaces the placeholder ({0}) with the value in the URL Field property for the current row. | -
In the Solution Explorer window, right-click ProductsEdit.aspx and then select Build and Browse from the context menu. -
Move your mouse pointer over the links that appear in the grid and note that the URLs that appear in the status bar correctly include the ProductID value associated with each product. Figure 17.3 shows the page with a URL in the status bar. Figure 17.3. The DataGrid control formats hyperlink URLs for you.
-
Click one of the links, noting the product name. When ProductDetail.aspx appears, verify that you've navigated to the correct page. Make a change to the unit price or units in stock; then click Save. Note that the value updates correctly on the original page once you dismiss the detail page. -
Close the browser window and save your project. There's not much magic involved in the Hyperlink column within the DataGrid control. When you add the Hyperlink column, at design time, the ASP.NET page framework renders the column as a hyperlink within an HTML table, at runtime. The only "magic" occurs in the rendering of the URL for the hyperlink. Because you want each link to navigate to the same page, with a different value in the query string, you might think you have to create some programming code to set up the different links. To make this simple for you, the page framework uses the URLFormatString property of the column, combined with the URLField property, and generates the correct URL for you. Using a replaceable parameter (in the same style as if you were calling String.Format), the grid generates a URL like this one for a product whose ProductID field contains 17: ProductDetail.aspx?ID=17 Editing Data on the Detail Page If you select a link on ProductsEdit.aspx, you'll be taken to the ProductDetail.aspx page, with the data matching the product you selected. This page uses no new techniques, so we won't focus on it here. Instead, this section demonstrates a few specific lines of code that the page uses in order to do its job. The ProductDetail.aspx page, shown in Figure 17.4, contains all the code needed to display and edit a single row of data. Figure 17.4. The ProductDetail.aspx page allows you to edit a single row of data.
Most importantly, how does this page retrieve just the single row of data that you need? In the ProductDisplay method, called from the Page_Load procedure, the code builds a SELECT statement that includes the results of retrieving the ID value from the query string. If you remember from the previous section, the links on ProductsEdit.aspx include text such as ID=17 in the query string, and the code in ProductDisplay.aspx can retrieve that value. Specifically, you'll find the code shown in Listing 17.2. Listing 17.2 Display Product Detail, Given the ProductID Value Private Sub ProductDisplay() Dim strSQL As String Dim strConn As String Dim dr As OleDbDataReader ' Get connection and SQL strings strConn = Session("ConnectString").ToString strSQL = _ "SELECT ProductID, ProductName, " & _ "SupplierID, CategoryID, QuantityPerUnit, " & _ "UnitPrice, UnitsInStock, UnitsOnOrder, " & _ "ReorderLevel, Discontinued " & _ "FROM Products " & _ "WHERE ProductID = " & Request.QueryString("ID") ' Load the data. Try dr = DataHandler.GetDataReader(strSQL, strConn) If dr.Read Then lblProductID.Text = dr("ProductID").ToString txtProductName.Text = dr("ProductName").ToString txtQtyPerUnit.Text = dr("QuantityPerUnit").ToString txtUnitPrice.Text = dr("UnitPrice").ToString txtUnitsInStock.Text = dr("UnitsInStock").ToString txtUnitsOnOrder.Text = dr("UnitsOnOrder").ToString txtReorderLevel.Text = dr("ReorderLevel").ToString chkDiscontinued.Checked = CBool(dr("Discontinued")) ' Position to the correct supplier and category SelectItem(ddlSupplier, dr("SupplierID").ToString) SelectItem(ddlCategory, dr("CategoryID").ToString) End If Catch exp As Exception lblError.Text = exp.Message End Try End Sub This procedure builds a SQL string that only retrieves the one row matching the passed-in product ID. Then, once it has retrieved a DataReader object, it copies data out of the DataReader's row into the controls on the page. One interesting side note: The data retrieved from the Products table includes SupplierID and CategoryID fields, and the page needs to convert these ID values into the corresponding text values. The ddlSupplier and ddlCategory controls' items have their Text property containing the text to display and their Value properties containing the corresponding ID values from their tables. (See the CategoryLoad and SupplierLoad procedures in the page's code-behind file for the details, but there's nothing new there.) In order to convert from ID values into the corresponding text, the code calls the SelectItem procedure: Private Sub SelectItem( _ ByVal Control As ListControl, _ ByVal Value As String) ' Given a ListBox or DropDownList control ' and a value, select the item containing ' the value. Dim li As ListItem li = Control.Items.FindByValue(Value) If Not li Is Nothing Then Control.SelectedIndex = Control.Items.IndexOf(li) End If End Sub The SelectItem procedure first calls the FindByValue method of the Items collection of the control in order to retrieve the ListItem object matching the value you supplied. It then uses the IndexOf method to find the ordinal position of the item in the list and sets the SelectedIndex property to match the selected index. It's a rather roundabout technique for setting the value of a DropDownList control, but it's the best we've seen. In order to save your changes, when you click the Save button, the event handler calls the ProductSave procedure, shown in Listing 17.3. Note the use of the QuoteString procedure you added earlier within this code. This procedure adds the necessary apostrophes when building up the SQL string. Listing 17.3 Save Changes Made to the Products Information Private Sub ProductSave() Dim strSQL As String Dim strConn As String ' Build SQL and connection strings strConn = Session("ConnectString").ToString strSQL = String.Format("UPDATE Products SET " & _ "ProductName = {0}," & _ "CategoryID = {1}, " & _ "SupplierID = {2}, " & _ "QuantityPerUnit = {3}, " & _ "UnitPrice = {4}, " & _ "UnitsInStock = {5}, " & _ "UnitsOnOrder = {6}," & _ "ReorderLevel = {7}," & _ "Discontinued = {8}" & _ " WHERE ProductID = " & Request.QueryString("ID"), _ DataHandler.QuoteString(txtProductName.Text), _ ddlCategory.SelectedItem.Value, _ ddlSupplier.SelectedItem.Value, _ DataHandler.QuoteString(txtQtyPerUnit.Text), _ txtUnitPrice.Text, _ txtUnitsInStock.Text, _ txtUnitsOnOrder.Text, _ txtReorderLevel.Text, _ CInt(chkDiscontinued.Checked)) Try DataHandler.ExecuteSQL(strSQL, strConn) Response.Redirect("ProductsEdit.aspx") Catch exp As Exception lblError.Text = exp.Message End Try End Sub The ProductSave procedure simply creates an UPDATE statement, uses the ExecuteSQL method to send the data back to SQL Server, and uses the Response.Redirect method to go back to the previous page. TIP The ProductSave procedure calls the QuoteString method you added to the DataHandler class, earlier in the chapter. This method formats strings, including surrounding apostrophes and doubled embedded apostrophes. It's also interesting to note that although the Checked property of a check box returns True or False (Boolean values), and the CInt function converts these values to -1 and 0, respectively, SQL Server interprets the -1 correctly and stores a 1 in the bit data type within the Products table.
|
