A sample ASP Application (1)
We are going to bring together all of
the concepts discussed so far into a small ASP application
based around the scott
database schema. This application will show a list of employees from the emp
table and allow the user to perform the usual data-entry
procedures:
Create a new employee
Edit an existing employee
Delete an employee
To implement this application we will
be using four ASP script files, an include file, and the global.asa
file . The include file is an ADO helper file that
we have created ourselves called ADOFunctions_inc.asp
used to create our database connections as needed.
It is often a good idea to
rename your included ASP files from .inc
to .asp
to prevent unauthorized people from simply opening them in a browser. We've
done this with ADOFunctions_inc.asp
as it contains a username and password which we don't want people to have access
to. I've kept the _inc
suffix so that I know it's an include file.
We will be retrieving lists of data using
simple SELECT
statements whereas the add, edit, update and delete functionality will be provided
by four Oracle stored procedures. This will let us examine how we go about calling
Oracle stored procedure using INPUT
and OUTPUT parameters
with the aid of the Microsoft OLE DB Provider for Oracle.
It is notoriously difficult
to retrieve an ADO Recordset
from an Oracle stored procedure. Oracle simply does not allow us to execute
a SELECT
statement from within stored procedure without assigning the returned values
to a PL/SQL variable using the INTO
keyword. There is a way to achieve this functionality with ADO using PL/SQL
tables or by using reference
cursors. In the next section,
we will be covering the retrieval of an ADO Recordset
from Oracle stored procedures using PL/SQL tables and then we'll look at doing
the same thing using reference cursors and a PL/SQL package.
One word of warning though, in order to
concentrate on the Oracle fundamentals, we won't be using any DHTML features,
so the screens do look rather bland!
global.asa
We won't use global.asa
to handle application and session events, but we will
use it to add a reference to the ADO type library to all of our ASP scripts.
This will allow us to use the constants such as adCmdText
for our ADO Command
object. Enter the following line into global.asa:
<!-- METADATA TYPE="TypeLib"
FILE="C:\Program
Files\Common Files\System\ado\msado15.dll" -->
This uses the METADATA
tag to include a TYPELIB
file from the location specified. This is the default
location into which the ADO library is located, but you should update it to
reflect your own
installation if it is different. By adding this line we can make use of all
of the standard ADO constants and enumerators.
Traditionally, ASP developers would include the Microsoft
ADO include file, ADOVBS.inc,
in order to refer to the ADO constants. This would have to be done on every
ASP script and is potentially difficult to support. By using the METADATA
tag you only have to declare it once which is faster for your web server
.
ADOFunctions_inc.asp
This include file is used in all of our
ASP scripts that need to connect to the database.
It is much better to put commonly used code into a single include file and reference
that in each of our pages, as there would be only one place in which we need
to change the username and password if we ever needed to.
So create a new folder called includes
and add a new file called ADOFunctions_inc.asp
containing the following code :
<%
Function
GetDBConnection()
Dim objConnection
Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString = "Provider=MSDAORA; " & _
"Data Source=Oracle8_dev;
" & _
"User ID=scott; Password=tiger;"
.Open
End With
Set GetDBConnection = objConnection
End
Function
%>
The GetDBConnection
function simply returns an ADODB.Connection
object which points to our Oracle database using the scott
account.
Default.asp
Our home page, Default.asp,
displays a list of all employees from the emp
table using a SELECT
statement ordered by name. This page allows the user
to create a new employee record by clicking the create
employee link, delete an employee by pressing the
Delete link,
or edit an employee by clicking the employee's name. Both the edit and add employee
link go to the EditEmp.asp
page.
So let's have a look at the ASP code behind
this page:
<% Option Explicit
Response.Expires
= 0 %>
<!-- #include file="includes/ADOFunctions_inc.asp" -->
<HTML>
<HEAD>
<META HTTP-EQUIV="Pragma"
CONTENT="no-cache">
<TITLE>Select
an Employee</TITLE>
</HEAD>
<BODY>
<CENTER><H2>Select
an Employee</H2></CENTER>
Select
an employee from the list or
<A
HREF="EditEmp.asp">create employee</A>.<P>
As usual we start off with the Option
Explicit
statement so that we must declare all variables and constants used in our code.
We don't want this page to be cached by the browser so that any amended records
are displayed each time the page is shown. We achieve this using Response.Expires
=
0
to tell the browser that this page expires immediately. If your site is going
to be accessed by users in different time zones then it's a good idea to actually
set this to a large negative number.
The line <META>
tag is used to tell any proxy servers that they should
not cache this page for the
same reason.
You'll notice this is the first time that
we include our ADOFunctions.inc
using the
#include directive
.
<%
Dim
objRecordset
Dim
varSQL
Dim
varEmpNo
varSQL
= "SELECT emp.empno, emp.ename, emp.job, " & _
" emp.sal, dept.dname, dept.loc"
& _
" FROM emp, dept" & _
" WHERE emp.deptno = dept.deptno" & _
" ORDER BY UPPER(emp.ename)"
Set objRecordset = GetDBConnection().Execute(varSQL)
Response.Write "<TABLE
BORDER=1><TR>" & _
" <TD>Employee</TD>" & _
"
<TD>Job</TD>" & _
" <TD>Salary</TD>" & _
" <TD>Department</TD>" & _
" <TD>Location</TD>" & _
" <TD> </TD>" & _
"</TR>"
The objRecordset
variable stores the result of our SELECT
statement executed by calling the GetDBConnection
function to return an ADO Connection.
As with SQL Server, Oracle
also supports table nam e aliases that can be used
for long or duplicated tables, such as:
SELECT
EmpHol.Name
FROM
EmployeesOnHoliday
EmpHol
WHERE EmpHol.Department=1
Now we fill out the table with the data:
Do
While Not objRecordset.EOF
varEmpNo = objRecordset.Fields("empno")
Response.Write "<TR>" & _
"
<TD><A HREF=EditEmp.ASP?EmpNo=" & varEmpNo & ">"
& _
objRecordset("ename") & "</A></TD>"
& _
" <TD>" & objRecordset("job")
& "</TD>" & _
" <TD>" & objRecordset("sal")
& "</TD>" & _
" <TD>" & objRecordset("dname")
& "</TD>" & _
" <TD>" & objRecordset("loc")
& "</TD>" & _
" <TD><A HREF=javascript:deleteEmployee("
& _
varEmpNo & ");>Delete</A></TD>" &
_
"</TR>"
objRecordset.MoveNext
Loop
Response.Write
"</TABLE>"
We navigate through the records contained
in the Recordset
object, creating a table row for each employee. We cache the employee number
as it is used as part of the URL for the hyperlink to EditEmp.asp.
Set
objRecordset = Nothing
%>
<SCRIPT>
function
deleteEmployee(EmpNo) {
if (window.confirm("Are you sure you want to delete employee?")
== true)
{
window.location = "DeleteEmp.ASP?EmpNo=" + EmpNo;
}
}
</SCRIPT>
</BODY>
</HTML>
We finish by closing off the ASP script
and defining the local JavaScript function deleteEmployee.
This function uses the window.confirm
function to confirm whether the record should be deleted. If Yes,
then the employee delete script, DeleteEmp.asp
is called.