COMP2121 Practical Session 7 – Online Orders
The next stage is to provide a simple orders generation page.
Now you have a products table, a customers table, and dynamic web pages that interact with those tables, you have the beginnings of an on-line shopping system. However, for such a system to be viable from a trading point of view it needs to generate orders for customers. As a first stage, we will develop a system that creates single product orders. If you study the e-commerce technologies module next year, you will create a full shopping cart system that even includes automatic updating of stock levels.
To create a system, you need to logically link the database tables. However, the customer and product entities have a many-many relationship so a direct link is not possible... how can this be resolved?
Yes, of course, a new entity. The new entity can simply be called order (entities are singular) and the associated table can be called orders.
Exercise 7(a) Creating and Linking the Orders Table
To become part of a database system, the orders entity will need to contain a link field from the existing entities product and customer. This will have to be done using the associated products and customers table:
1. Open the database from previous exercises containing the two tables
2. Create and save the orders table, as described above. It will need to have three fields – one for its own unique identifier, orderID, and another for each of the foreign keys of customerID and productID. Add a single record, using a productID and customerID that you know already exists.
3. Use the Database Tools/Relationships option in Access to reveal the three data tables. Now drag-and-drop from the primary key on customers to the foreign key on orders. Tick to enforce referential integrity. Repeat for products. This will create many:1 relationships between order and product, and order and customer, based on the common fields. Read the diagram over the page as including ID after each field in the orders table
4. Save and close the completed database file.
Exercise 7(b) Creating the "New Orders" Page
In this example, you’ll be creating a page that generates orders for one product only… Online order systems can rapidly get very complex, and this is as easy as it gets.
1. Now go into Visual Studio, and open your website.
2. Create a blank page, that uses code behind, which you will use product and customer information to generate actual orders, which can then be converted into single-product invoices for customers. Add a suitable title to the page (e.g Orders), and save as createorders.aspx
3. Read this… A valid order can only be created using valid productIDs and valid customerIDs. This has been enforced in the database, through the referential integrity tickbox. However, the person entering data will not necessarily be able to recognise the ID fields necessary to create that order.
A drop down listbox (toolbox) will therefore be needed to provide a suitable selection of input data that will generate the two ID fields that will be required to make an order. As this has to be real data, the listboxes have to take selections from existing database entries. Sounds complicated, but we are building on existing knowledge. Hopefully you are aware of a simplified version of a list box from earlier HTML work.
The data to populate each list box will therefore come from an accessdatasource, so you’ll need to create two listboxes coupled to two datasources each linked to a specific data table. Hope that’s clear… here we go…
4. Type a suitable header for your first listbox (Customers), and hit the ENTER key. Drag and drop an accessdatasource and drop down box to the cursor. Configure the datasource to display custname and customerID.
5. Now use the smart tag on the listbox to link to the datasource, and select custname as the field to display, and customerID as the field to pass on. Check the “Enable PostBack” box. This will take away the need for a button (see below)
6. Save and Run the page to make sure the dropdown displays as expected.
7. Repeat 4-6 to create a mechanism for displaying your ProductName and selecting ProductID
8. If you’ve done everything right, the dropdown lists of customer surnames and product names will be displayed (illustrated below).
9. Now, use your web design skills to improve the appearance of the page, which will be used to create single item orders. Save the page, and make sure it still works.
The next trick is to access the IDs for the data selected, and then use them to create new orders which can be added to the orders table. Next exercise…
Exercise 7(c): Completing the Save Orders Page
You may remember from a previous exercise that the label control can be used to display variables. In this exercise, you’ll use the label twice, and then use the data displayed to create an order form that creates the order.
1.Create some space between and under the dropdown boxes to display the label text. Now drag and drop two label controls into the spaces. Look at the source code – they should have the IDs of label1 and label2.
2.Now look at the code for the drop down boxes. These will probably have the IDs dropdownlist1 and dropdownlist2. In a dropdownlist, the ID assumes the value selected in the drop down. What now needs to be done, therefore, is to pass the values held in dropdownlist1 and dropdownlist2 into label1 and label2. To do this, you need to go into the code behind page, and create a simple C# function, as you did before. The full function is:
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = DropDownList1.Text;
Label2.Text = DropDownList2.Text;
}
If you forgot to create a code behind page, you can still use the code – just use <script>..code…</script> at the top of the page under the page declaration.
3. Once you’ve added this code, save the page and code behind page. Try running again. No Numbers should initially be displayed. Change the selections, and see what happens to the labels. These are the productID and customerID needed to create an order from the highlighted selections. Thanks to the postback facoility, you can change the drop downs independently, for convenience.
4. The final stage is to create the form using DetailsView that will create the order. This will need to be underneath all your existing controls. Same logic as last week, but you’ll only need the “insert” option to be ticked. Over to you… remember to remove orderID from the form and query (why?)
5.Save again, and test out the system. The database will initially present details of an existing order, so click on NEW. Enter the data (make sure you get the right box…) and click INSERT. Check results in the database to make sure that the order has been written. If you get time, create another page that will display existing orders, and figure out a way for this page to be displayed when the new order has been created.
6.OK. You’ve now created a simple facility for ordering one item of a particular product that could work online. What now needs to be done to create a proper on-line order. We’ll continue with this next week….
RCH111