{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

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

Info icon This 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
Image of page 1

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

View Full Document Right 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>") Response.Write("</tr>")
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern