MT Select - Sample ASP Files Multi-Table Select Motes Notes...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Sample ASP Files – Multi-Table Select Mote’s Notes 4/13/2011 Join1A.asp This is a report of the employees and their current (i.e., 4/1/2011) paychecks. The recordset is based on an inner join of tblEmployee and tblPaycheck with a hardwired date of 4/1/2011 in the WHERE clause. [Rarely is it acceptable to hardwire values in queries and web pages. The purpose of this first example is to demonstrate how to do a join. A more powerful version of the page would use a subquery to determine the date of the most recent paychecks, then use that dynamic value in the WHERE clause instead of the hardwired date.] <html> <head> <title>Join 1A</title> <link rel=stylesheet type='text/css' href='MyStyle.css'> </head> <!--#include file="MyInfo.asp" --> <body> <% Response.Write("<h2>Join 1A: 4/1/2011 Paychecks</h2>") ' Open connection to back-end SQL Server database Set conn=Server.CreateObject("ADODB.Connection") conn.CommandTimeout=15 conn.ConnectionTimeout=15 conn.Open BackEnd
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 ' Open employee/paycheck recordset sql="SELECT FirstName, LastName, tblEmployee.EID, JobTitle, GrossAmount, NetAmount FROM tblEmployee INNER JOIN tblPaycheck ON tblEmployee.EID=tblPaycheck.EID WHERE PayDate=' 4/1/2011 ' ORDER BY LastName, FirstName" Set rst=Server.CreateObject("ADODB.Recordset") rst.Open sql, conn ' Display employee/paycheck data Response.Write("<table border=0>") Response.Write("<tr>") Response.Write("<th align='left'>Employee</th>") Response.Write("<th align='center'>EID</th>") Response.Write("<th align='left'>Job Title</th>") Response.Write("<th align='right'>Gross</th>") Response.Write("<th align='right'>Net</th>") Response.Write("<th align='right'>WH</th>") Response.Write("</tr>") CountEmployees=0 SumGross=0 SumNet=0 Do Until rst.EOF CountEmployees=CountEmployees+1 SumGross=SumGross+rst("GrossAmount") SumNet=SumNet+rst("NetAmount") FullName=rst("FirstName") & " " & rst("LastName") WH=rst("GrossAmount")-rst("NetAmount") Response.Write("<tr>") Response.Write("<td align='left'>" & FullName & "</td>") Response.Write("<td align='center'>" & rst("EID") & "</td>") Response.Write("<td align='left'>" & rst("JobTitle") & "</td>") Response.Write("<td align='right'>" & FormatCurrency(rst("GrossAmount"),2) & "</td>") Response.Write("<td align='right'>" & FormatCurrency(rst("NetAmount"),2) & "</td>") Response.Write("<td align='right'>" & FormatCurrency(WH,2) & "</td>") Response.Write("</tr>") rst.MoveNext Loop Response.Write("<tr>") Response.Write("<th align='left'>Total</th>") Response.Write("<th align='center'>" & CountEmployees & "</th>") Response.Write("<th></th>") Response.Write("<th align='right'>" & FormatCurrency(SumGross,2) & "</th>") Response.Write("<th align='right'>" & FormatCurrency(SumNet,2) & "</th>") Response.Write("<th align='right'>" & FormatCurrency(SumGross-SumNet,2) & "</th>")
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 05/26/2011 for the course MIS 325 taught by Professor Mote during the Spring '08 term at University of Texas at Austin.

Page1 / 10

MT Select - Sample ASP Files Multi-Table Select Motes Notes...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online