Cars App 1
What's the Plan?
The plan is to create a web app similar to e-commerce app 3, but to adapt it
for an online store selling made-to-order cars from various manufacturers. The main feature that is added
is the grouping of products (cars) by departments (brands, manufacturers, categories, or whatever you
wish to call this). Thus, a customer would typically select a brand of car from a drop-down list and
then select a particular model of car from the drop-down list that appears as a result of the choice
of brand. Selecting the model brings up a page of detailed information on the product (car).
Since our business builds cars as the orders arrive, there is no inventory of cars to keep track of.
In other e-commerce applications, tracking inventory and letting customers see if a product is in stock or
not might be needed. Inventory information might be kept in its own table or might be a column in the
Product table.
The search functionality in this app can also be used to look up cars matching
a certain target string. This search functionality is expanded so that the matches for the target
string are sought in substrings of the product name (car model), product brand name (manufacturer),
and product description. The brand name is the new item here.
- The home page is shown in this screen shot.
- The privacy page and
contact page are much the same as we used before.
- The brands page allows the user to choose from a pull-down
list the brands (manufacturers) of cars.
- Doing so brings up the cars page, which allows the user to choose
a particular model of car from a pull-down list.
- That then brings up the results page, which shows the details
about that particular car, such as the description and price.
- The search page is similar to what we used before.
- After entering a search string at the search page, the
search results page is shown.
- Clicking on one of the View buttons on the search results page takes you to the
results page already seen above.
|
Copying an Old Project
- Although you can build this project from scratch, it is probably easier to copy
e-commerce app 3.
- Open the project, e-commerce app 3, in Visual Studio.
- Make a copy of this project and name the new project cars1.
- Close the old solution and open the new cars1 project.
- Save all of your files.
|
Database Changes
Product Table
- We basically create entirely new tables to hold the data for our cars web site.
- First, use SQL Server's Enterprise Manager to create a table called Product designed as follows:
|
Column Name |
Data Type |
Length |
Allow Nulls |
ProdNum |
Int |
4 |
no |
ProdName |
char |
24 |
no |
ProdDescription |
varchar |
128 |
yes |
ProdPrice |
money |
8 |
no |
- In addition, make ProdNum to be the primary key field.
- Also make it an Identity field.
- Fill in descriptions for each field if you wish to do so.
- Add the following data to this table:
|
ProdNum |
ProdName |
ProdDescription |
ProdPrice |
1 |
Hurricane |
Top of line luxury automobile. GPS navigation system comes standard. |
35255 |
2 |
Tornado |
Top of line sports car. |
32999 |
3 |
Cheetah |
Modestly-priced sports car. |
27880 |
4 |
Famtastic |
Modestly-priced family car. |
22555 |
6 |
Lightning |
Sporty two-seater electric/gas hybrid car. |
33000 |
7 |
Bolt |
Small 4-seater electric/gas hybrid car. |
36500 |
8 |
Thunder |
Large 6-seater electric/gas hybrid car. |
39999 |
9 |
Rocket |
Our fastest sports car. |
38400 |
10 |
Laser |
Small 2-seater sports car. |
37600 |
11 |
Kangaroo |
Large all terrain vehicle. |
35000 |
12 |
Rabbit |
Small all terrain vehicle. |
33900 |
13 |
M-star |
Medium-sized sport utility vehicle. |
29000 |
14 |
Roady |
Modestly-priced sports car. |
28100 |
15 |
Hippo |
Large sport utility vehicle. |
31200 |
Category Table
- Next, create a table called Category to hold the product categories (brands or manufacturers if
you prefer that terminology).
- Use the following design in Enterprise Manager:
|
Column Name |
Data Type |
Length |
Allow Nulls |
CategoryNum |
Int |
4 |
no |
CategoryName |
char |
24 |
no |
- In addition, make CategoryNum to be the primary key field.
- Also make it an Identity field.
- Fill in descriptions for each field if you wish to do so.
- Add the following data to this table:
|
CategoryNum |
CategoryName |
1 |
Electra Motors |
2 |
Alpha Omega |
3 |
Vanguard |
4 |
Road Master |
CategoryProduct Table
- Next, create a table called CategoryProduct to link the Category and Product tables.
- Use the following design in Enterprise Manager:
|
Column Name |
Data Type |
Length |
Allow Nulls |
CategoryNum |
Int |
4 |
no |
ProdNum |
int |
4 |
no |
- In addition, make CategoryNum and ProdNum to be the composite primary key field.
- To do this, use CTRL-click on the 2 rows and then press the key button.
- Fill in descriptions for each field if you wish to do so.
- Add the following data to this table:
|
CategoryNum |
ProdNum |
1 |
6 |
1 |
7 |
1 |
8 |
2 |
9 |
2 |
10 |
2 |
11 |
2 |
12 |
3 |
1 |
3 |
2 |
3 |
3 |
3 |
4 |
4 |
13 |
4 |
14 |
4 |
15 |
Relationships Between Tables
- We should set up the appropriate relationships between the CategoryProduct table
and each of the other two tables.
- This is done much like in the previous survey app.
- Open the CategoryProduct table in design view.
- Set up a relationship between the ProdNum (primary key) field of the Product table and the ProdNum
(foreign key) field of the CategoryProduct table. You can refer to
this picture of the setup for this relationship.
- Similarly, set up a relationship between the CategoryNum (primary key) field of the Category table
and the CategoryNum (foreign key) field of the CategoryProduct table. You can refer to
this picture of the setup for this relationship.
|
Web Page Changes and Additions
Home Page
- The home page should be adjusted to contain the text "Order a new car, built as you like it!"
and other lines as seen in this screen shot, though you can adjust
the contents as you like.
- In either HTML view or in Properties, change the title of the home page
to "Home" or perhaps Home followed by the name of the web site.
- In addition, the header.txt and footer.txt files that give the standard layout for each page
need to be adjusted. The changes for header.txt are summarized here:
- The name of the business, found in the line with font size 7, has been changed from
"Br. David's Software" to something about cars. The color was changed to red for this.
- The image displayed at the top of the page was changed to
cars1.jpg and was derived from clip art.
You will probably want to drag this image file from My Computer to Solution Explorer
so as to add it to this project. Also in Solution Explorer, right click on the old earth image
and delete it from the project.
- All of the navigation icons along the left side have been changed to use
http://cis.stvincent.edu/icons/ball.red.gif in the
img src tag.
- The Home link and icon all use home.aspx in the
A HREF tags as the
page to go to. In addition, the old hw5.aspx home page was renamed as home.aspx so
as to match this.
- The second link was changed to "Brands" and uses brands.aspx
in the
A HREF tags as the page to go to.
- The text for the link that said "Contact Us" has been shortened to "Contact".
- The search and privacy links use the same text and pages to jump to as before.
The only thing that changes for these is the new icon, as mentioned above.
- The changes for footer.txt are simple ones. Adjust the copyright notice to reflect the
company name, adjust the date as desired, and fill in whatever you like for the warning.
The screen shots use this:
"Warning: This is a demo, not a real e-commerce site! Any resemblance to real cars is accidental."
|
Privacy and Contact Pages
- No changes are needed on the privacy page
other than to change the title to Privacy (with the company name appended if you wish).
- On the contact page, change the contact information to something reasonable for this business.
Perhaps the email address should be for the sales department, for example.
Also change the title to Contact (with the company name appended if you want).
You can refer to this screen shot.
|
Brands Page
- Rename the old products.aspx page as brands.aspx.
- We want this page to look like this.
- Change the title to Brands (with the company name appended if you wish).
- You might want to change the text of the label to
"Select a brand of car from the pull-down:".
- Change the ID of the drop-down list to BrandList.
- Click on the data adapter at the bottom of this page and then click on the configure link
at the bottom of the Properties window.
- Set up the data adapter to use a new stored procedure named SelectCategoryName.
- Do NOT generate insert, delete, or update stored procedures to go along with this one.
- The stored procedure should simply return the CategoryName column from the Category table
and put the results in ascending order.
- The resulting stored procedure should look like this:
|
CREATE PROCEDURE studentc.SelectCategoryName
AS
SET NOCOUNT ON;
SELECT CategoryName FROM dbo.Category ORDER BY CategoryName
GO
|
- Remember to go to SQL Server Enterprise Manager and give the SQL or IWAM account Exec access
to this stored procedure. (Use whatever fits your server configuration.)
- In the Properties window for your new data adapter, expand the SelectCommand field. Replace
what is in the CommandText field with yourid.SelectProdSearch. (Of course, fill in your user
ID in place of yourid.) Say Yes and Yes to the questions asked.
- In the Properties window for the data adapter, try out the Preview Data link to see
if your stored procedure is working.
- Of course, save all of your files now and periodically throughout the development of this web app.
|
Brands Code-Behind File
- Open the brands.aspx.vb code-behind file.
- Adjust the code to match that shown below. These changes involve referring to the BrandList
instead of the old ProductList, redirecting to cars.aspx instead of results.aspx, etc.
|
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
'Put user code to initialize the page here
Dim TargetBrand As String
If Not IsPostBack Then
Try
BrandList.Items.Add("*** Make a choice ***")
SqlConnection1.Open()
dReader = SqlDataAdapter1.SelectCommand.ExecuteReader
While (dReader.Read())
BrandList.Items.Add(dReader.GetString(0))
End While
Catch exception As System.Data.SqlClient.SqlException
ErrorLabel.Visible = True
Catch exception As Exception
ErrorLabel.Visible = True
Finally
SqlConnection1.Close()
End Try
Else ' Postback case:
TargetBrand = BrandList.SelectedItem.Text
If Not Equals(TargetBrand, "*** Make a choice ***") Then
Session.Add("BrandSelected", TargetBrand)
Response.Redirect("cars.aspx")
End If
End If
End Sub
|
Cars Page
- We need to create that cars.aspx page.
- Begin by adding a new web form to your project and name the form cars.aspx.
- We want this page to look like this picture.
- You can copy the 2 labels and the drop-down list from the brands.aspx form to the cars.aspx form.
- Make a copy of the large font label and place it at the top, so that there are 2 large labels
above the drop-down list.
- The top label will be used to display the brand of car selected on the previous page.
- Change the ID of this label to BrandLabel and use the empty string for its text.
- Change the text for the label below this to "Select model of car from the pull-down:"
- Change the ID of the drop-down list to CarsList and make sure that AutoPostBack is True.
- Change the title of this page to "Cars" (with the company name appended if you like).
- Remember to go to HTML view and include the header.txt and footer.txt files. (If you have
forgotten where they go, just look at one of the old web pages such as contact.apsx.)
- Add an SqlDataAdapter to this form and set it up to use a new stored procedure called
SelectProdName.
- Use query builder to set up this stored procedure as shown here.
Note that you put all 3 of our tables into query builder: Product, Category, and CategoryProduct.
The stored procedure should return the ProdName field from the Product table and the CategoryName
field from the Category table for all rows of the joined table where the @TargetBrand parameter
matches the CategoryName. This is because we want to get a list of the names of the products (cars)
for the selected category (brand). We will put this list of product names into the drop-down
list on the cars page. We also order this data by ProdName.
- Do NOT generate insert, delete, or update stored procedures to go along with this stored procedure.
- The resulting stored procedure should look like this:
|
CREATE PROCEDURE studentc.SelectProdName
(
@TargetBrand char(24)
)
AS
SET NOCOUNT ON;
SELECT dbo.Product.ProdName, dbo.Category.CategoryName
FROM dbo.Product INNER JOIN dbo.CategoryProduct
ON dbo.Product.ProdNum = dbo.CategoryProduct.ProdNum
INNER JOIN dbo.Category ON dbo.CategoryProduct.CategoryNum = dbo.Category.CategoryNum
WHERE (dbo.Category.CategoryName = @TargetBrand)
ORDER BY dbo.Product.ProdName
GO
|
- As usual go to SQL Server Enterprise Manager and give the SQL or IWAM account Exec access
to this stored procedure. (Use whatever fits your server configuration.)
- In the Properties window for your new data adapter, expand the SelectCommand field. Replace
what is in the CommandText field with yourid.SelectProdSearch. (Of course, fill in your user
ID in place of yourid.) Say Yes and Yes to the questions asked.
- In the Properties window for the data adapter, try out the Preview Data link to see
if your stored procedure is working.
|
Cars Code-Behind File
- In the cars.apsx.vb code-behind file we set up the BrandLabel so that it displays the
brand name already chosen (and available here because we saved it in the session state).
- We also pass this brand name as the parameter to the stored procedure so that it can look
up the names of all the car models for this manufacturer (brand). These model names are
then used to populate the CarList drop-down list.
- If the user selects an item from the list (other than the "*** Make a choice ***" string)
we add the selected model name to the session state and redirect to the results.aspx page.
- Add
Protected dReader As System.Data.SqlClient.SqlDataReader in the usual spot
inside the Web Form Designer Generated Code region.
- The complete code for the Page_Load procedure should look like this:
|
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
'Put user code to initialize the page here
Dim TargetModel As String
If Not IsPostBack Then
Try
BrandLabel.Text = "Brand (manufacturer): " & Session("BrandSelected")
CarsList.Items.Add("*** Make a choice ***")
SqlConnection1.Open()
SqlDataAdapter1.SelectCommand.Parameters("@TargetBrand").Value =
Session("BrandSelected")
dReader = SqlDataAdapter1.SelectCommand.ExecuteReader
While (dReader.Read())
CarsList.Items.Add(dReader.GetString(0))
End While
Catch exception As System.Data.SqlClient.SqlException
ErrorLabel.Visible = True
Catch exception As Exception
ErrorLabel.Visible = True
Finally
SqlConnection1.Close()
End Try
Else ' Postback case:
TargetModel = CarsList.SelectedItem.Text
If Not Equals(TargetModel, "*** Make a choice ***") Then
Session.Add("ModelSelected", TargetModel)
Response.Redirect("results.aspx")
End If
End If
End Sub
|
Results Page
- Go to the results.aspx page.
- It should still contain old content about Br. David's Software.
- We want to revise this page as shown in this screen shot.
- Remove the download link and the image.
- The 4 labels and text box should remain but move them so that the items
simply go in sequence down the page.
- Change the ID of the top label to BrandLabel and make its text field empty.
- Change the ID of the next label to ModelLabel and make its text field empty as well.
- We will fill in these labels from the code-behind file.
- Change the title of the page to "Selected Product" (with the company name appended if you wish).
- Click on the data adapter at the bottom of this page and then click on the configure link
at the bottom of the Properties window.
- Set up the data adapter to use a new stored procedure named SelectModelBrand.
- Do NOT generate insert, delete, or update stored procedures to go along with this.
- The stored procedure should return the product description and product price for the brand and
model of car chosen by the user.
- Use query builder to set up this stored procedure
as shown here.
Once again you put all 3 of our tables into query builder: Product, Category, and CategoryProduct.
- The resulting stored procedure should look like this:
|
CREATE PROCEDURE studentc.SelectModelBrand
(
@TargetModel char(24),
@TargetBrand char(24)
)
AS
SET NOCOUNT ON;
SELECT dbo.Product.ProdDescription, dbo.Product.ProdPrice
FROM dbo.Product INNER JOIN dbo.CategoryProduct
ON dbo.Product.ProdNum = dbo.CategoryProduct.ProdNum
INNER JOIN dbo.Category ON dbo.CategoryProduct.CategoryNum = dbo.Category.CategoryNum
WHERE (dbo.Product.ProdName = @TargetModel) AND (dbo.Category.CategoryName = @TargetBrand)
GO
|
- Remember to go to SQL Server Enterprise Manager and give the SQL or IWAM account Exec access
to this stored procedure. (Use whatever fits your server configuration.)
- In the Properties window for your new data adapter, expand the SelectCommand field. Replace
what is in the CommandText field with yourid.SelectProdSearch. (Of course, fill in your user
ID in place of yourid.) Say Yes and Yes to the questions asked.
- In the Properties window for the data adapter, try out the Preview Data link to see
if your stored procedure is working.
|
Results Code-Behind File
- The existing Page_Load code in this file needs to be modified a bit though the overall
design is much the same.
- In particular, we now have 2 parameters to fill in for the stored procedure, namely the
model and brand of car.
- The revised code should look like this:
|
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
'Put user code to initialize the page here
Dim Price As Decimal
Dim Model As String
Dim Brand As String
BrandLabel.Visible = False
ErrorLabel.Visible = False
ModelLabel.Visible = False
PriceLabel.Visible = False
ProductDescriptionBox.Visible = False
If Not IsPostBack Then
If Session.Count = 0 Then ' No session items were supplied.
ErrorLabel.Visible = True
Else
Try
Model = Session("ModelSelected")
Brand = Session("BrandSelected")
SqlConnection1.Open()
SqlDataAdapter1.SelectCommand.Parameters("@TargetModel").Value = Model
SqlDataAdapter1.SelectCommand.Parameters("@TargetBrand").Value = Brand
SqlDataAdapter1.Fill(dTable)
If dTable.Rows.Count = 0 Then
ErrorLabel.Visible = True
Else
ProductDescriptionBox.Text = dTable.Rows.Item(0).Item(0)
ProductDescriptionBox.Visible = True
Price = dTable.Rows.Item(0).Item(1)
PriceLabel.Text = "Price: " & String.Format("{0:C}", Price)
PriceLabel.Visible = True
BrandLabel.Text = "Selected Brand: " & Brand
BrandLabel.Visible = True
ModelLabel.Text = "Selected Model: " & Model
ModelLabel.Visible = True
End If
Catch exception As System.Data.SqlClient.SqlException
ErrorLabel.Visible = True
Catch exception As Exception
ErrorLabel.Visible = True
Finally
SqlConnection1.Close()
End Try
End If
End If
End Sub
|
Search Page
- Change this page so that the first label reads
"You can search for cars whose brand name, model name,".
- Then have the second label continue with "or description contains a desired term or substring."
- Add the required field validator and the same regular expression validator that we
used in e-commerce app 4.
- Change the title of the page to "Search" (with the company name appended if you wish).
- You can refer to this screen shot of the search page.
- No changes should be needed to the code-behind file for this page.
|
Search Results Page
- Open SearchResults.aspx.
- Change the title of the page to "Search Results" (with the company name appended if you desire).
- The rest of the contents of the page should be fine, including the Property Builder settings
for the data grid, where we set things up to use Previous and Next buttons for paging and to
use push buttons labelled View to allow the user to view the details on any one product.
- Click on the data adapter at the bottom of this page and then click on the configure link
at the bottom of the Properties window.
- Set up the data adapter to use a new stored procedure named SelectLikeBrandModelDesc.
- Do NOT generate insert, delete, or update stored procedures to go along with this.
- The stored procedure should return the product name and category (brand) name for any
cars whose brand name, model name, or description contain the parameter value as a substring.
- Use query builder to set up this stored procedure
as shown here.
Once again you put all 3 of our tables into query builder: Product, Category, and CategoryProduct.
- The resulting stored procedure should look like this:
|
CREATE PROCEDURE studentc.SelectLikeBrandModelDesc
(
@Target varchar(24)
)
AS
SET NOCOUNT ON;
SELECT dbo.Product.ProdName, dbo.Category.CategoryName
FROM dbo.Product INNER JOIN dbo.CategoryProduct
ON dbo.Product.ProdNum = dbo.CategoryProduct.ProdNum
INNER JOIN dbo.Category ON dbo.CategoryProduct.CategoryNum = dbo.Category.CategoryNum
WHERE (dbo.Product.ProdName LIKE '%' + @Target + '%')
OR (dbo.Category.CategoryName LIKE '%' + @Target + '%')
OR (dbo.Product.ProdDescription LIKE '%' + @Target + '%')
ORDER BY dbo.Category.CategoryName, dbo.Product.ProdName
GO
|
- Remember to go to SQL Server Enterprise Manager and give the SQL or IWAM account Exec access
to this stored procedure. (Use whatever fits your server configuration.)
- In the Properties window for your new data adapter, expand the SelectCommand field. Replace
what is in the CommandText field with yourid.SelectProdSearch. (Of course, fill in your user
ID in place of yourid.) Say Yes and Yes to the questions asked.
- In the Properties window for the data adapter, try out the Preview Data link to see
if your stored procedure is working.
|
Search Results Code-Behind File
- No changes should be needed in the Page_Load procedure in this file.
- Nor are any changes needed in the DataGrid1_PageIndexChanged procedure.
- You will, however, need to change the DataGrid1_ItemCommand procedure that
handles things when the user clicks on a View button to see the details of a particular car.
In particular, you need to put both the model and brand selected into the session state before
redirecting to the results page. Change the code to match the following:
|
Private Sub DataGrid1_ItemCommand(ByVal Source As Object, _
ByVal e As DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
'Note that e.Item is the row where the user clicked a View button
If e.CommandName = "View" Then
Session.Add("ModelSelected", e.Item.Cells(1).Text)
Session.Add("BrandSelected", e.Item.Cells(2).Text)
Response.Redirect("results.aspx")
End If
End Sub
|
- Recall that e.Item.Cells(1) refers to column 1 of the row in the data grid that was clicked.
This is where we have the model name.
- Note that numbering starts at 0, so that column 0 is the one containing the View buttons.
- Column 2 is the last column in the data grid and is where we have the brand name.
- The results.aspx page is the same one that the cars.aspx page sends the user to when a particular
car is selected. Here we redirect to the same results page for displaying the detailed
information on the selected car, identified by model (ProdName) and brand (CategoryName).
|
Testing Your App
- Build your project.
- View your new web app in a browser and check that all pages display correctly
and that the database-related code works on all of the pages that use this.
- Be sure to check that you can look up a product (car) by starting at the brands page
where you select a brand of car, then getting redirected to the cars page where you select a
model of car, and finally getting redirected to the results page where you see the
details on this car.
- On the search results page be sure to try out the Next and Previous links to see
that the paging works correctly. Also check the View buttons to see that they
send you to the results page for detailed information on the selected product.
|
|