2000 Midterm 2 (50 points)
CSC 123 - Server-Side Web Programming with Active Server Pages
1. True or False: ADO is one of ASP's intrinsic objects.
2. You are the new lead ASP programmer for the MegaCard, a well-funded dot-com startup selling baseball cards online. The marketing division wants a list of sites that are forwarding people directly to your site's home page (i.e., have links on their sites that people use to get to your home page). How could you use ASP to get this information, and how would you save the information (be specific)? (2 points)
Add code to the home page that writes the value of request.servervariables("HTTP_REFERRER") to a database for each request.
3. A colleague of yours at MegaCard has been arguing to continue using MS Access as the database backend for the MegaCard ASP pages (MegaCard is a "Microsoft shop"). The president of MegaCard calls you to a meeting and asks if you believe this is the best approach for the company. What would you say? What supporting reasons would you offer? (3 points)
Should use SQL Server (also Oracle, or other service-based data engine ok).
•Can support many more users then a file-based database such as Fox or Access.
•Supports advanced facilities such as multi-step transactions over different machines.
•Faster performance and throughput.
•It allows work to be deferred to the server rather then all the work being done on the client as in a file based data systems.
4. MegaCard's marketing division chief approaches you and says: "I need a really simple web page that outputs all of the data in the CATALOG table—no special formatting, no paging, no column captions, just a quick dump of the contents into an HTML table. And I need it in 2 minutes!” Complete the page below using the shortest and simplest code you can (its doable in 8 lines!). (5 points)
<%@ language=VBScript %>
<% option explicit %>
const cDSN = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=d:\inetpub\data\megacard.mdb; DefaultDir=d:\inetpub\data\;FIL=MS Access;DriverId=25; UID=admin;PWD=; "
<title>Catalog Table Contents</title>
<meta name="description" value="Description of this page…">
<h1>Catalog Table Contents</h1>
sSQL = “Select * from Catalog”
set rs = Server.CreateObject(“ADODB.Recordset”)
rs.open sSQL, cDSN
Response.write rs.GetString(,,”</td><td>”,</td></tr><tr><td>”,” ”)
set rs = Nothing
for I = 0 to rs.fields.count – 1
response.write “<td> & rs.fields(i).value & “</td>”
5. Write an ASP code fragment
that outputs an HTML table with data from the SQL Server table called
"Products" (see below). Don't
worry about the page's initial ASP/HTML code or footer, but make use of the code
fragment below. Have the “Description”
text hyperlink to a fictitious page 'buyprod.asp?VW=1&ID=" where you
would follow the ID field with the primary key of the table below. Use a
"firehose" cursor for this page. Your output should include headers
for the HTML columns and format the
ProductID (Primary Key)
const cDSN = "Driver=SQL Server;Server=SQLSERV;Database=MegaCard;UID=sa;PWD=;"
sSQL = "Select * from Products"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open sSQL, cDSN
Response.Write "<tr><td>Description</td><td>Qty Ordered</td><td>Cost</td></tr>"
While not rs.eof
Response.Write "<tr><td><a href=’BuyProd.asp?FLAG=1&ID=" & rs("ProductID") & "’> & rs("Description"> & "</a></td>"
Response.Write "<td>" & rs("QuantityOrdered") & "</td>"
Response.Write "<td>" & FORMATCURRENCY((rs("Cost"),2) & "</td></tr>"
Set rs = Nothing
Using the diagram below, develop SQL statements as described below: (15 points)
6. Write a statement that returns the student’s first and last name, ClassID, and letter grade for StudentID 234 and ClassID 5. (2 points)
SELECT Student.FirstName, Student.LastName, StudentGrade.ClassID, StudentGrade.Grade FROM Student INNER JOIN StudentGrade ON Student.StudentID=StudentGrade.StudentID WHERE ClassID=5 and StudentID=234
7. Write a statement to insert a new grade record for student 234 of "B+" and 89.5 points for ClassID 5 during Fall (code is 'FL') 2000. (1 points)
INSERT INTO StudentGrade (StudentID, Year, Semester, GradePoints, Grade, ClassID) VALUES (234, 2000,’FL’,89.5,’B+’,5)
8. Write a statement that returns the student number and total number of GradePoints for student 234 in ClassID 5 during Fall 2000. (2 points)
SELECT SUM(StudentGrade.GradePoints) as TotalPoints, Student.StudentNumber FROM Student INNER JOIN StudentClass ON Student.StudentID=StudentClass.ClassID WHERE (ClassID=5) AND (Student.StudentID=234) AND (StudentGrade.Semester='FL') AND (StudentGrade.Year=2000)
Select studentid, sum(gradepoints) as TotalPoints from StudentGrade WHERE …
9. In a fictitious ASP page at http://MyDomain.org/Store/OrderDetail.asp, you need to use a server-side include to include the utility page http://MyDomain.org/includes/MyUtil.asp. Write out the line of code you would include in your OrderDetail.asp to do this. (2 points)
<!-- #include file='../includes/MyUtil.asp' -->
<!-- #include virtual='/includes/MyUtil.asp' -->
The transaction log records data modifications—INSERT, UPDATE, and DELETE statements—as they are executed. If the SQL Server failes, the transaction log is used to roll forward (apply the modifications to the data) to all committed transactions that have not been "checkpointed" and roll back (remove) any incomplete transactions.
11. Name two drawbacks to client-side form validation? (2 points)
· Client-side validation dependent on browser scripting abilities
· Reliance only on client-side validation does not protect server-side scripts from malicious inputs.
· Highly complex client-side validation scripts can make for heavy, slow pages.
12. Write out what each of these acronyms stand for: ADO, HTTP, DSN (3 points)
ADO = ActiveX Data Objects (Active Data Objects also acceptable)
HTTP=Hypertext Transfer Protocol
DSN = Data Source Name
13. True or False: Are all of the ASP statements below valid and return the same value? (1 points)
False. rs!sString is not valid in ASP
14. Write a DSN to the SQL Server database called "Publications" on the SQL Server called "DATASERV" using the username "test" and the password "password". (3 points)
15. If you open an ADO recordset with a recordcount property of 90, what would be the value of the recordset's pagecount if its pagesize property is 20? (2 points)
16. If your ASP page is very slow, how long will an IIS webserver (using the default install configuration) attempt to process the page before it gives up? (1 points)
17 . Review the source for the following ASP page and make any necessary corrections and/or additions. (12 points)
1. <%@ language=VBScript %>
2. <% option explicit %>
4. const cDSN "DRIVER=Microsoft Access Driver (*.mdb);DBQ=d:\inetpub\data\megacard.mdb; DefaultDir=d:\inetpub\data\;FIL=MS Access;DriverId=25;UID=admin;PWD=;"
6. iProdID = Response.Form("ID")
8. <title>CSC 123 Midterm 2 – Test Page</title>
12. Set cn = Server.CreateObject("ADODB.Connection")
13. cn.Open cDSN
sSQL = "Select ProdID, iInventory from Inventory where
ProdID=" & iProdID
15. rs.open sSQL, cn
16. If rs.eof then
17. Response.Redirect "error.asp"
19. If rs("iInventory") < 10 then
20. Response.Write "Warning, Low inventory (" & rs("iInventory") & " items)<BR>"
22. Response.Write "Inventory is” & rs("iInventory") & " items.<BR>"
23. End If
24. End if
27. <hr>CSC 123 Midterm 2<br>
28. Contact: <a href="email@example.com">Valtara Webmaster</a>
Line 3+ Missing Response.Buffer = True (needed for line 18)
Line 4 Equal sign (=) missing in DSN
Line 5 IProdID is not dimensioned
Line 6 Should be REQUEST.Form(“ID”)
Line 7+ Missing initial HTML tag, Missing META description element
Line 11 BODY element missing a default BGCOLOR attribute
Line 14+ Recordset object should be instantiated.
Line 20+ rs(“iInventory”) should be written to local variable
Line 26+ Recordset object not closed or set to nothing.
Line 26+ Connection object not set to nothing.
Line 29 Missing mailto in email link
Extra Credit (4 points)
18. Write an ASP code fragment that deletes the record created in question #7 using only an ADO Connection object. Assume there could only be one record that matched the parameters discussed in question #7. (4 points)
const cDSN = “something…”
Set conn = Server.CreateObject("ADODB.Connection")
sSQL = "Delete From StudentGrade WHERE (ClassID=5) AND (Student.StudentID=234) AND (StudentGrade.Semester='FL') AND (StudentGrade.Year=2000) AND (StudentGrade.GradePoints=89.5)"
conn.Execute sSQL, RA
Set conn = Nothing