A348 Home Work 3 - Project  Databases/XML/XSL

 Modified: 

Overview 

The stock trading site has two main functions: managing access to the site and managing site functions.

Managing access - Home work 2 completed the logic for registration (Registration.asp) and logging in (Login.asp) of stock traders and logging out the current trader out (Logout.asp). The user view to manage access appears at right.

Managing function - This home work will complete the bulk of logic for the site function. The user view to manage site function appears at right. The new left menu provides access to the functions after user login, the center provides display of function results.

However, our concern will not be the user interface but managing the data and maintaining system integrity. In the following, we will develop methods that allow testing each module without a user interface.

There are three main site functions.

  1. Account - Displays current account information such as name, address and balance.
  2. Portfolio - Displays the stocks owned, the value and total worth at current price.
  3. Shopping cart - The shopping cart metaphor describes much of the fundamental site operation.

The major logic of the site functionality is implemented in the following scripts:

  1. List current stock holdings and value (Portfolio.asp) - Final version will be completed in class.
  2. List the trader summary (TraderSummary.asp) - Mostly completed in class.
  3. List shopping cart contents (CartSummary.asp).
  4. List available stocks to buy (BuyList.asp).
  5. Buy stocks from those available (BuyStock.asp) and place in shopping cart.
  6. List stocks owned to sell (SellList.asp).
  7. Sell stocks from those owned (SellStock.asp) and place in shopping cart.

Review of Portfolio.asp

The portfolio is the list of an individual's stock symbol, stock name, shares owned, price, stock value and total worth. The script:

  1. JOINs Portfolio and Stock tables on SYMBOL and SELECTs records where ID='TraderID' to produce the necessary fields in a record set,
  2. generates the XML from the record set,
  3. transforms the XML into HTML using the XSL file PortfolioContent.htm.
Portfolio.ASP
<%@ Language=JScript%>
<%	
   if(Session("trader") == undefined)
      Response.Redirect("Login.xml");

   conn = Server.CreateObject("ADODB.Connection"); 
   conn.Mode = 3;
   conn.Open ("DSN=Project");
   rs = conn.Execute(
     "SELECT PORTFOLIO.SYMBOL, PRICE, SHARES, " +
     "SHARES*PRICE as STOCKVALUE " +
     "FROM STOCK INNER JOIN PORTFOLIO ON " +
     "STOCK.SYMBOL = PORTFOLIO.SYMBOL " +
     "WHERE ID='TraderID';");

   var xmlDoc = Server.CreateObject("Msxml2.DOMDocument");
   xmlDoc.async = false;
   rs.Save (xmlDoc, 1) ;        // Convert recordset to XML
                                           // Save as XML
   xmlDoc.save(Server.MapPath("Portfolio.xml"));

   var xsl = Server.CreateObject("Msxml2.DOMDocument");
   xsl.async = false;              // Convert XML tree using XSL   
   xsl.load(Server.MapPath("PortfolioContent.htm"));
                                            // Write tree as HTML
   Response.Write(xmlDoc.transformNode(xsl)); 
   conn.Close();
%>

Portfolio Summary

UAL - 45 - 7.6400 - 343.8000
IBM - 15 - 15.0500 - 225.7500
 

 
PortfolioContent.htm
<xsl:stylesheet version="2.0" 
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
 xmlns="http://www.w3.org/1999/xhtml" 
 xmlns:rs="urn:schemas-microsoft-com:rowset" 
 xmlns:z="#RowsetSchema">

   <xsl:output method="html" />

   <xsl:template name="PortfolioContent">
      <h1>Portfolio Summary</h1>
      <xsl:for-each select="xml/rs:data/z:row">
         <xsl:value-of select="@SYMBOL" />
         -
         <xsl:value-of select="@SHARES" />
         -
         <xsl:value-of select="@PRICE" />
         -
         <xsl:value-of select="@STOCKVALUE" />
         <br />
      </xsl:for-each>
   </xsl:template>

   <xsl:template match="/">
      <xsl:call-template name="PortfolioContent" />
   </xsl:template>
</xsl:stylesheet>

 

Step 1

  • Access
  1. Open Access from Start | Programs | Microsoft Access
  2. Open File | Open... and select C:\Project\Project.mdb
  3. Select Tables | TRADER
    • Add 2 new records with fields ID, FIRSTNAME, LASTNAME, BALANCE.
    • Save and close the table.
  4. Select Tables | STOCK
    • Note the stock SYMBOL.
  5. Select Tables | PORTFOLIO
    • Add 3 new records with fields ID, SYMBOL and SHARES. Use an ID from Trader table and a SYMBOL from Stock table.
    • Save and close the table.

 

  1. Download Home Work 3 files.
  2. Unzip to the C:\Project directory. Overwrite Global.asa, Portfolio.asp and PortfolioContent.htm but no other of your existing files!

 

  1. Open ForntPage.
  2. Open C:\Project\Portfolio.asp
  3. Modify Portfolio.asp to work for ID's in your Portfolio database table.
    • Change ID='TraderID' to an ID in Trader table.
  4. Login and test by the address line in the browser of: localhost/Project/Portfolio.asp
    • If you receive the Login page, you are either not logged in or the system is in an ambiguous state (the Trader table indicates that you are logged in but the Session("trader") variable is undefined. For now, comment out the following of Portfilio.asp:
      •   // if(Session("trader") == undefined)
          //    Response.Redirect("Login.xml");
         
  5. Your results should appear similar to above Portfolio Summary.

 

Session("trader")

Each trader session is tracked after logging in by the Session("trader") variable which should be the trader's ID. When David is logged in Session("trader") is 'David'. Each script has access to the Session("trader") variable.

In the Portfolio.asp script SELECT, rather using a fixed ID, we need to use instead Session("trader"). We also need an easy, sure way to initialize Session("trader") to a known value without going through the log in process each time.

Step 2

  • SessionTrader.asp
  1. In FrontPage open SessionTrader.asp (has been unzipped from HW3.zip to C:\Project).
  2. Modify the "ID" value to one of your ID's in the Traders table.
  3. Test by loading SessionTrader.asp in the browser.
  • Portfolio.asp
  1. Modify to use the Session("trader") variable in the SELECT statement WHERE ID= clause.
  2. To turn on checking that user logged in, uncomment: 

      if(Session("trader") == undefined)
          Response.Redirect("Login.xml");
     

  3. Test. Results should be similar to Step 1.

 

SELECTing and Displaying

The portfolio should also include the NAME of the stock. We need to:

  1. SELECT the NAME field in Portfolio.asp
  2. Display @NAME in PortfolioContent.htm.

Step 3

  • Portfolio.asp

    Modify the SELECT statement for selecting NAME field also.

     

  • PortfolioContent.htm
    1. Add a <xsl:value-of select="@NAME" /> to display the NAME of the stock.
    2. Test by loading Portfolio.asp in the browser.

 

TraderSummary.asp

The script is similar to Portfolio.asp script but without the JOIN as only the Trader table is consulted.

  1. SELECT * FROM Trader WHERE ID=Session("trader")
  2. Convert the record set to XML.

    var xmlDoc = Server.CreateObject("Msxml2.DOMDocument");
    xmlDoc.async = false;
    rs.Save (xmlDoc, 1) ;

         

  3. Save a copy of the XML in case needed for testing.

    xmlDoc.save(Server.MapPath("TraderSummary.xml"));

     

  4. Transform the XML using an XSL file.

    var xsl = Server.CreateObject("Msxml2.DOMDocument");
    xsl.async = false;             
    xsl.load(Server.MapPath("TraderSummaryContent.htm"))

     

  5. Write the transformation to the browser.

    Response.Write(xmlDoc.transformNode(xsl));

Step 1

  • SQL
  1. Open the Project.mdb file in Access
  2. Click on Queries | Create Query in Design View
  3. Close the Show Table dialog.
  4. Click in menu: View | SQL View
  5. Copy and paste the following, changing 'TraderID' to one of your Trader table IDs.

SELECT * FROM Trader WHERE ID='TraderID'

  1. Click in menu: Query | Run
  2. Click in menu: View | SQL View
  • ASP
  1. Open Portfolio.asp and save as TraderSummary.asp
  2. Edit TraderSummary.asp:
    • Copy the above SELECT and paste into script.
    • Change "Portfolio.xml" to "TraderSummary.xml"
    • Change "PortfolioContent.htm" to "TraderSummaryContent.htm".
  3. Test by loading TraderSummary.asp into a browser. You should get an error because TraderSummaryContent.htm is not yet defined.

 

TraderSummaryContent.htm

The transformation of XML to HTML uses this style sheet. For testing purposes, it can be very basic, patterned after the PortfolioContent.htm style sheet.  

Step 2

  • Style Sheet - Test
  1. In FrontPage, open  PortfolioContent.htm and save as TraderSummaryContent.htm
  2. Save and test by loading TraderSummary.asp. The output won't be correct until the TraderSummaryContent.htm style sheet is modified.
  • Style Sheet - Modification

The output problem is due to the mismatch between the Trader table fields of SYMBOL, SHARES, etc. and those in the TraderSummaryContent.htm style sheet of @ID, @BALANCE, etc.

  1. Edit the TraderSummaryContent.htm file.
  2. Change the Portfolio table field names to those of the Stock table:
    • SYMBOL to ID
    • SHARES to FIRSTNAME
    • PRICE to LASTNAME
    • STOCKVALUE to BALANCE
  3. Save and test by loading TraderSummary.asp in a browser. The output should look better.

 

 

Errors

Step 3

  • ASP Syntax - These are usually clearly pointed out by ASP. You've seen the type of error message:

Expected ')' /project/tradersummary.asp, line 9

  • XSL Syntax - These are clear also after some interpretation. The error is in the TraderSummaryContent.htm file, the XSL style sheet. It occurred when the style sheet was applied to the XML.

msxml3.dll (0x80004005)
The stylesheet does not contain a document element. The stylesheet may be empty, or it may not be a well-formed XML document. /project/tradersummary.asp, line 17

  • Line 17 would be: Response.Write(xmlDoc.transformNode(xsl));
  • msxsl - Applies the XSL style sheet to XML and displays useful error messages. Assuming that your TraderSummary.asp script produced a TraderSummary.xml file:
  1. Open a COMMAND prompt window.
  2. Change to the C:\Project directory.
  3. Test by:

    C:\Project>msxsl Tradersummary.xml TraderSummaryContent.htm

    Error occurred while parsing document.

    Code: 0xc00ce504
    URL: file:///C:/Project/TraderSummaryContent.htm
    Line: 21
    Column: 3
    A name was started with an invalid character.

 

BuyList.asp and BuyListContent.htm

List all the stocks in the Stock table. The script is very similar to TraderSummary.asp and testing style sheet BuyListContent.htm can be very similar to TraderSummaryContent.htm file.

To write the BuyListContent.htm style sheet, modify the TraderSummaryContent.htm file with the names of the Stock table fields, SYMBOL, NAME, and PRICE.

Add records if necessary to the Stock table using Access. Be sure to close the table before running scripts.

 

CartSummary.asp and CartSummaryContent.htm

The shopping cart metaphor holds the stocks that are to be bought or sold. The Cart table holds all carts for all traders, each distinguished by the unique trader ID field.

List all the entries in the shopping cart for the current trader ID (i.e. select on trader ID). The script is very similar to TraderSummary.asp and testing style sheet can be very similar to TraderSummaryContent.htm file.

To write the CartSummaryContent.htm style sheet, modify the TraderSummaryContent.htm file with the names of the Cart table fields, TRADEID, ID, SYMBOL, SHARES, PRICE, and BUY.

If necessary, add records with the ID that matches Session("trader") to the Cart table using Access. Be sure to close the table before running scripts. Note that TRADEID is automatically generated.

Assignment

Complete:

  1. List current stock holdings and value (Portfolio.asp) - Final version will be completed in class.
  2. List the trader summary (TraderSummary.asp) - Mostly completed in class.
  3. List shopping cart contents (CartSummary.asp).
  4. List available stocks to buy (BuyList.asp).

Turn in

  1. Project directory.
    1. FTP to www.csci.ius.edu
      • In browser address enter: ftp://www.csci.ius.edu
      • Login using your IUS username and password.
      • Copy the contents of the Project directory onto ftp page.
    2. Test in IE. The Welcome.xml is a default page.
      • In browser address enter: http://www.csci.ius.edu/username/Welcome.xml
  2. Email notification to rwisman@ius.edu with subject: YOUR NAME - A348 HW3