Updating Product Data

for RuBoard

In the preceding hour, you saw how to create a set of screens that displays a list of products and enables you to click on a particular product to see the detailed product information. The next few sections of this hour reinforce the same three- tier style of development by implementing an administrative section.

The stored procedure that will actually update the product information in the database is straightforward and provided in Listing 22.1. As you can see, it accepts the product values as parameters and passes those parameters to an UPDATE query. When the query is done, the number of records updated by the query is passed back to the calling method. This can be used to ensure that the update had no errors. Because we're updating a single product at a time, the number of rows modified should always be "1" if the query is successful.

Listing 22.1 The Product_Update Stored Procedure

 CREATE PROCEDURE Product_Update (     @ProductID int,     @ProductName nvarchar(40),     @QuantityPerUnit nvarchar(20),     @UnitPrice money,     @UnitsInStock smallint,     @ImagePath varchar(256),     @Discontinued bit,     @RowsAffected int OUTPUT ) AS UPDATE     Products SET     ProductName = @ProductName,     QuantityPerUnit = @QuantityPerUnit,     UnitPrice = @UnitPrice,     UnitsInStock = @UnitsInStock,     ImagePath = @ImagePath,     Discontinued = @Discontinued WHERE     ProductID = @ProductID 

With the stored procedure created, the next step is to create the necessary middle-tier method that will perform the update. Place the code from Listing 22.2 into the ProductsDB.vb file. For more details on creating a component layer, please see the relevant sections in the preceding hour.

Listing 22.2 The UpdateProducts Middle-Tier Method

 Public Function UpdateProducts( ProductID as Int32, _                                         ProductName as string, _                                         QuantityPerUnit as string, _                                         UnitPrice as string, _                                         UnitsInStock as Int16, _                                         ImagePath as string, _                                         Discontinued as Boolean ) as Int32             'Create Instance of Connection and Command Objects             Dim conn as New SqlConnection( _                         ConfigurationSettings.AppSettings("ConnectionString"))             Dim cmd as New SqlCommand("Product_Update", conn)             'Mark the Command as a Stored Procedure             cmd.CommandType = CommandType.StoredProcedure             ' Add Parameters to SPROC             Dim parameterProductID As SqlParameter = _                               New SqlParameter("@ProductID", SqlDbType.Int, 4)             parameterProductID.Value = productID             cmd.Parameters.Add(parameterProductID)             Dim parameterProductName As SqlParameter = _                       New SqlParameter("@ProductName", SqlDbType.NVarChar, 40)             parameterProductName.Value = ProductName             cmd.Parameters.Add(parameterProductName)             Dim parameterQuantityPerUnit As SqlParameter = _                   New SqlParameter("@QuantityPerUnit", SqlDbType.NVarChar, 20)             parameterQuantityPerUnit.Value = QuantityPerUnit             cmd.Parameters.Add(parameterQuantityPerUnit)             Dim parameterUnitPrice As SqlParameter = _                             New SqlParameter("@UnitPrice", SqlDbType.Money, 8)             parameterUnitPrice.Value = Convert.ToDecimal(UnitPrice)             cmd.Parameters.Add(parameterUnitPrice)             Dim parameterUnitsInStock As SqlParameter = _                       New SqlParameter("@UnitsInStock", SqlDbType.SmallInt, 2)             parameterUnitsInStock.Value = Convert.ToInt16(UnitsInStock)             cmd.Parameters.Add(parameterUnitsInStock)             Dim parameterImagePath As SqlParameter = _                         New SqlParameter("@ImagePath", SqlDbType.NVarChar, 50)             parameterImagePath.Value = ImagePath             cmd.Parameters.Add(parameterImagePath)             Dim parameterDiscontinued As SqlParameter = _                       New SqlParameter("@Discontinued", SqlDbType.Bit, 1)             parameterDiscontinued.Value = Discontinued             cmd.Parameters.Add(parameterDiscontinued)             Dim parameterRowsAffected As SqlParameter = _                           New SqlParameter("@RowsAffected", SqlDbType.Int, 4)             parameterRowsAffected.Direction = ParameterDirection.Output             cmd.Parameters.Add(parameterRowsAffected)             conn.Open()             cmd.ExecuteNonQuery()             conn.Close()             Return Convert.ToInt32(parameterRowsAffected.Value)         End Function 

After you've added the preceding code into the ProductsDB.vb file, don't forget that you must recompile the namespace. You must manually recompile your namespace after each change.

As you can see, the UpdateProducts() method in Listing 22.2 accepts seven arguments that describe a product. Lines 9 “15 set up the Connection and Command objects. Then, lines 17 “51 create the set of stored procedure input parameters. The last parameter, found in lines 53 “56, sets up the output parameter that we'll use to ensure our query was successful.

With the middle tier and data layers firmly in place, you can easily work on the presentation layer. Rather than create an entirely new product list screen, you can use the Products.aspx Web form from the preceding hour in Listing 21.3 with a few changes. At the end of the DataList 's ItemTemplate , place the code in Listing 22.3. This will create a new link for each product on the product list screen. Clicking these links will navigate to the ProductEditor.aspx Web form that you will create next. You can see the additional link created in Figure 22.1. Notice that the hyperlink generated by the DataGrid passes the ProductID to the producteditor.aspx page in the querystring. This is the mechanism the product editor uses to identify which product is being modified.

Figure 22.1. Each product in the Product List screen now contains an additional link: edit product.

Listing 22.3 Adding an Additional Link to the Products.aspx DataList

 <a href='producteditor.aspx?productID= <%# DataBinder.Eval(Container.DataItem,  "ProductID") %>'>     edit product </a> 

The code for the ProductEditor.aspx Web form is provided in Listing 22.4. Notice that it is much like the ProductDetails.aspx screen. Instead of displaying the detailed product information in label Web controls, however, it uses editable text boxes. Note that the LoadData methods for these two screens are almost identical. When the ProductEditor.aspx Web form loads, it retrieves the product information using the exact same techniques as the ProductDetails.aspx uses.

Listing 22.4 The ProductEditor.aspx Web Form

 <% @Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="VB" runat="server" >         Sub Page_Load(Source as Object, E as EventArgs)             'Get ProductID and store in Viewstate             Dim ProductID As Integer = Int32.Parse(Request.Params("ProductID"))             if ProductID <> "" then                 ViewState("ProductID") = ProductID             end if             if IsPostBack then                 UpdateData( Convert.ToInt32(ViewState("ProductID")) )             end if             LoadData( ProductID )         End Sub         Private Sub UpdateData( productID as Int32 )             Dim iResults as Int32             Dim product as new ADO24HRS.ProductsDB()             iResults = product.UpdateProducts( _                         productID, _                         txtProductName.Text, _                         txtQuantityPerUnit.Text, _                         txtUnitPrice.Text, _                         txtUnitsInStock.Text, _                         txtImagePath.Text, _                         chkDiscontinued.Checked )             if iResults = 1 then                 lblOutput.Text = "Record successfully updated!"             else                 lblOutput.Text = "There was a problem updating the record"             end if         End Sub         Private Sub LoadData( productID as Int32 )             'Declare new instance of ProductsDB             Dim products as New ADO24HRS.ProductsDB()             'Declare new instance of custom class             Dim myProductDetails as ADO24HRS.ProductDetails             'Fill custom class with data             myProductDetails = products.GetProductDetails( productID )             txtProductName.Text = myProductDetails.ProductName             txtQuantityPerUnit.Text = myProductDetails.QuantityPerUnit             txtUnitPrice.Text = myProductDetails.UnitPrice.ToString()             txtUnitsInStock.Text = myProductDetails.UnitsInStock.ToString()             ProductImage.Src = "/ADO24HOURS" + myProductDetails.ImagePath             txtImagePath.Text = myProductDetails.ImagePath.ToString()             chkDiscontinued.Checked = myProductDetails.Discontinued         End Sub     </script> </HEAD> <BODY> <h1>Edit Product Details</h1> <hr> <form runat="server" id=form1 name=form1> <asp:label id="lblOutput" runat="server" /> <table cellpadding=5>   <tr>     <td>         <img id="ProductImage" height=144 width=144 runat="server">     </td>     <td valign="center">       <table>         <tr>           <td>             <strong>Product Name: </strong>           </td>           <td>             <asp:TextBox id="txtProductName" runat="server" />           </td>         </tr>         <tr>           <td>             Quantity Per Unit:           </td>           <td>             <asp:TextBox id="txtQuantityPerUnit" runat="server" />           </td>         </tr>         <tr>           <td>             Units In Stock:           </td>           <td>             <asp:TextBox id="txtUnitsInStock" runat="server" />           </td>         </tr>         <tr>           <td>             Price Per Unit:           </td>           <td>             <asp:TextBox id="txtUnitPrice" runat="server" />           </td>         </tr>         <tr>           <td>             Image Path:           </td>           <td>             <asp:TextBox id="txtImagePath" runat="server" />           </td>         </tr>         <tr>           <td>             Product Discontinued:           </td>           <td>             <asp:CheckBox id="chkDiscontinued" runat="server" />           </td>         </tr>         <tr>           <td>             &nbsp;           </td>           <td>             <input type="submit" value="Submit Changes">           </td>         </tr>       </table>     </td>   </tr> </table> </form> <hr> </BODY> </HTML> 

After you click on one of the new Edit Product links on the product listing screen, the ProductEditor.aspx Web form from Listing 22.4 loads. It appears much like the screen in Figure 22.2.

Figure 22.2. The appearance of the Product Editor.

You can use the Product Editor to make changes to existing product information, such as the product name, quantity in stock, and price. After you make changes to the selected product and submit the form, the Web form gathers the product information and passes it to the UpdateProducts() method of the ProductsDB class, found in Listing 22.2. As you saw earlier, that method then passes that product information to the Product_Update stored procedure from Listing 22.1. The Product_Update method executes an UPDATE SQL query, which saves the product data. After this is completed, the stored procedure passes the number of rows affected by the query back to the UpdateProducts() method. The UpdateProducts() method passes this value back to the Web form, which uses the value to either confirm for the user that the product was updated successfully, or inform the user that something went wrong.

for RuBoard