Tutorial:
Database - ASP - Display Video Transacts With FoxPro Database
Dr.
Thomas E. Hicks
Computer Science
Department
Trinity University
IIS 5 Install on Windows XP Pro
IIS 5 Installation on Windows 2000 Pro/Server
It might even help to read a tutorial that describes the need for Database Web Applications.
Database Need For Web Applications
You may use the basic ideas of this tutorial for either the ODBC connection or the MapPath connection..
Database - ASP - ODBC Connections
Database - ASP - MapPath Connections
The Customer table stores the video store Customer entity. The Movie table stores the video store movie entity. The Video Database is a relational database; the Transact table stores the relationship of movies checked out by the Movies.
The Customer .dbf table contains 24 records. Each record of the Customer table contains fields Name, No, and Phone. The table designer view shows the datatypes associated with the Customer fields.
The Movie.dbf table contains 38 records. Each record of the customer table contains fields Name, No, and Category. The table designer view shows the datatypes associated with the movie fields.
The Transact.dbf table contains 101 records. Each record of the transact table contains fields Name, No, and Phone. The table designer view shows the datatypes associated with the customer fields.
This database may be downloaded.
We are going to place all of the ASP files in folder C:\Inetpub\wwwroot\ASP. We could place the database files anywhere on the drive. For security reasons, it is not a good idea to place them in the wwwroot directory.
For purposes of this tutorial, we shall assume that the Video database files are located in C:\Video. For purposes of this tutorial, we shall assume that the Security database is located in C:\.
<%@ LANGUAGE = VBScript %> |
The server will have one default scripting language. This may or may not be VBScript. This line makes sure that the scripting language is VBScript. Blocks of ASP code begin with <% and end with %>.
<% Option Explicit %> |
This line of ASP code forces the programmer to explicitly declare each and every variable.
<% Response.Expires = 0 %> |
This line of ASP reloads the most recent database data each time the page is refreshed.
<!-- #include virtual = "../../Common/adovbs.inc" --> |
Variables, such as AdOpenDynamic and AdLockOptimistic are defined in the adovbs.inc file. I keep a copy of this file in folder C:\Inetpub\WWWRoot\Common.
<% '======================================================================== '======================================================================== '==== DisplayVideoTransact.asp ==== '======================================================================== '==== ==== '==== Purpose : Demonstrate the MapPath connection to the ==== '==== Video database located in folder Video that is ==== '==== three levels up at C:\Video ==== '==== ==== '==== Written By : Dr. Thomas E. Hicks Date: 6/1/2003 ==== '======================================================================== '======================================================================== '------------------------------------------------------------------------ ' Declarations '------------------------------------------------------------------------ Dim Conn, TransactSQL, TransactRecordSet, ConnString, Counter |
The documentation block provides a brief statement of purpose. Immediately following the documentation block are declarations for all of the variables used on this page.
'------------------------------------------------------------------------ ' Initializations '------------------------------------------------------------------------ Counter = 1 |
Counter is initialized to 1. It shall be used to number the rows/records in the table.
'------------------------------------------------------------------------
' Create The Console Object & MapPath Connection To The Server
'------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")
ConnString = "SourceType=DBC; SourceDB=" &_
Server.MapPath("../../../Video/Video.dbc")
Conn.Open "Driver={Microsoft Visual FoxPro Driver}; " & ConnString
|
The block of code above connects this page to the Video database. A connection object is created. Variable ConnString contains part of the connection arguments. The Conn.Open opens the Video database and associates it with the connection object.
'------------------------------------------------------------------------
' Read All Of The Transact Information
'------------------------------------------------------------------------
TransactSQL = " Select * " &_
" From Transact"
Set TransactRecordSet = Server.CreateObject ("ADODB.RecordSet")
TransactRecordSet.Open TransactSQL, Conn, AdOpenDynamic, AdLockOptimistic
%>
|
The SQL query is to select all of the information from the Transact table. A TransactRecordSet object is created.
When the record set object is passed the SQL query and the connection object, all of the information about all of the Transact information is transferred from the server to your record set.
<html><head> <title>Dr. Thomas E. Hicks - DisplayVideoTransact.asp </title> </head> |
The HTML places Dr. Thomas E. Hicks - DisplayVideoTransact.asp in the browser title bar.
<BODY TEXT = "#000000" BGCOLOR = "#000000" VLINK ="#000000" ALINK ="#000000" BACKGROUND ="Paper.jpg"> |
This HTML code above defines the page background, the default text color, the default background color, and the default link colors.
<CENTER> <font face="Arial" size="4">DisplayVideoTransact.asp<br> Written By<br> Dr. Thomas E. Hicks</font></p> </CENTER> <hr> |
The HTML code above creates the following commercial at the top of the page.
DisplayVideoTransact.asp
Written By
Dr. Thomas E. Hicks
<!-- ================================================================ --> <!-- Display Transacts In The Table --> <!-- ================================================================ --> <font face="System" size="3" color="#FFFFFF"> <TABLE BORDER = "5" CELLPADDING = "4" CELLSPACING = "4" STYLE = "border-collapse: collapse" BORDERCOLOR = "#800000" BGCOLOR = "#FFFFFF" WIDTH = "100%" ID = "TransactTable"> |
Although it is not essential, we are going to display the information from the database in a traditional HTML table. The code above establishes the format for this table.
<!-- ================================================================ --> <!-- Title Bar Row --> <!-- ================================================================ --> <TR><TD ALIGN = "Right"> <CENTER> <font color="#000080">#</font></CENTER> </TD> <TD ALIGN = "Left"> <font color="#000080">Date</font> </TD> <TD ALIGN = "Right"> <CENTER> <font color="#000080">No</font></CENTER> </TD> <TD ALIGN = "Left"> <font color="#000080">Customer #</font> </TD> <TD ALIGN = "Left"> <font color="#000080">Movie #</font> </TD> </TR> |
The HTML code above creates the following Table Title Bar in the first row of the table.
# |
Date | No |
Customer # | Movie # |
<% Do While NOT TransactRecordSet.EOF %> |
If the query is successful, the record set will contain information returned from the server. The record set pointer initially points to the first record in the record set. This record set point can be moved through the record set.
We would like to display the information from the current record in a row of our table and then move this record set pointer to the next record. This process shall continue in a Do While loop until the end of file is reached.
The basic form for the ASP Do While
Loop is :
Do While [Condition]
...........
Loop
<!-- ================================================================ -->
<!-- One Row Per Record -->
<!-- ================================================================ -->
<TR>
<TD ALIGN = "Center"> <% = Counter %> </TD>
<TD ALIGN = "Left"> <% = TransactRecordSet.Fields("Date") %> </TD>
<TD ALIGN = "Center"> <% = TransactRecordSet.Fields("No") %> </TD>
<TD ALIGN = "Left"> <% = TransactRecordSet.Fields("CustomerNo") %> </TD>
<TD ALIGN = "Left"> <% = TransactRecordSet.Fields("MovieNo") %> </TD>
</TR>
|
All of the code from the block above dumps output into a single row of the HTML table. A Counter is displayed in the first column. The current record's Date is placed in the second column. The current record's No is placed in the third column. The current record's CustomerNo is placed in the fourth column. The current record's MovieNo is placed in the fifth column.
<%
Counter = Counter + 1
TransactRecordSet.MoveNext
Loop
%>
|
Counter is simply a counter to
number the records; it is incremented each pass through the loop.
The record set pointer is moved to the next record.
Loop transfers control back to the Do While statement and repeats the block of
code within the loop untill the condition is no longer true. This loop
terminates when the record set pointer reaches the end of file.
</TABLE> </BODY></HTML> |
The HTML code above simply ends the table, the body, and the document.
Although it is not the purpose of this tutorial to teach ASP or HTML, a example is necessary to verify that the connection works.
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<% Response.Expires = 0 %>
<!-- #include virtual = "../../Common/adovbs.inc" -->
<%
'========================================================================
'========================================================================
'==== DisplayVideoTransact.asp ====
'========================================================================
'==== ====
'==== Purpose : Demonstrate the MapPath connection to the ====
'==== Video database located in folder Video that is ====
'==== three levels up at C:\Video ====
'==== ====
'==== Written By : Dr. Thomas E. Hicks Date: 6/1/2003 ====
'========================================================================
'========================================================================
'------------------------------------------------------------------------
' Declarations
'------------------------------------------------------------------------
Dim Conn, TransactSQL, TransactRecordSet, ConnString, Counter
'------------------------------------------------------------------------
' Initializations
'------------------------------------------------------------------------
Counter = 1
'------------------------------------------------------------------------
' Create The Console Object & MapPath Connection To The Server
'------------------------------------------------------------------------
'Create a Connection Object
Set Conn = Server.CreateObject("ADODB.Connection")
ConnString = "SourceType=DBC; SourceDB=" &_
Server.MapPath("../../../Video/Video.dbc")
Conn.Open "Driver={Microsoft Visual FoxPro Driver}; " & ConnString
'------------------------------------------------------------------------
' Read All Of The Transact Information
'------------------------------------------------------------------------
TransactSQL = " Select * " &_
" From Transact"
Set TransactRecordSet = Server.CreateObject ("ADODB.RecordSet")
TransactRecordSet.Open TransactSQL, Conn, AdOpenDynamic, AdLockOptimistic
%>
<html><head>
<title>Dr. Thomas E. Hicks - DisplayVideoTransact.asp </title>
</head>
<BODY TEXT = "#000000"
BGCOLOR = "#000000"
VLINK ="#000000"
ALINK ="#000000"
BACKGROUND ="Paper.jpg">
<CENTER>
<font face="Arial" size="4">DisplayVideoTransact.asp<br>
Written By<br>
Dr. Thomas E. Hicks</font></p>
</CENTER>
<hr>
<!-- ================================================================ -->
<!-- Display Transacts In The Table -->
<!-- ================================================================ -->
<font face="System" size="3" color="#FFFFFF">
<TABLE BORDER = "5"
CELLPADDING = "4"
CELLSPACING = "4"
STYLE = "border-collapse: collapse"
BORDERCOLOR = "#800000"
BGCOLOR = "#FFFFFF"
WIDTH = "100%"
ID = "TransactTable">
<!-- ================================================================ -->
<!-- Title Bar Row -->
<!-- ================================================================ -->
<TR><TD ALIGN = "Right">
<CENTER> <font color="#000080">#</font></CENTER> </TD>
<TD ALIGN = "Left"> <font color="#000080">Date</font> </TD>
<TD ALIGN = "Right">
<CENTER> <font color="#000080">No</font></CENTER> </TD>
<TD ALIGN = "Left"> <font color="#000080">Customer #</font> </TD>
<TD ALIGN = "Left"> <font color="#000080">Movie #</font> </TD>
</TR>
<%
Do While NOT TransactRecordSet.EOF
%>
<!-- ================================================================ -->
<!-- One Row Per Record -->
<!-- ================================================================ -->
<TR>
<TD ALIGN = "Center"> <% = Counter %> </TD>
<TD ALIGN = "Left"> <% = TransactRecordSet.Fields("Date") %> </TD>
<TD ALIGN = "Center"> <% = TransactRecordSet.Fields("No") %> </TD>
<TD ALIGN = "Left"> <% = TransactRecordSet.Fields("CustomerNo") %> </TD>
<TD ALIGN = "Left"> <% = TransactRecordSet.Fields("MovieNo") %> </TD>
</TR>
<% Counter = Counter + 1 TransactRecordSet.MoveNext Loop %> </TABLE> </BODY></HTML> |
You can see the results below:
