(a)chapter 3

Answers to the Exercises

Day 1 Exercises

1. Today, we looked at how to create and execute ASP pages. For this exercise, create an ASP page named HelloWorld.asp, which contains the following code:

1: <%@ Language = VBSCRIPT %>

2: <% Option Explicit %>

3: <%

4: Response.Write "Hello, world!"

5: %>

What will the output be? Save this file to your root physical directory and visit it through a browser. What URL will you use to visit this ASP page?

The output will be: Hello, world! To visit this page, you could use either of the two following URLs:

http://localhost/HelloWorld.asp

http://serverName/HelloWorld.asp

Where serverName is the name of your machine.

Day 2 Exercises

Consider the following ASP Script:

1: <%@ Language=VBScript %>

2: <% Option Explicit %>

3: <HTML>

4: <BODY>

5: Today is:

6: <!--

7: print the current date

8: -->

9: <%= Date() %>,

10: a

11: <%

12: Dim arrDOW

13: arrDOW = Array("Sunday", "Monday", "Tuesday", "Wednesday", _

14: "Thursday", "Friday", "Saturday")

15: Response.Write arrDOW(DatePart("w", Date()) - 1)

16: Response.Write " and the current time is "

17: 'print the time

18: Response.Write Time()

19: %>

20: </BODY>

21: </HTML>

What language is this written in?

VBScript

2. Find an example of a VBScript comment.

Line 17

3. Find an example of an HTML comment.

Lines 6 through 8

4. What lines, besides the first two, contain ASP code?

Line 9 and lines 11 through 19

5. Find all the uses of Response.Write in this script.

Lines 15, 16, and 18

6. Find all the uses of the shortcut alternative to Response.Write in this script.

Line 9

7. Convert the shortcut into a Response.Write.

Response.Write Date()

Day 3 Exercises

1. Write a VBScript statement using either VarType or TypeName that is equivalent to the IsDate function.

VarType(variable_name) = vbDate

2. Write a statement that calculates the length of the hypotenuse of a right triangle. Assume the lengths of the other two sides are stored in sngSide1 and sngSide2.

sngHypotenuse = (sngSide1 ^ 2 + sngSide2 ^ 2) ^ 0.5

3. Write the explicit declaration for variables that will store a user's name, age, email address, and birth date. Choose good names according to the guidelines discussed today.

One good possible answer is

Dim strName, iAge, strEmailAddress, dtBirthdate

Day 4 Exercises

1. Write a function called iSumSeries that computes the sum of the integers from iStart to iStop.

Function iSumSeries (iStart, iStop)

Dim iCounter, iTemp

iTemp = 0

For iCounter = iStart to iStop

iTemp = iTemp + iCounter

iSumSeries = iTemp

End Function

2. Write a function that computes the greatest common divisor of two integers.

There are better ways to do it, but this is one of the most straightforward:

Function iGCD (iNum1, iNum2)

Dim iCounter

iCounter = iNum1

Do Until (iNum1 Mod iCounter = 0) AND (iNum2 Mod iCounter = 0)

iCounter = iCounter - 1

Loop

iGCD = iCounter

End Function

Day 5 Exercises

1. Given a person's birth date, write the code to determine his or her current age.

DateDiff("yyyy", BirthDate, Date)

2. Code a function to determine the base-10 logarithm of a number.

Function Log10(sngNumber)

Log10 = Log(sngNumber)/Log(10)

End Function

3. Write the code to turn a comma-delimited string of names into a semicolon-delimited string.

arrTemp = Split(MyString, ",", -1)

MyString = Join(arrTemp, ";")

4. Given an array of names, write the code to determine whether the name Bill Miller is in the array, without using a loop or Filter.

strTemp = Join(arrNames, ";")

bolInArray = (InStr(1, strTemp, "Bill Miller", 1) <> 0)

If bolInArray Then

'Bill is in the array

Else

'Bill is not in the array

End If

Day 6 Exercises

1. Suppose that you want an object to represent a desk lamp. What properties and methods would you need? Assume that the lamp has three settings: off, dim, and full power. Do not worry about coding the object, just design it.

One good solution would be:

Properties: Model, Brand, Setting, Color, Wattage

Methods: ChangeBulb, ChangeSetting

2. Suppose that you want an object to represent a microwave. What properties, methods, and events would you need? It can have variable power and time settings and should beep when the food is done. Do not worry about coding the object, just design it.

One good solution would be:

Properties: Model, Brand, PowerSetting, Color, TimeSetting

Methods: SetTime, SetPower, Start, Stop

Events: TimerEnd

Day 7 Exercises

1. Write a Response.Write statement to send <A HREF="aboutme.html"> to the browser.

Response.Write("<A HREF=""aboutme.html"">")

2. Write the code that sends the user to another page if it is an odd-numbered day and prints a message if it is an even-numbered day.

<%@ Language=VBScript %>

<% Option Explicit %>
<% Response.Buffer = True %>

<HTML>

<BODY>

If DatePart("d", Date) Mod 2 = 1 Then

Response.Redirect "OddDay.html"

End If

Today is an even-numbered day.
</BODY>

</HTML>

3. Write a statement using Response.ExpiresAbsolute that is equivalent to the following:

Response.Expires = 5

The answer is

Response.ExpiresAbsolute = DateAdd("n", 5, Now)

4. Write the code to have the cached version of the current page expire the first of the next month.

Dim dtFirstOTM

dtFirstOTM = DateAdd("d", 1 - DatePart("d", Date), Date)

Response.ExpiresAbsolute = DateAdd("m", 1, dtFirstOTM)

Day 8 Exercises

1. Create an HTML page that displays a form to collect users' programming experience. It should ask the users to enter their names and select which programming languages they are familiar with. The list of programming languages they can choose from should include VisualBasic, C, HTML, and Fortran. Feel free to add other programming languages to the list. Users should also be asked to enter how many years of experience they have with computer programming. Take the time to think through the form layout and what form fields will be needed.

<HTML>

<BODY>

<B>Welcome! Please enter the following information so that we may

ascertain your computer programming experience!</B><HR>

Name: <INPUT TYPE=TEXT NAME=Name><BR>

What programming languages are you familiar with?<BR>

<INPUT TYPE=CHECKBOX NAME=Languages VALUE=VisualBasic>VisualBasic<BR>

<INPUT TYPE=CHECKBOX NAME=Languages VALUE=Fortran>Fortran<BR>

<BR>

How many years of programming experience do you have?<BR>

<OPTION VALUE="0">None

<OPTION VALUE="1">One Year

<OPTION VALUE="2">Two Years

<OPTION VALUE="3">Three Years

<OPTION VALUE="3+">More than three years

</SELECT>

<P>

</FORM>

</BODY>

</HTML>

2. Write the HTML code needed to produce a form that looks like the form in Figure 8.11.

<HTML>

<BODY>

<B>Please enter your age:</B>

<P>

<B>Please enter your salary: $<INPUT TYPE=TEXT NAME=Salary SIZE=10>

<P>

<B>What level of education have you completed?</B><BR>

Some high school<BR>

Completed high school<BR>

Some college education<BR>

Completed a B.S. degree<BR>

Completed a masters degree<BR>

<P>

</FORM>

</BODY>

</HTML>

Day 9 Exercises

1. Create a calculator-type form. It should contain three list boxes. The first and third list boxes should list the numbers 0 through 9. The middle list box should list the following mathematical operators: +, -, *, and /. The user should be able to select the two numbers and the operation, submit the form, and be shown the answer.

The form creation Web page code:

<%@ Language=VBScript %>

<HTML>

<BODY>

Perform a mathematical computation!<BR>

<OPTION VALUE="0">0

<OPTION VALUE="1">1

<OPTION VALUE="2">2

<OPTION VALUE="3">3

<OPTION VALUE="4">4

<OPTION VALUE="5">5

<OPTION VALUE="6">6

<OPTION VALUE="7">7

<OPTION VALUE="8">8

<OPTION VALUE="9">9

</SELECT>

<OPTION VALUE="+">+

<OPTION VALUE="-">-

<OPTION VALUE="*">*

<OPTION VALUE="/">/

</SELECT>

<OPTION VALUE="0">0

<OPTION VALUE="1">1

<OPTION VALUE="2">2

<OPTION VALUE="3">3

<OPTION VALUE="4">4

<OPTION VALUE="5">5

<OPTION VALUE="6">6

<OPTION VALUE="7">7

<OPTION VALUE="8">8

<OPTION VALUE="9">9

</SELECT>

<P>

</FORM>

</BODY>

</HTML>

The form processing script:

<%@ Language=VBScript %>

<% Option Explicit %>

Dim iTerm1, iTerm2, strOperator

iTerm1 = CInt(Request("Term1"))

iTerm2 = CInt(Request("Term2"))

strOperator = Request("Operator")

Dim sngAnswer

Select Case strOperator

Case "+":

sngAnswer = iTerm1 + iTerm2

Case "-":

sngAnswer = iTerm1 - iTerm2

Case "*":

sngAnswer = iTerm1 * iTerm2

Case "/":

sngAnswer = iTerm1 / iTerm2

End Select

Response.Write "The answer is " & sngAnswer

2. Given the following form, create a form processing script to display all the form field values in a nice-looking report.

Please enter you customer information:<BR>

First Name: <INPUT TYPE=TEXT NAME=FirstName><BR>

Last Name: <INPUT TYPE=TEXT NAME=LastName><BR>

Phone Number: <INPUT TYPE=TEXT NAME=PhoneNumber><BR>

Receive our free newsletter<BR>

When is the best time to contact you?<BR>

<INPUT TYPE=RADIO NAME=Contact VALUE=Night>Night<BR>

<INPUT TYPE=RADIO NAME=Contact VALUE=Never>Never

<P>

</FORM>

Here is the code for Report.asp:

<%@ Language=VBScript %>

<% Option Explicit %>

Dim strFirstName, strLastName, strPhone

Dim bolNewsletter, strContactTime

strFirstName = Request("FirstName")

strLastName = Request("LastName")

strPhone = Request("PhoneNumber")

bolNewsletter = Request("Newsletter")

if bolNewsletter = "on" then

bolNewsletter = True

else

bolNewsletter = False

end if

strContactTime = Request("Contact")

<HTML>

<BODY>

<B>Customer Name:</B> <%=strLastName%>, <%=strFirstName%><BR>

<B>Phone:</B> <%=strPhone%><BR>

<B>Newsletter Subscription:</B>

if bolNewsletter then

Response.Write "Yes"

else

Response.Write "No"

end if

<BR>

<B>Contact Time:</B> <%=strContactTime%>

</BODY>

</HTML>

Day 10 Exercises

1. Create two ASP pages: ValidLink.asp and InvalidLink.asp. Next, create a third ASP page, VisitMe.asp. In both ValidLink.asp and InvalidLink.asp, simply create a hyperlink that, when clicked, takes the user to VisitMe.asp. In VisitMe.asp, determine what page the user came from. If the user came via InvalidLink.asp, display a message telling him that he can only reach VisitMe.asp through ValidLink.asp. If the user came from ValidLink.asp, display a welcome message and the visitor's User-Agent string.

If you have an ASP page that you only want visitors to reach when coming through another page, this technique works well.

This is the code for both ValidLink.asp and InvalidLink.asp:

<%@ Language=VBScript %>

<HTML>

<BODY>

<A HREF="VisitMe.asp">Click to visit <CODE>VisitMe.asp</CODE>!</A>

</BODY>

</HTML>

Here is the code for VisitMe.asp:

<%@ Language=VBScript %>

<% Option Explicit %>

'Save the referring URL

Dim strReferer

strReferer = Request.ServerVariables("HTTP_REFERER")

'Now, we need to grab the filename out of the full URL

Dim strFileName

strFileName = Right(strReferer, Len(strReferer) - _

InStrRev(strReferer, "/"))

If Ucase(strFileName) = "VALIDLINK.asp" then

Response.Write "Welcome! Your User-Agent string is:<BR>"

Response.Write Request.ServerVariables("HTTP_USER-AGENT")

Else

Response.Write "You can only access this page if you come "

Response.Write "through <A HREF=""ValidLink.asp"">ValidLink.asp</A>"

End If

2. Create an ASP page named DeleteAllCookies.asp. Write code that will delete all the cookies your Web site has created on the client's computer. (Hint: Remember that cookies are deleted when they expire!)

Because cookies are deleted when they expire, simply loop through all the cookies with a For Each ... Next loop, setting the expires property to some date in the past for all the cookies. The following is the code for DeleteAllCookies.asp:

<%@ Language=VBScript %>

<% Option Explicit %>

Dim strCookieName, strKeyName

For Each strCookieName in Request.Cookies

Response.Cookies(strCookieName).Expires = Date - 1

Response.Write "Cookies have been deleted..."

Day 11 Exercises

1. Start by creating a blank Global.asa file. Next, add code to the Session object's OnStart event handler that will create a session variable named LogOnTime. LogOnTime should be initialized to the Web server's current date and time. On an ASP page, have a message print out, You arrived at this site at, and then have it display the LogOnTime session variable.

GLOBAL.ASA:

Sub Application_OnStart()

End Sub

Sub Application_OnEnd()

End Sub

Sub Session_OnStart()

'Get the web server's current time

Session("LogOnTime") = Now()

End Sub

Sub Session_OneND()

End Sub

</SCRIPT>

SOME_ASP_PAGE.ASP:

<%@ Language=VBScript %>

<% Option Explicit %>

<HTML>

<BODY>

You arrived at this site at <%=Session("LogOnTime")%>

</BODY>

</HTML>

2. Write a program that will use session variables to determine whether a user has cookies enabled. Remember that for a user's session variables to be saved, he must have cookies enabled. (Hint: Because a user needs to accept cookies to have his session variables persisted, if you write a session variable on one ASP page, transfer to another ASP page, and that session variable does not exist, what does that tell you about the user's cookie acceptance?)

You need to write two ASP pages. The first ASP page creates a session variable named CookieTest and sets it equal to the string "Test". You then automatically redirect the user to the second page via the Request.Redirect method. On the second page, if the user has cookies enabled, the value of CookieTest will be "Test", obviously. If, however, the user doesn't support cookies, then the user cannot be mapped to an existing Session, and the session variable CookieTest will return an empty string.

FirstASPPage.asp:

<%@ Language=VBScript %>

<% Option Explicit %>

'We must set Response.Buffer to true, so that no HTTP

'headers will have been written before the Redirect

Response.Buffer = True

'Create a session variable named CookieTest

Session("CookieTest") = "Test"

'Now, redirect the user to SecondASPPage.asp

Response.Redirect "SecondASPPage.asp"

SecondASPPage.asp:

<%@ Language=VBScript %>

<% Option Explicit %>

'What does Session("CookieTest") equal? If it

'equals "Test" then the user supports cookies,

'else the user doesn't support cookies

If Session("CookieTest") = "Test" then

Response.Write "You accepted my cookie!"

Else

Response.Write "You have cookies disabled..."

End If

Day 12 Exercises

1. Assuming that the schedule file has already been written and is called AdList.txt, write the code to print three ads with a border thickness of 2.

<%@ Language=VBScript %>

<% Option Explicit %>
<HTML>

<BODY>

Dim objAd

Set objAd = Server.CreateObject("MSWC.AdRotator")

objAd.Border = 2

Response.Write(objAd.GetAdvertisement("AdList.txt"))

Set objAd = Nothing

</BODY>

</HTML>

2. Write the code to print out links to the previous three pages using the Content Linker. Be sure that can handle cases where there are fewer than three previous pages.

<%@ Language=VBScript %>

<% Option Explicit %>

<HTML>

<BODY>

Dim objNL, iCnt, iIndex

Set objNL = Server.CreateObject("MSWC.NextLink")

iIndex = objNL.GetListIndex("LinkList.txt")

For iCnt = iIndex-3 to iIndex-1

If iCnt > 0 Then

<A HREF="

<%= objNL.GetNthURL("LinkList.txt", iCnt) %>">

<%= objNL.GetNthDescription("LinkList.txt", iCnt) %>

</A><BR>

End if

Set objNL = Nothing

</BODY>

</HTML>

3. Write code that will redirect Netscape users to a separate page.

<%@ Language=VBScript %>

<% Option Explicit %>

Dim objBC

Set objBC = Server.CreateObject("MSWC.BrowserType")

If objBC.Frames = "Netscape" Then

Response.Redirect "NetscapePage.asp"

End if

Set objBC = Nothing

Day 13 Exercises

1. Write the code to convert a file called input.txt to all uppercase letters.

<%@ LANGUAGE=VBSCRIPT %>

<% Option Explicit %>

<HTML>

<BODY>

Dim objOpenFile, objFSO, strPath, strFile

strPath = Server.MapPath("input.txt")

Set objFSO = _

Server.CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(strPath) Then

Set objOpenFile = _

objFSO.OpenTextFile(strPath, 1)

strFile = objOpenFile.ReadAll

strFile = Ucase(strFile)

Set objOpenFile = _

objFSO.OpenTextFile(strPath, 2)

objOpenFile.Write(strFile)

objOpenFile.Close

Set objOpenFile = Nothing

End If

Set objFSO = Nothing

</BODY>

</HTML>

2. Write the code to print to the screen the first five lines from each of input1.txt, input2.txt, input3.txt, and input4.txt.

<%@ LANGUAGE=VBSCRIPT %>

<% Option Explicit %>

<HTML>

<BODY>

Dim objOpenFile, objFSO, strPath, strFile

Dim iCount, iCount2

Set objFSO = _

Server.CreateObject("Scripting.FileSystemObject")

For iCount = 1 to 4

strPath = "input" & iCount & ".txt"

strPath = Server.MapPath(strPath)

If objFSO.FileExists(strPath) Then

Set objOpenFile = _

objFSO.OpenTextFile(strPath, 1)

For iCount2 = 1 to 5

Response.Write objOpenFile.ReadLine

Response.Write "<BR>"

objOpenFile.Close

End If

Set objOpenFile = Nothing

Set objFSO = Nothing

</BODY>

</HTML>

Day 14 Exercises

1. Locate the fatal bug in the following ASP script:

1: <%@ Language = VBSCRIPT %>

2: <% Option Explicit %>

3: <%

4: Dim strNow

5: strNow = Now()

6: Response.Write "What time is it?<BR>"

8: Resopnse.Write "It is " & strNow & " right now.<BR>"

9: Response.Write "It was " & DateAdd("h",-2,strNow) & _

10: " two hours ago."

11: %>

The fatal bug occurs on line 8 with a misspelling of the word Response. Note on line 8 that Response is spelled Resopnse. This will generate a fatal error.

2. Locate the following nonfatal bug in the following ASP script:

1: <%@ Language = VBSCRIPT %>

2: <% Option Explicit %>

3: <%

4: Dim strNow

5: strNow = Now()

6: Response.Write "What time is it?<BR>"

8: Resopnse.Write "It is " & strNow & " right now.<BR>"

9: Response.Write "It was " & DateAdd("h",-3,strNow) & _

10: " two hours ago."

11: %>

The nonfatal bug occurs on line 9. The script informs the user that it is displaying the time as of two hours ago. However, it is showing the time that was actually three hours ago. Although this script will not cause any program termination, it will cause incorrect output, hence classifying it as a nonfatal bug. It is unclear whether the -3 in the DateAdd function should be a -2, or whether, instead of displaying two hours ago, three hours ago should be displayed. Regardless, this will remain classified as a nonfatal bug until this discrepancy is resolved.

Day 15 Exercises

1. Imagine that you were asked to create a database that would store employee information. What columns would you envision needing in your Employee database table? What data types would these columns need to be?

There are no right or wrong answers to this exercise. Here are some columns that might be needed in the Employee table, along with their associated data types:

FirstName[md]Text

LastName[md]Text

SocialSecurityNumber[md]Text

DateOfBirth[md]Date/Time

Title[md]Text

Salary[md]Currency

What columns did you think were important? Did you choose appropriate data types for those columns?

Day 16 Exercise

1. Suppose that the Recordset object objRS has the following data in it:

ProductName ModelNumber Price

Standard Widget A-195 $14.99

Widget Plus T-1105 $21.95

Micro Widget A-102 $9.99

Widget Deluxe A-119C $29.95

What will be the output of the following code?

objRS.MoveNext

Response.Write objRS("ModelNumber")

Response.Write "<BR>"

Response.Write objRS("Price")

Response.Write "<BR>"

objRS.MoveNext

Response.Write objRS.EOF

Response.Write "<BR>"

Response.Write objRS("ProductName")

Response.Write "<BR>"

objRS.MoveNext

Response.Write objRS.EOF

The output will be:

T-1105

21.95

False

Micro Widget

True

2. Create a new access database called JoesWidgets.mdb. Create a file called JoeConnect.asp that will open a connection to the new database. You may use a System DSN connection or a DSN-less connection, whichever you prefer.

Dim objConn

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

objConn.ConnectionString = "DSN=Joe.dsn"

objConn.Open

Dim objConn

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

objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & _

"DBQ=C:\My Documents\JoesWidgets.mdb"

objConn.Open

3. Add a new table to the database called tblEmployees. It should have the following fields.

Write an ASP page to open the table and display at most five records. You should include the file you created in Exercise 2. Test it first with the table empty and then add at least six entries and test it again.

<%@ Language=VBScript %>

<% Option Explicit %>

<HTML>

<BODY>

<TABLE>

Dim objRS, iCount

iCount = 0

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "tblEmployees", objConn, , , adCmdTable

'check for end of record set AND number of records

'displayed less than five

Do While (Not objRS.EOF AND iCount < 5)

Response.Write "<TR><TD>"

Response.Write objRS("Name")

Response.Write "</TD><TD>"

Response.Write objRS("IDNumber")

Response.Write "</TD><TD>"

Response.Write objRS("Salary")

Response.Write "</TD><TD>"

Response.Write objRS("BeganWorking")

Response.Write "</TD></TR>"

iCount = iCount + 1

objRS.MoveNext

Loop

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

</TABLE>

</BODY>

</HTML>

Day 17 Exercise

Create a table in your database called tblLinks. It should have the following fields: LinkID, URL, Title, Description. All are text fields except LinkID, which is an AutoNumber. Write an HTML form that a user can use to enter a Title, URL, and Description for a new link. Title and URL are required. Then write the ASP page that actually adds it to the table. HINT: Because LinkID is of type AutoNumber, you do not need to set it yourself.

<HTML>

<HEAD><TITLE>Submit your link</TITLE></HEAD>

<BODY>

<TABLE>

<TR>

<TD align=right>Title:</TD>

</TR>

<TR>

</TR>

<TR>

<TD align=right>Description:</TD>

</TR>

</TABLE>

</FORM>

</BODY>

</HTML>

<%@ Language=VBScript %>

<% Option Explicit %>

<HTML>

<BODY>

Dim objRS

If ((Request.Form("title") = "") OR (Request.Form("url") = "")) Then

Response.Write "<A HREF='newlink.html'>"

Response.Write "You must enter values for Title and URL."

Response.Write "</A>"

Else

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "tblLinks", objConn, , adLockOptimistic, adCmdTable

objRS.AddNew

objRS("Title") = Request.Form("title")

objRS("URL") = Request.Form("url")

objRS("Description") = Request.Form("description")

objRS.Update

Response.Write "Your link has been added. Thank you!"

objRS.Close

Set objRS = Nothing

End If

objConn.Close

Set objConn = Nothing

</BODY>

</HTML>

Write a page that takes the titles of the links in the table and lists them in a drop-down box. The user may then click one of the titles and then a submit button, and be taken to a page where he may edit the title, URL, or description of the specified record. Create a third page to actually save the changes to the database. HINT: Pass the LinkID between the pages to identify the correct record to edit.

<%@ Language=VBScript %>

<% Option Explicit %>

<HTML>

<BODY>

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "tblLinks", objConn, , , adCmdTable

Do While Not objRS.EOF

Loop

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

</SELECT>

</FORM>

</BODY>

</HTML>

<%@ Language=VBScript %>

<% Option Explicit %>

Dim objRS, bolFound, iLinkID

iLinkID = Request.Form("linkID")

'make sure a link was chosen

If iLinkID = "" Then

objConn.Close

Set objConn = Nothing

Response.Redirect "listlinks.asp"

End If

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "tblLinks", objConn, , , adCmdTable

bolFound = False

'look for username in table

Do While Not (objRS.EOF OR bolFound)

If (StrComp(objRS("LinkID"), iLinkID, vbTextCompare) = 0) Then

BolFound = True

Else

objRS.MoveNext

End If

Loop

'if LinkID is not found, display message

If Not bolFound Then

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

Response.Write "<A HREF='listlinks.asp'>"

Response.Write "Error, that link cannot be found."

Response.Write "</A>"

Response.End

End If

<HTML>

<BODY>

<TABLE>

<TR>

<TD align=right>Title:</TD>

<TD align=left><INPUT TYPE=TEXT NAME="title"

VALUE="<%=objRS("Title") %>"></TD>

</TR>

<TR>

<TD align=left><INPUT TYPE=TEXT NAME="url "

VALUE="<%=objRS("URL") %>"></TD>

</TR>

<TR>

<TD align=right>Description:</TD>

<TD align=left><INPUT TYPE=TEXT NAME="description" SIZE=30

VALUE="<%=objRS("Description") %>"></TD>

</TR>

</TABLE>

</FORM>

</BODY>

</HTML>

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

<%@ Language=VBScript %>

<% Option Explicit %>

<HTML>

<BODY>

Dim objRS, bolFound, iLinkID

iLinkID = Request.Form("username")

'make sure all the data was entered

If ((Request.Form("title") = "") OR (Request.Form("url") = "")) Then

objConn.Close

Set objConn = Nothing

You must enter values for the Title and URL. Either hit the "back"

button or click <A HREF="listlinks.asp">here to log in</A>

Else

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "tblLinks", objConn, , adLockOptimistic, adCmdTable

bolFound = False

'try to find entry in table

Do Until objRS.EOF OR bolFound

If (StrComp(objRS("LinkID"), iLinkID, vbTextCompare) = 0) Then

bolFound = True

Else

objRS.MoveNext

End If

Loop

'if linkID not found, display message

If Not bolFound Then

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

Response.Write "<A HREF='listlinks.asp'>"

Response.Write "Invalid link.<P>"

Response.Write "</A>"

Response.End

End If

objRS("Title") = Request.Form("title")

objRS("URL") = Request.Form("url")

objRS("Description") = Request.Form("description")

objRS.Update

objRS.Close

Set objRS = Nothing

End If

objConn.Close

Set objConn = Nothing

Changes made successfully.

</BODY>

</HTML>

Write a page that takes the titles of the links in the table and lists them in a drop-down box. The user may then click one of the titles and then a Delete Link button. Clicking the button opens a page that makes the change to the database and then reloads the previous page.

<%@ Language=VBScript %>

<% Option Explicit %>

<HTML>

<BODY>

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "tblLinks", objConn, , , adCmdTable

Do While Not objRS.EOF

Loop

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

</SELECT>

</FORM>

</BODY>

</HTML>

<%@ Language=VBScript %>

<% Option Explicit %>

Dim objRS, bolFound, iLinkID

iLinkID = Request.Form("linkid")

'make sure a link was chosen

If iLinkID = "" Then

objConn.Close

Set objConn = Nothing

Response.Redirect "deletelink.asp"

End If

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "tblLinks", objConn, , adLockOptimistic, adCmdTable

'try to find entry in table

bolFound = False

Do Until objRS.EOF OR bolFound

If (StrComp(objRS("LinkID"), iLinkID, vbTextCompare) = 0) Then

bolFound = True

Else

objRS.MoveNext

End If

Loop

If bolFound Then

objRS.Delete

End If

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

Response.Redirect "deletelink.asp"

Day 18 Exercises

1. The script presented in Listing 18.3 sorts the output of the Portfolio table using the Sort property of the Recordset object. Modify the output so that at the top of the page there are three hyperlinks. The first hyperlink should say, "Sort by Symbol," the second, "Sort by Shares," and the third, "Sort by Price." When the user clicks on a hyperlink, the page is reloaded, except that the new sorting is applied.

Hint: You will need to use the query string to determine what column you want to sort by.

<%@ Language = VBSCRIPT %>

<% Option Explicit %>

'Create a connection to our database that contains the

'Portfolio table. Use a DSN connection

Dim objConn

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

objConn.ConnectionString = "DSN=Portfolio"

objConn.Open

'Create a recordset object instance, objRS

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

'The cursor location must be set to adUseClient for the

'Sort property to work

objRS.CursorLocation = adUseClient

'Open the recordset using the adOpenStatic cursor

objRS.Open "Portfolio", objConn, , , adCmdTable

'Determine what we need to sort by. The querystring value "SORT"

'will determine what we need to sort by. If SORT is non-existent,

'we will sort by the Symbol

Select Case Request("SORT")

Case "Symbol":

objRS.Sort = "Symbol"

Case "Shares"

objRS.Sort = "Shares"

Case "Price"

objRS.Sort = "Price"

Case Else:

objRS.Sort = "Symbol"

End Select

'Display the hyperlinks to let the user resort the results

Response.Write "<A HREF=""18Example1.asp?SORT=Symbol"">Sort by Symbol</A> |"

Response.Write "<A HREF=""18Example1.asp?SORT=Shares"">Sort by Shares</A> |"

Response.Write "<A HREF=""18Example1.asp?SORT=Price"">Sort by Price</A> |"

Response.Write "<P>"

'Display the recordset in sorted order

Do While Not objRS.EOF

Response.Write "<B>" & objRS("Symbol") & "</B><BR>"

Response.Write "Price: " & FormatCurrency(objRS("Price"))

Response.Write "<BR>Shares: " & FormatNumber(objRS("Shares"),0)

Response.Write "<BR>Date: " & FormatDateTime(objRS("Date"))

Response.Write "<P><HR><P>"

'Move to the next record in the recordset

objRS.MoveNext

Loop

'Clean up!!

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

Create two ASP pages, a form creation Web page (SelectPrice.asp) and a form processing script (ListStocksByPrice.asp). In SelectPrice.asp, the user should be shown a form into which she can enter a desired maximum price. When the form is submitted, ListStocksByPrice.asp will list all the stocks in the Portfolio table that cost strictly less than the price entered by the user.

SelectPrice.asp:

<%@ Language = VBSCRIPT %>

<% Option Explicit %>

<HTML>

<BODY>

<B>My portfolio contains information about all of my stock

purchases. You can choose to list stocks that cost less

than a certain price.</B><BR>

Maximum Cost:

<P>

</FORM>

</BODY>

</HTML>

ListStockByPrice.asp:

<%@ Language = VBSCRIPT %>

<% Option Explicit %>

'Create a connection to our database that contains the

'Portfolio table. Use a DSN connection

Dim objConn

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

objConn.ConnectionString = "DSN=Portfolio"

objConn.Open

'Read in the user's input

Dim iMaxPrice

iMaxPrice = Request("MaxPrice")

'Make sure the user entered a valid value for the MaxPrice

If Len(iMaxPrice) = 0 or Not isNumeric(iMaxPrice) then

iMaxPrice = 100

End If

'Create a recordset object instance, objRS

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

'Open the recordset using the adOpenForwardOnly cursor

objRS.Open "Portfolio", objConn, adOpenForwardOnly, , adCmdTable

'Return only those stocks that are strictly less than the user's input

objRS.Filter = "Price < " & iMaxPrice

'Display the recordset

Do While Not objRS.EOF

Response.Write "<B>" & objRS("Symbol") & "</B><BR>"

Response.Write "Price: " & FormatCurrency(objRS("Price"))

Response.Write "<BR>Shares: " & FormatNumber(objRS("Shares"),0)

Response.Write "<BR>Date: " & FormatDateTime(objRS("Date"))

Response.Write "<P><HR><P>"

'Move to the next record in the recordset

objRS.MoveNext

Loop

'Clean up!!

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

Day 19 Exercises

1. Imagine that you have a table for employee information. This table, EmployeeInfo consists of five columns: FirstName, LastName, SSN, Age, and Salary. Write a SELECT statement that will retrieve the FirstName, LastName, and Salary columns from the EmployeeInfo table where the employee is more than 50 years old, or the employee's Salary is less than or equal to $25,000. Order the results by the Salary, in ascending order.

SELECT FirstName, LastName, Salary

FROM EmployeeInfo

WHERE Age > 50 OR Salary <= 25000

ORDER BY Salary

2. Create an ASP page that will display the results of the SQL statement from Exercise 1. Assume that a DSN named Employee exists, which you can use to connect to the Employee database, which contains the table EmployeeInfo. Be sure to use FormatCurrency when displaying the employee's salary.

<%@ Language=VBScript %>

<% Option Explicit %>

'Open up a connection to our Access database that stores the customer

'information. We will use a DSN-less connection here.

Dim objConn

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

objConn.ConnectionString = "DSN=Employee"

objConn.Open

'Create our SQL statement variable

Dim strSQL

strSQL = "SELECT FirstName, LastName, Salary FROM EmployeeInfo " & _

"WHERE Age > 50 OR Salary <= 25000 " & _

"ORDER BY Salary"

'Create a recordset object instance, and execute the SQL statement

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open strSQL, objConn

'We are displaying products whose price is less than iMaxPrice

Response.Write "<B>Employees over 50 or who make $25,000 or less:</B><BR>"

Do While Not objRS.EOF

Response.Write objRS("LastName") & "," & objRS("FirstName") & " - " & _

FormatCurrency(objRS("Salary")) & "<BR>"

'Move on to the next customer

objRS.MoveNext

Loop

'Clean up our ADO objects

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

Create two ASP pages, a form creation Web page (SelectEmployeeByAge.asp) and a form processing script (ListEmployeeByAge.asp). In SelectEmployeeByAge.asp, the user should be shown a form into which she can enter a desired age. When the form is submitted, ListEmployeeByAge.asp lists all the employees whose Age is identical to the value entered by the user. Just the employee's name and age should be shown.

SelectEmployeeByAge.asp:

<HTML>

<BODY>

<B>What age employee do you want to look for?</B><BR>

<P>

</FORM>

</BODY>

</HTML>

ListEmployeeByAge.asp:

<%@ Language=VBScript %>

<% Option Explicit %>

'Open up a connection to our Access database that stores the customer

'information. We will use a DSN-less connection here.

Dim objConn

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

objConn.ConnectionString = "DSN=University"

objConn.Open

'Read in the value max price value entered by the user

Dim iAge

iAge = Request("Age")

'Make sure that iAge is a numeric value. If it is not, set

'iAge to 30

If Len(iAge) = 0 or Not IsNumeric(iAge) then

iAge = 30

End If

'Create our SQL statement variable

Dim strSQL

strSQL = "SELECT FirstName, LastName, Age FROM EmployeeInfo " & _

"WHERE Age = " & iAge & _

" ORDER BY LastName, FirstName"

'Create a recordset object instance, and execute the SQL statement

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open strSQL, objConn

If objRS.EOF then

Response.Write "There are no employees aged " & iAge

Else

'We are displaying products whose price is less than iMaxPrice

Response.Write "<B>A Listing of Employees who are " & _

iAge & " Years Old:</B><BR>"

Do While Not objRS.EOF

Response.Write objRS("LastName") & ", " & objRS("FirstName") & _

" - " & objRS("Age") & "<BR>"

'Move on to the next customer

objRS.MoveNext

Loop

End If

'Clean up our ADO objects

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

Day 20 Exercises

1. Create a Recordset with a scrollable cursor of your choice. The Recordset should then move to the last record and continue to move to the previous record until the Recordset object's BOF property is True. In this example, rather than moving from the start of the Recordset to the end of the Recordset, one record at a time, you are moving from the end to the beginning one record at a time.

<%@ Language = VBSCRIPT %>

<% Option Explicit %>

'Create a connection to our Products database

Dim objConn

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

objConn.ConnectionString = "DSN=ProductsDB"

objConn.Open

'Create a recordset object instance, objRS

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

'Open the Recordset with a Keyset cursor

objRS.Open "Products", objConn, adOpenKeyset, , adCmdTable

'Print out the contents of the Products table from the last

'record to the first... So, to start off, we need to move

'to the last record

objRS.MoveLast

'Now we need to move from the last to the first

Do While Not objRS.BOF

Response.Write "<BR>" & objRS("Name") & " - " & _

FormatCurrency(objRS("Price"))

'Move to the previous record

objRS.MovePrevious

Loop

'Clean up!!

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

2. Create a Recordset object implicitly using the Execute method of the Connection object. The Execute method should be used to perform a SQL query.

<%@ Language = VBSCRIPT %>

<% Option Explicit %>

'Create a connection to our Products database

Dim objConn

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

objConn.ConnectionString = "DSN=ProductsDB"

objConn.Open

'Create a SQL string

Dim strSQL

strSQL = "SELECT Name,Price FROM Products ORDER BY Price"

'Create a recordset object instance, objRS

Dim objRS

Set objRS = objConn.Execute(strSQL)

'Display the contents of the Recordset objRS

Do While Not objRS.EOF

Response.Write "<BR>" & objRS("Name") & " - " & _

FormatCurrency(objRS("Price"))

'Move to the next record

objRS.MoveNext

Loop

'Clean up!!

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

3. Create a Recordset object implicitly using the Execute method of the Command object. The Execute method should be used to retrieve the contents of a database table.

<%@ Language = VBSCRIPT %>

<% Option Explicit %>

'Create a connection to our Products database

Dim objConn

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

objConn.ConnectionString = "DSN=ProductsDB"

objConn.Open

'Create the command object

Dim objCommand

Set objCommand = Server.CreateObject("ADODB.Command")

'Set the command object properties

objCommand.CommandText = "Products"

objCommand.ActiveConnection = objConn

objCommand.CommandType = adCmdTable

'Create an implicit recordset object instance, objRS

Dim objRS

Set objRS = objCommand.Execute

'Display the contents of the Recordset objRS

Do While Not objRS.EOF

Response.Write "<BR>" & objRS("Name") & " - " & _

FormatCurrency(objRS("Price"))

'Move to the next record

objRS.MoveNext

Loop

'Clean up!!

objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

Day 21 Exercise

1. Using the programming techniques discussed in the section "Practiced ASP Programming Techniques," rewrite the following code:

1: <%@ Language=VBScript %>

2: <% Option Explicit %>

3: <%

4: Dim a, b

5: b = 1

6: For a = 1 to 10

7: Response.Write b & " squared equals " & b * b & "<BR>"

8: b = b + 1

9: Next

10: %>

<%@ Language=VBScript %>

<% Option Explicit %>

'Display the numbers 1 through 10, and their squares

Dim iLoop, iNumber

iNumber = 1

'Loop through 1 to 10

For iLoop = 1 to 10

Response.Write iNumber & " squared equals " & iNumber * iNumber & "<BR>"

iNumber = iNumber + 1