N342 Home Work 3 - Project  Databases/XML/XSL

Modified

Note

IE caches files by default which can prevent opening of a later version. The effect is particularly noticeable during repeated tests of HTML forms referencing ASP files that have been changed but are not opened because of a cached version of the HTML.

The solution is to require IE to check for new versions on every visit to a page. In IE: Tools | Internet Options | Temporary Internet Files | Settings | Every Visit to Page

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 ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + 
                      Server.MapPath("Project.mdb"));
   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
  2. Open File | Open... and select C:\N342\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 ID from Trader table and a SYMBOL from Stock table.
    • Save and close the table.
  • Files
  1. Download Home Work 3 files.
  2. Unzip to the W:\N342\Project directory. You will need to overwrite Global.asa, Portfolio.asp and PortfolioContent.htm files.
  • ASP
  1. Open FrontPage.
  2. Open W:\N342\Project\Portfolio.asp
  3. Modify Portfolio.asp to work for ID's in your Portfolio database table and disable logged in checking.
    • In the SELECT, change ID='TraderID' to an ID in your Trader table.
    • For testing Portfolio.asp without checking that a trader is logged in, comment out the following of Portfolio.asp

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

  4. Test by the address line in the browser of: www.ius.edu/username/N342/Project/Portfolio.asp
  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 W:\N342\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. Although not necessary while testing, to turn on checking that user is 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. In FrontPage, open Portfolio.asp and save as TraderSummary.asp
  2. Edit TraderSummary.asp:
    • Copy the above SELECT and paste into ASP 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 Trader 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 probably 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 W:\N342\Project directory.
  3. Test by:

    W:\N342\Project>msxsl Tradersummary.xml TraderSummaryContent.htm

    Error occurred while parsing document.

    Code: 0xc00ce504
    URL: file:///W:/N342/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 the 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 save 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 save the table before running scripts. Note that TRADEID is automatically generated.

Hints

Assignment

Complete:

  1. List current stock holdings and value (Portfolio.asp and PortfolioContent.htm).
  2. List the trader summary (TraderSummary.asp and TraderSummaryContent.htm).
  3. List available stocks to buy (BuyList.asp and BuyListContent.htm).
  4. List shopping cart contents (CartSummary.asp and CartSummaryContent.htm).

Turn in - Due before class on the date listed in the syllabus.

  1. OnCourse Drop Box
    1. Compress the Project folder containing the homework files.
      • In Windows Explorer, right-click on the Project folder.
      • Click Send to then click Compressed (zipped) Folder.
      • Project.zip will be created.
    2. Upload Project.zip to your CSCI N342 OnCourse Drop Box using the Display Name of HW3.
  2. IUS server
    1. Copy to W: drive or FTP to webftp.ius.edu
      • In browser address enter: ftp://webftp.ius.edu
      • Login using your IUS ADS\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.ius.edu/username/N342/Project/Welcome.xml
  3. Email notification to rwisman@ius.edu with subject: YOUR NAME - N342 HW3 - username