N342 Home Work 3 - Project  Databases/XML/XSL

Modified

Note

  1. IE caches files by default which can prevent opening of a later version. The effect is particularly noticeable during repeated tests with an ASP file that has been changed but is not opened because of a cached version.

    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

     

  2. When running your ASP, receive error message about "Site may be down, try again later. Etc." IE, by default, does not display errors from a Web server.

    Turn OFF friendly error messages by, in IE:

    Tools | Internet Options | Advanced | Browsing | Uncheck "Show friendly HTTP error messages

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 much of the site functionality logic. 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 managing the data and maintaining system integrity, not the user interface. 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 figure at right illustrates that Portfolio component takes as input Stock and Portfolio data, outputting the results to PortfolioContent.

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.xsl.
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='Ray';");

   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.xsl"));
                                          // Write tree as HTML
   Response.Write(xmlDoc.transformNode(xsl));
   conn.Close();
%>

 

PortfolioContent.xsl
<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>

	



Portfolio Summary

MSFT - 100 - 10.1100 - 1011.000
IBM - 10 - 15.0500 - 150.500


    

Step 1

  • 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.xsl files.
  • Access
  1. Open Access
  2. Open File | Open... and select W:\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.
  • 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: iu-uits-ediwp1.ads.iu.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 ASP script in the Project directory has common 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 we test.

SessionTrader.asp will be modified to initialize Session("trader") variable to make it appear to other ASP scripts you have logged in.

Step 2

  • SessionTrader.asp
  1. In FrontPage open SessionTrader.asp (has been unzipped from HW3.zip to W:\N342\Project).
  2. Modify the "TraderID" 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="TraderID" had been.

    Yes, you'll need to use concatenation.
     

  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 in the listing.

Need to:

  1. SELECT the NAME field from the join in Portfolio.asp
     
  2. Display @NAME in PortfolioContent.xsl

Step 3

  • Portfolio.asp

    Modify the SELECT statement for selecting NAME field also.

    Note that the case of SELECT fields and XSL select must agree.

     

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

 

That completes Portfolio.asp for now.

The code, general development and debugging methods of Portfolio.asp can now be applied to other components of the site.

 

TraderSummary.asp

As described below, 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.xsl"))

     

  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 Create | Query Design
  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: Query | Design | Run
  2. Click: Query | Design | SQL View
  • ASP
  1. In FrontPage, open Portfolio.asp and save as TraderSummary.asp
     
  2. Edit TraderSummary.asp:
    • Copy the above SELECT, replacing the SELECT in TraderSummary.asp script.
    • Change "Portfolio.xml" to "TraderSummary.xml"
    • Change "PortfolioContent.xsl" to "TraderSummaryContent.xsl".
       
  3. Test by loading TraderSummary.asp into a browser.

    You should get an error similar to below because TraderSummaryContent.xsl is not yet defined.

    msxml3.dll error '80004005'

    The stylesheet does not contain a document element. The stylesheet may be empty, or it may not be a well-formed XML document.

 

TraderSummaryContent.xsl

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

Step 2

  • Style Sheet - Test
  1. In FrontPage, open PortfolioContent.xsl and save as TraderSummaryContent.xsl
  2. Save and test by loading TraderSummary.asp. The output won't be correct until the TraderSummaryContent.xsl 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.xsl style sheet of @ID, @BALANCE, etc.

  1. Edit the TraderSummaryContent.xsl 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. Change output heading from Portfolio to Trader Summary.
     
  4. Save and test by loading TraderSummary.asp in a browser.

 

Step 3

  • TraderSummary.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 2.

Errors

Step 4

  • 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.

    Create an error in TraderSummaryContent.xsl by deleting > in:

    </xsl:for-each
     

    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

    The error is in the TraderSummaryContent.xsl file, the XSL style sheet.

    It occurred when the style sheet was applied to the XML at Line 17.

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. Download and save msxsl.exe to the W:\N342\Project directory.
  2. Open a COMMAND prompt window.
  3. Change to the W:\N342\Project directory.
  4. Test by:

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

     
    Error occurred while parsing document.

    Code: 0xc00ce505
    URL: file:///W:/N342/Project/TraderSummaryContent.xsl
    Line: 19
    Column: 2
    A name contained an invalid character.

 

BuyList.asp and BuyListContent.xsl

List all fields of all the stocks in the Stock table, open the Stock table to determine the fields.

The ASP script is very similar to TraderSummary.asp and the testing style sheet BuyListContent.xsl can be very similar to TraderSummaryContent.xsl file.

To write the BuyListContent.xsl style sheet, modify the TraderSummaryContent.xsl 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.xsl

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 fields for 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.xsl file.

To write the CartSummaryContent.xsl style sheet, modify the TraderSummaryContent.xsl 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.xsl).
  2. List the trader summary (TraderSummary.asp and TraderSummaryContent.xsl).
  3. List available stocks to buy (BuyList.asp and BuyListContent.xsl).
  4. List shopping cart contents (CartSummary.asp and CartSummaryContent.xsl).

 

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. Web server
     
    1. Files should be at W:\N342\Project
    2. Test in IE. The Welcome.xml is a default page.
      • In browser address enter: http://iu-uits-ediwp1.ads.iu.edu/username/N342/Project/Welcome.xml
         
  3. Email notice of completion to rwisman@ius.edu with subject: YOUR NAME - N342 HW3 - username