Adding New Products in the Store Manager

 

Introduction.  Now that we have taken care of searching our existing products, we need to start looking at other typical database functions.  In this page we develop the functionality to add new products  The interface for this is done in an ASP called NewProduct.asp.  Since NewProduct.asp is essentially a form, it needs a processing script to actually add the new product to the database.  This processing script will be called AddNewProduct.asp.  This ASP will rely on one stored procedure called sp_InsertProduct to actually implement the insertion into the table.  These will be presented on this page and some discussion will ensue.

The following table gives the ASPs and stored procedure we will develop in this page.

 

Stored Procedure ASP Container
  NewProduct.asp
sp_InsertProduct AddNewProduct.asp

 

The Stored Procedure.  First we present the stored procedure.  Remember, you do not need to implement the stored procedures, this is to illustrate what you would need to do if you had the correct permissions in your database.  This stored procedure uses an INSERT INTO command in SQL to take the new product fields and insert them into the table.  It also uses a SELECT command to increment the idProduct.  The file should be called sp_InsertProduct.asp.
<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include File="adovbs.inc"-->

<%
' Open a connection to our SQL Server database
' We will use the ADO Driver connection
Dim connDeptProds, strSQLCreate
Set connDeptProds = Server.CreateObject("ADODB.Connection")
connDeptProds.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"

connDeptProds.Open

' Creating the SQL String to create the stored procedure
strSQLCreate = "CREATE PROCEDURE sp_InsertProduct " & _
"@chrProductName varchar(255), " & _
"@txtDescription text, " & _
"@chrProductImage varchar(100), " & _
"@intPrice int, " & _
"@intActive int AS " & _
"insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive) " & _
"values(@chrProductName, @txtDescription, @chrProductImage, @intPrice, @intActive) " & _
"select idProduct = @@identity"

connDeptProds.execute(strSQLCreate)

connDeptProds.Close
Set connDeptProds = Nothing

Response.Write "<font size = 5>The SQL has executed</font>"

%>

 

 

As with all of the stored procedures you create, this file would only need to be executed once in your database.  After that you should get an error that the stored procedure already exists.  Since this is linked to the WildWillies database you do not even need to do this much.  But, remember, if you do execute such an ASP you can delete the ASP immediately after the stored procedure has been created.

The NewProduct.asp.  This is essentially a form page to get the inputs for the new product.  You need to save this file and upload it to your StoreManager subdirectory.  This ASP will do the following.

  1. It uses the validatecheck.asp include to make sure the user didn't try to circumvent the security
  2. It then uses the navinclude.asp to create the common navigation at the top of each page in the store manager.
  3. It displays a form for the inputs associated with the product and posts them to the AddNewProduct.asp.

Call the file NewProduct.asp.
<%@ Language=VBScript %>
<!-- #Include file="include/validatecheck.asp" -->
<html>
<!-- NewProduct.asp - Handles adding in a new product into the store. -->

<head>
<meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</head>
<body>

<!-- #Include file="include/navinclude.asp" -->

<!-- Form to post the new product to the database -->
<form method="post" action="AddNewProduct.asp">

<!-- Table build the form for adding the new product -->
<table cellpadding="3" cellspacing="3">

<!-- Product Name Input -->
<tr>
<td align="right"><b>Product Name:</b></td>
<td>
<input type="text" value="" name="chrProductName" size="60">
</td>
</tr>

<!-- Product Description Input -->
<tr>
<td align="right"><b>Product Description:</b></td>
<td>
<textarea cols="50" rows="10" name="txtDescription"></textarea>
</td>
</tr>

<!-- Product Image Input -->
<tr>
<td align="right"><b>Product Image:</b></td>
<td><input type="text" value="" name="chrProductImage"></td>
</tr>

<!-- Product Price Input -->
<tr>
<td align="right"><b>Product Price:</b></td>
<td><input type="text" value="" name="intPrice"></td>
</tr>

<!-- Check box to indicate the product is active -->
<tr>
<td align="right"><b>Active:</b></td>
<td>
<input type="checkbox" value="1" name="intActive">
</td>
</tr>

<!-- Submit button to add the product -->
<tr>
<td colspan="2" align="center">
<input type="submit" value="Add Product" name="Submit">
</td>
</tr>

<!-- Close out the page. -->
</table>
</form>

</body>
</html>

 

After you upload this ASP and access it in your web you should see something like the following.

 

 

Now that we have the form for inputs, we need the processing script

The AddNewProduct.asp.  This is a relatively straight forward ASP with the real work done by the stored procedure.  You need to save this file and upload it to your StoreManager subdirectory.  This ASP will do the following.

  1. It cleans up the inputs using Request and Replace objects.
  2. It modifies the price to an integer.
  3. Then it uses the sp_InsertProduct to insert the basic product information into the database.
  4. Then it redirects the user to the ManageProduct.asp in order to do things like assign the attributes and department.

Call the file AddNewProduct.asp.
<%@ Language=VBScript %>
<%
' ****************************************************
' AddNewProduct.asp - Handles adding a new product to
' the store.
' ****************************************************

' Retrieve the product name and ensure that any single quotes are doubled.
chrProductName = replace(request("chrProductName"), "'", "''")

' Retrieve the product description and ensure that any single quotes are doubled.
txtDescription = replace(request("txtDescription"), "'", "''")

' Retrieve the product image.
chrProductImage = request("chrProductImage")

' Retrieve the price. Ensure that we multiply times 100 to store as a whole integer.
intPrice = request("intPrice") * 100

' Retrieve the active setting.
intActive = request("intActive")

' We have to check and see if any setting is made. 
' It will not be set if the box is not checked.
if intActive = "" then 

' Set the flag to 0 so it is not active.
intActive = 0

else

' Set the flag to 1 so it is active.
intActive = 1

end if


' Create an ADO database connection
set dbProduct = server.createobject("adodb.connection")

' Create the record set
set rsProduct = server.CreateObject("adodb.recordset")

' Open the connection using our SQL Server DSN-less connection
dbProduct.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=WildWillies;UID=cis; PWD=csatqu"

dbProduct.Open

' Execute the sp_InsertProduct stored procedure 
' to add the product into the database. 
sql = "execute sp_InsertProduct '" & _
chrProductName & "', '" & _
txtDescription & "', '" & _
chrProductImage & "', " & _
intPrice & ", " & _
intActive

' Execute the statement
set rsProduct = dbProduct.Execute(sql)

' Send the user to the ManageProduct.asp page to allow
' the user to edit the new product. 
Response.Redirect "ManageProduct.asp?idProduct=" & _
rsProduct("idProduct")

%>

 

Since we haven't yet developed the ManageProduct.asp we can't really make use of this page.  That will happen in the next web page.