Fall 2000 Midterm 2 (50 points)
CSC 123 - Server-Side Web Programming with Active Server Pages

Name: _____________ Student ID#: Child Web:

1. True or False: ADO is one of ASP's intrinsic objects.
(1 point)

False

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.

•More scalability.

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

•etc…

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=; "

dim rs

dim sSQL

<html>

<head>

<title>Catalog Table Contents</title>

</head>

<h1>Catalog Table Contents</h1>

sSQL = “Select * from Catalog”

set rs = Server.CreateObject(“ADODB.Recordset”)

rs.open sSQL, cDSN

response.write “<table><tr><td>”

Response.write rs.GetString(,,”</td><td>”,</td></tr><tr><td>”,” ”)

response.write “</td></tr></table>”

rs.close

set rs = Nothing

another approach…

…

for I = 0 to rs.fields.count – 1

response.write “<td> & rs.fields(i).value & “</td>”

…

</body>

</html>

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 output appropriately.
(6 points)

PRODUCTS
Column Name	Type
ProductID (Primary Key)	Integer
Description	Text (50)
QuantityOrdered	Number (Long)
Cost	Currency

const cDSN = "Driver=SQL Server;Server=SQLSERV;Database=MegaCard;UID=sa;PWD=;"

dim rs

dim sSQL

sSQL = "Select * from Products"

set rs = Server.CreateObject("ADODB.Recordset")

rs.open sSQL, cDSN

Response.Write "<table>"

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>"

Wend

Response.Write "</table>”

rs.close

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)

ALSO OK

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)

10. What is the purpose of SQL Server's transaction log? (2 points)

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)

rs!sString

rs.Fields("sString").Value

rs("sString")

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)

"Driver=SQL Server;Server=DATASERV;Database=Publications;UID=test;PWD=password;"

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)

90 seconds

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 %>

3. <%

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=;"

5. dim rs
dim sSQL
dim cn

6. iProdID = Response.Form("ID")

7. <head>

8. <title>CSC 123 Midterm 2 – Test Page</title>

9. </head>

10. <body>

11. <%

12. Set cn = Server.CreateObject("ADODB.Connection")

13. cn.Open cDSN

14. sSQL = "Select ProdID, iInventory from Inventory where ProdID=" & iProdID

15. rs.open sSQL, cn

16. If rs.eof then

17. Response.Redirect "error.asp"

18. Else

19. If rs("iInventory") < 10 then

20. Response.Write "Warning, Low inventory (" & rs("iInventory") & " items)<BR>"

21. Else

22. Response.Write "Inventory is” & rs("iInventory") & " items.<BR>"

23. End If

24. End if

25. cn.Close

26. %>

27. <hr>CSC 123 Midterm 2<br>

28. Contact: <a href="webmaster@valtara.com">Valtara Webmaster</a>

29. </body>

30. </html>

Errors:

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…”

Dim conn

Dim sSQL

Dim RA

Set conn = Server.CreateObject("ADODB.Connection")

Conn.open cDSN

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

conn.close

Set conn = Nothing

Name: _______________________ Student ID#:________________ Child Web: __________

10. What is the purpose of SQL Server's transaction log? (2 points)

Name: _____________ Student ID#: Child Web: