Initializing the Tables

 

Introduction.  Previously, we created the tables for our on-line store and shopping experience.  Now we need to initialize the tables with some dummy data in order to test it.  The first thing we need to do is make sure we are keeping track of a ShopperID for everyone visiting our web.   This can also be used to uniquely identify these shoppers in other tables in our web.

The following Global.asa file is executed when the session starts.

 

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
' Subroutine is fired off when the session starts
Sub Session_OnStart

' Start the shopper ID at 0
session("idShopper") = 0

End Sub

</SCRIPT>

 

 

Now we need to start creating some ASPs that will execute our SQL to insert some initializing data into our tables.  Our first program is called LoadDepartments.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 connLoad, strSQLCreate
Set connLoad = Server.CreateObject("ADODB.Connection")
connLoad.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"

connLoad.Open

' Creating the SQL String to insert data into the table
strSQLCreate = "insert into department(chrDeptName, txtDeptDesc, chrDeptImage) values('Funky Wacky Music'," & _
"'The craziest music you have ever seen. Is it even music?','funk.gif')"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into department(chrDeptName, txtDeptDesc, chrDeptImage) values('Cool Backstreet Jazz'," & _
"'Jazz that will make you dance.','dance.gif')"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into department(chrDeptName, txtDeptDesc, chrDeptImage) values('Crying Westerns'," & _
"'The stories are so sad and the twangs so deep you will cry.','Western.gif')"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into department(chrDeptName, txtDeptDesc, chrDeptImage) values('Punked Out'," & _
"'All of these titles are just on the edge of being punk (their worse).','punk.gif')"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into department(chrDeptName, txtDeptDesc, chrDeptImage) values('Wild T-Shirts'," & _
"'The coolest ... everyone will be wearing them!','tshirts.gif')"
connLoad.execute(strSQLCreate)

connLoad.Close
Set connLoad = Nothing

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

%>

 

 

The next ASP should be called LoadProducts.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 connLoad, strSQLCreate
Set connLoad = Server.CreateObject("ADODB.Connection")
connLoad.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"

connLoad.Open

' Creating the SQL Strings to insert data into the tables
strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('Joe Bob''s Thimble Sounds', 'Great thimble music that you will love!', 'thimble.gif', 1000, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(1,1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('The sounds of silence (for real).', '120 minutes of blank static." & _
"Nothing like it to drive your neighbors nuts!', 'static.gif', 875, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(1,2)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('Alley Jazz.', 'Recorded in an alley in New Orleans during Mardi Gras', 'jazz.gif', 1875, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(2,3)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('Circle Sax', 'The circle ensemble plays sax while spinning.', 'circle.gif', 825, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(2,4)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('Hat''s Off', 'Sad songs of cowboy''s hats falling off on the trail.', 'hatsoff.gif', 900, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(3,5)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('Lasso''s Of Love', 'Heart warming achapela country love songs. " & _
"Includes the soothing sounds of the swirling lasso in the background.', 'lassos.gif', 8900, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(3,6)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('My Dog is a Cat', 'Bizarre songs about animals being other animals.', 'DogCat.gif', 500, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(4,7)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('Candle Sticks are Falling', 'Poems about candles read with smash punk in the background.', 'candles.gif', 999, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(4,8)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('T-Shirt Rip', 'Full of holes and totally cool!', 'hole.gif', 8000, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(5, 9)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into products(chrProductName, txtDescription, chrProductImage, intPrice, intActive)" & _
"values('Undershirt', 'It looks like an undershirt, but it is better!', 'under.gif', 2000, 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert departmentproducts(idDepartment, idProduct) values(5, 10)"
connLoad.execute(strSQLCreate)


connLoad.Close
Set connLoad = Nothing

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

%>

 

 

The next ASP should be called LoadAttributes.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 connLoad, strSQLCreate
Set connLoad = Server.CreateObject("ADODB.Connection")
connLoad.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"

connLoad.Open

' Creating the SQL Strings to insert data into the tables
strSQLCreate = "insert into attributecategory(chrCategoryName) values('Size')"
connLoad.execute(strSQLCreate) 

strSQLCreate = "insert into attributecategory(chrCategoryName) values('Color')"
connLoad.execute(strSQLCreate)



strSQLCreate = "insert into attribute(chrAttributeName, idAttributeCategory) values('Small', 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into attribute(chrAttributeName, idAttributeCategory) values('Medium', 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into attribute(chrAttributeName, idAttributeCategory) values('Large', 1)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into attribute(chrAttributeName, idAttributeCategory) values('X-Large', 1)"
connLoad.execute(strSQLCreate)



strSQLCreate = "insert into attribute(chrAttributeName, idAttributeCategory) values('Red', 2)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into attribute(chrAttributeName, idAttributeCategory) values('Blue', 2)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into attribute(chrAttributeName, idAttributeCategory) values('Green', 2)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into attribute(chrAttributeName, idAttributeCategory) values('White', 2)"
connLoad.execute(strSQLCreate)


strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(1, 9)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(2, 9)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(3, 9)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(4, 9)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(5, 9)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(6, 9)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(7, 9)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(8, 9)"
connLoad.execute(strSQLCreate)



strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(1, 10)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(2, 10)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(3, 10)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(4, 10)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(5, 10)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(6, 10)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(7, 10)"
connLoad.execute(strSQLCreate)

strSQLCreate = "insert into productattribute(idAttribute, idProduct) values(8, 10)"
connLoad.execute(strSQLCreate)


connLoad.Close
Set connLoad = Nothing

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

%>

 

Now that these records have been initialized in our tables we can start developing the store in future web pages.