Adding Product Data

for RuBoard

Though the ProductEditor.aspx Web form can now be used to update product information, there's still no way to add new products into the database. In this section, you'll see how to modify the existing Product Editor to also handle the case where a user needs to enter a new product. You could create an entirely new screen to handle product additions, but because this new screen would be very similar to the existing Product Editor, it makes sense to just modify the existing code to handle both cases.

The stored procedure Product_Add in Listing 22.5 performs the actual addition of new product data into the database. Notice that the parameters are nearly the same as for the Product_Update stored procedure in Listing 22.1. However, there are a few differences. Because Product_Add is creating a new entry in the products table, a new ProductID is generated instead of being passed in as an argument. This newly created ProductID is then returned to the component layer. You can check that value to ensure that the new product was actually inserted into the database. You know that if the value of the ProductID is not greater than 0, the insert was unsuccessful .

Listing 22.5 The Product_Add Stored Procedure

 CREATE PROCEDURE Product_Add (     @ProductName nvarchar(40),     @QuantityPerUnit nvarchar(20),     @UnitPrice money,     @UnitsInStock smallint,     @ImagePath varchar(256),     @Discontinued bit,     @ProductID int OUTPUT ) AS INSERT INTO Products (     ProductName,     QuantityPerUnit,     UnitPrice,     UnitsInStock,     ImagePath,     Discontinued ) VALUES (     @ProductName,     @QuantityPerUnit,     @UnitPrice,     @UnitsInStock,     @ImagePath,     @Discontinued ) SELECT @ProductID = @@IDENTITY 

With the stored procedure created, the next step is to create the component layer method that will use the stored procedure in Listing 22.5 to add the new product record. The new AddProduct() method is provided in Listing 22.6.

Listing 22.6 The ProductEditor.aspx Web Form

 Public Function AddProduct( _                                     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_Add", conn)             'Mark the Command as a Stored Procedure             cmd.CommandType = CommandType.StoredProcedure             ' Add Parameters to SPROC             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 parameterProductID As SqlParameter = _                               New SqlParameter("@ProductID", SqlDbType.Int, 4)             parameterProductID.Direction = ParameterDirection.Output             cmd.Parameters.Add(parameterProductID)             conn.Open()             cmd.ExecuteNonQuery()             conn.Close()             Return Convert.ToInt32(parameterProductID.Value)         End Function 

Finally, you'll need to make a few edits to the ProductEditor.aspx Web form in order to enable the form to handle adding a new product. The two changes are in Listings 22.7 and 22.8. Add the AddNew() method in Listing 22.7 either just before or just after the existing UpdateData() method. This is the method that will call the component layer AddProduct() method from Listing 22.6.

Likewise, replace the old Page_Load() event on the ProductEditor.aspx Web form with the one in Listing 22.8. Page_Load() has been modified to handle the case where the ProductID passed to it is 0. If the ProductID is 0, the form assumes it should load a blank form and enable the user to add a new product record to the database.

Listing 22.7 The ProductEditor.aspx Web Form

 Private Sub AddNew( )     Dim iResults as Int32     Dim product as new ADO24HRS.ProductsDB()     iResults = product.AddProduct( _                 txtProductName.Text, _                 txtQuantityPerUnit.Text, _                 txtUnitPrice.Text, _                 txtUnitsInStock.Text, _                 txtImagePath.Text, _                 chkDiscontinued.Checked )     if iResults > 1 then         lblOutput.Text = "Record successfully added!"     else         lblOutput.Text = "There was a problem adding the record."     end if End Sub 

Listing 22.8 The ProductEditor.aspx Web Form

 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         if ProductID <> 0 then             UpdateData( Convert.ToInt32(ViewState("ProductID")) )         else             AddNew()         end if     end if     if ProductID <> 0 then         'Load existing product information         LoadData( ProductID )     else         'Prepare form for new data         ProductImage.Visible = False         header.InnerText = "Add New Product"     end if End Sub 

The last task you need to perform is to add a button or hyperlink anywhere inside the server-side form tags in products.aspx. A sample button is provided in Listing 22.9.

Listing 22.9 The Create New Product Button Added to Products.aspx

 <asp:button id="cmdNewProduct" OnClick="cmdNewProduct_OnClick"                    Text="Create New Product" runat="server" /> 

Then, when the user clicks on the new button, redirect them to the ProductEditor.aspx Web form with the ProductID set to 0. This sample code is provided in Listing 22.10.

Listing 22.10 The Create New Product Button Added to Products.aspx

 Private Sub cmdNewProduct_OnClick( Source as Object, E as EventArgs )     Response.Redirect("ProductEditor.aspx?ProductID=0") End Sub 

Now, when the ProductEditor.aspx Web form is loaded using this button, your screen will look like the one in Figure 22.3, ready to enter a new product.

Figure 22.3. The appearance of the Product Editor screen ready to accept a new product.

for RuBoard