Tour Reservation from Active Server Pages

This example shows how, in a small Internet application built with Active Server Pages, ADO can be used to query and insert data in a data source governed by a USoft Rules Engine.

An Active Server Pages application consists of a sequence of three pages:

•BROWSE.asp

•SUBMIT.asp

•CONFIRM.asp

In BROWSE.asp, the user is shown a tabular display of all tours scheduled by a Travel Agency on which places are still available. One column is clickable. By clicking on this column, the user can select a tour for which she wants to book a reservation.

In SUBMIT.asp the user is asked to enter personal details such as name and address.

In CONFIRM.asp, both the personal details and the reservation record are written to the database.

Sample code for BROWSE.asp:

<%@ LANGUAGE="VBScript" %>

<% Option Explicit %>

<%Response.Expires=0%>

<HTML>

<HEAD>

<META HTTP-EQUIV="Content-Type" content="text/html;

charset=iso-8859-1">

<TITLE>Query Results</TITLE>

</HEAD>

<BODY>

Dim objConnection

Dim objRecordset

Const adOpenKeyset = 1

Dim strSQL

' Open the Connection

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

objConnection.Open "Travel Agency", "", ""

' Create the SQL statement

strSQL = "SELECT * FROM SCHEDTOUR WHERE MAX_PART > (SELECT SUM(NUM_PART) FROM RESERVATION WHERE RESERVATION.SCHEDTOUR_ID = SCHEDTOUR.SCHEDTOUR_ID) OR NOT EXISTS (SELECT ' ' FROM RESERVATION WHERE RESERVATION.SCHEDTOUR_ID = SCHEDTOUR.SCHEDTOUR_ID)"

' Create recordset

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

objRecordset.ActiveConnection = objConnection

objRecordset.PageSize = 200

Response.Write("Your Query:<P>")

Response.Write(strSQL)

Response.Write("<P>")

objRecordset.Open strSQL, objConnection, adOpenKeyset

' Show the results

<TR>

<TH>

SCHEDTOUR_ID

</TH>

<TH>

DESTINATION

</TH>

<TH>

TOUR_TYPE

</TH>

<TH>

START_DATE

</TH>

<TH>

RETURN_DATE

</TH>

<TH>

PRICE

</TH>

<TH>

MAX_PART

</TH>

<TH>

GUIDE

</TH>

</TR>

Dim i

Do Until objRecordSet.EOF

<TR>

<TD>

Dim strURL

strURL = "DEMO_P_INSERT.asp?SCHEDTOUR_ID=" & _

objRecordset("SCHEDTOUR_ID") & _

"&DESTINATION=" & _

objRecordset("DESTINATION") & _

"&START_DATE=" & _

objRecordset("START_DATE") & _

"&PRICE=" & _

objRecordset("PRICE")

> book!

</a>

</TD>

<TD>

<%=objRecordset("DESTINATION")%>

</TD>

<TD>

<%=objRecordset("TOUR_TYPE")%>

</TD>

<TD>

<%=objRecordset("START_DATE")%>

</TD>

<TD>

<%=objRecordset("RETURN_DATE")%>

</TD>

<TD>

<%=objRecordset("PRICE")%>

</TD>

<TD>

<%=objRecordset("MAX_PART")%>

</TD> <TD>

<%=objRecordset("GUIDE")%>

</TD>

</TR>

objRecordset.MoveNext

Loop

</TABLE>

' Close database

objRecordset.Close

objConnection.Close

</BODY>

</HTML>

Sample code for SUBMIT.asp:

<HTML>

<HEAD>

<META HTTP-EQUIV="Content-Type" content="text/html;

charset=iso-8859-1">

<TITLE>Insert Record</TITLE>

</HEAD>

<BODY>

To book a reservation on the following tour, please fill out this form.

<B>

<P>

schedtour_id = Request.QueryString("SCHEDTOUR_ID")

destination = Request.QueryString("DESTINATION")

Response.Write ("Tour no. " & schedtour_id & " to " & _

destination)

<P>

start_date = Request.QueryString("START_DATE")

Response.Write ("Start Date: " & start_date)

<P>

price = Request.QueryString("PRICE")

Response.Write ("Price per Person: " & price)

</B>

<P>

Your personal details:

<P>

Sub NumericFailMsg(ColumnPrompt)

Msgbox "Please enter a numerical value for " & ColumnPrompt

End Sub

Sub DateFailMsg(ColumnPrompt)

MsgBox "Please enter a proper date for " & ColumnPrompt

End Sub

Sub MandatoryFailMsg(ColumnPrompt)

MsgBox "Please enter a value for " & ColumnPrompt

End Sub

Sub cmdSubmit_OnClick

Dim TheForm

Set TheForm = SCHEDTOUR

TheForm.Submit

End Sub

</SCRIPT>

<table>

<form

action="DEMO_CONFIRM.asp"

method="POST"

name=SCHEDTOUR

<!-- <TR>

<TD>

Schedtour id [hidden]

</TD>

<TD>

</TD>

</TR>

--> <TR>

<TD>

First Name

</TD>

<TD>

</TD>

</TR>

<TR>

<TD>

Family Name

</TD>

<TD>

</TD>

</TR>

<TR>

<TD>

Address

</TD>

<TD>

</TD>

</TR> <TR>

<TD>

City

</TD>

<TD>

</TD>

</TR>

<TR>

<TD>

Area Code

</TD>

<TD>

</TD>

</TR>

<TR>

<TD>

How many places?

</TD>

<TD>

</TD>

</TR>

</table>

<p>

<table>

<tr>

<td>

</td>

<td>

</td>

</tr>

</form>

</table>

</body>

</html>

Sample code for CONFIRM.asp:

<%@ LANGUAGE="VBScript" %>

<% Option Explicit %>

<%Response.Expires=0%>

<HTML>

<HEAD>

<META HTTP-EQUIV="Content-Type" content="text/html;

charset=iso-8859-1">

<TITLE>Insert Record</TITLE>

</HEAD>

<BODY>

Dim objConnection

Dim objCommand

Dim strSQL

' Open the Connection

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

objConnection.Open "Travel Agency", "", ""

'objConnection.BeginTrans

' Create the SQL statement

strSQL = "INSERT INTO PERSON " & _

"(" & _

"FIRST_NAME" & _

",FAMILY_NAME" & _

",ADDRESS" & _

",CITY" & _

",AREA_CODE" & _

")" & _

"VALUES (?,UPPER(?),?,UPPER(?),UPPER(?))"

' Create the Command Object

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

objCommand.ActiveConnection = objConnection

objCommand.CommandText = strSQL

' Add the Parameters to the Command Object

Dim strValue

Dim objParam

strValue = Request.Form("FIRST_NAME")

Set objParam = objCommand.CreateParameter(, 200, ,255, strValue)