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. 
      
  2. Unzip to the W:\N342\Project directory. You will need to overwrite 
		Global.asa, 
      Portfolio.asp and PortfolioContent.xsl files.
    3. 
    

    
    
      
  • Access
    • 
    

    
    
      
  1. Open Access
    2. 
      
  2. Open File | Open... and select W:\N342\Project\Project.mdb 
    3. 
      
  3. Select Tables | TRADER
      
        
    • Add 2 new records with fields ID, FIRSTNAME, LASTNAME, BALANCE.
      • 
        
    • Save and close the table.
      • 
      
    
      
    4. 
      
  4. Select Tables | STOCK
      
        
    • Note the stock SYMBOL.
      • 
      
    
      
    5. 
      
  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.
      • 
      
    
      
    6. 
    

    
    
		
  • ASP
    • 
	

    
    
      
  1. Open FrontPage.
    
 
    2. 
      
  2. Open 
      W:\N342\Project\Portfolio.asp
    
 
    3. 
      
  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. 
      
  4. Test by the address line in the browser of: 
		iu-uits-ediwp1.ads.iu.edu/username/N342/Project/Portfolio.asp
    
 
    5. 
		
  5. Your results should appear similar to above Portfolio Summary.
    6. 
    

    		
  




 


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. 
      
  2. Modify the "TraderID" value to one of your ID's in the Traders table.
    3. 
      
  3. Test by loading SessionTrader.asp in the browser.
    4. 
    

    
    
      
  • 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. 
		
  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. 
      
  3. Test. Results should be similar to Step 1.
    4. 
    

    		
  




 


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. 
  
  2. Display @NAME in PortfolioContent.xsl
    3. 




  

    
    
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. 
        
    2. Test by loading Portfolio.asp in the browser.
      3. 
      
    
      
    • 
    

    		
  




 


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. 
  
  2. Convert the record set to XML.
    var xmlDoc = Server.CreateObject("Msxml2.DOMDocument");
    
  xmlDoc.async = false;
    
  rs.Save (xmlDoc, 1) ; 
    
  
          
    3. 
  
  3. Save a copy of the XML in case needed for testing.
    
  xmlDoc.save(Server.MapPath("TraderSummary.xml"));
    
  
     
    4. 
  
  4. Transform the XML using an XSL file.
    var xsl = Server.CreateObject("Msxml2.DOMDocument");
    
  xsl.async = false;             
  
    
  xsl.load(Server.MapPath("TraderSummaryContent.xsl"))
    
  
     
    5. 
  
  5. Write the transformation to the browser.
    
  Response.Write(xmlDoc.transformNode(xsl)); 
    6. 




  

    
    
Step 1

    
    
      
  • SQL
    • 
    

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

    

      

        
SELECT * FROM Trader WHERE ID='TraderID'

      

    

    
    
      
  1. Click: Query | Design | Run
    2. 
      
  2. Click: Query | Design | SQL View
    3. 
    

    
    
      
  • ASP
    • 
    

    
    
      
  1. In FrontPage, open Portfolio.asp and save as TraderSummary.asp
    
 
    2. 
      
  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. 
      
  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.
    		
		
    
		
    
		
    4. 
    

    		
  




 


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. 
      
  2. Save and test by loading TraderSummary.asp. The output won't be 
		correct until the TraderSummaryContent.xsl style sheet is modified.
    3. 
    

    
    
      
  • 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. 
		
  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. 
		
  3. Change output heading from Portfolio to Trader Summary.
    
 
    4. 
		
  4. Save and test by loading TraderSummary.asp in a browser.
    5. 
    

  




 



  

    
    
Step 3

    
    
      
  • TraderSummary.asp
    • 
    

    
    
      
  1. Modify to use the Session("trader") variable in the SELECT statement 
      WHERE ID= clause. 
    
 
    2. 
		
  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. 
		
  3. Test. Results should be similar to Step 2.
    4. 
    

    		
  




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. 
			
  2. Open a COMMAND prompt window.
    3. 
			
  3. Change to the W:\N342\Project directory.
    4. 
			
  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.    		
					
    
				
    
			
    5. 
		

	

    		
  




 


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




 


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. 
	
    2. Upload Project.zip to your CSCI N342 OnCourse Drop Box using the 
	Display Name of HW3.
      
 
      3. 

    

	
    2. 
	
  2. Web server
    
 
      
		
    1. Files should be at W:\N342\Project 
      2. 
		
    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. 
	
    3. 
	
  3. Email notice of completion to rwisman@ius.edu with subject: YOUR NAME - N342 HW3 - 
	username
    4.