A sample ASP Application (2)
DeleteEmp.asp
This page simply calls an Oracle stored
procedure, emp_Delete,
passing in the employee number so that it can be deleted
from the emp
table.
We've covered stored procedures earlier
in this book, so we'll just explain the important parts of this new procedure.
This stored procedure doesn't come as part of the default database, so we are
going to create it ourselves. Using SQL*Plus, or your preferred Oracle editor,
you will need to connect to the scott
account and execute the following SQL to create the new procedure:
CREATE
OR REPLACE PROCEDURE emp_Delete
(i_empno IN NUMBER)
AS
BEGIN
DELETE
FROM emp
WHERE empno = i_empno;
END;
As you can see, it is a very simple procedure
that takes one input parameter, i_empno,
and deletes the record with the corresponding employee number from the emp table. We
use the IN
statement to tell Oracle that this parameter is for input only. You must tell
Oracle if you want the value of parameters to be updated as the procedure exits,
using the OUT
statement, in exactly the same way that you should use the ByVal
and ByRef
statements in your own ASP procedures. I tend to prefix the name of each parameter
with an i_
or o_
to denote the direction. You can also specify a parameter as being both IN
and OUT
but that's not a recommended practice .
So jumping back to DeleteEmp.asp,
we have the following code:
<% Option Explicit
Response.Buffer
= True
%>
<!-- #include file="includes/ADOFunctions_inc.asp "
-->
<HTML>
<%
Dim objCommand
Dim varEmpNo
varEmpNo = Request.QueryString("EmpNo")
Set objCommand = Server.CreateObject("ADODB.Command")
Set objCommand.ActiveConnection
= GetDBConnection()
This time we are using the ADO Command
object to execute our stored procedure because we need to get at the parameters
that make up this stored procedure. This is more important when you want to
retrieve the value of output parameters, as they are only accessible from the
Command
object's Parameters
collection rather than a Recordset.
With objCommand
.CommandText
= "{call emp_Delete(?)}"
.CommandType
= adCmdText
.Parameters(0).Value
= varEmpNo
.Execute()
End With
Set objCommand = Nothing
Response.Redirect "default.asp"
%>
</HTML>
We use the CommandText
property to tell the Command
object the SQL statement to execute using the {call
procname}
syntax. Each ?
refers to a parameter to this stored procedure and can be referenced in the
Command
object's Parameters
collection – starting from 0. We simply set the first and only parameter
to that of the employee number passed in through the URL and then run the procedure
using the Execute
function.
Finally, we redirect the user back to
our home page, default.asp.
Another approach could have been to open a new pop-up window
to confirm the delete, which would have then refreshed default.asp
using the JavaScript window.opener
property, if the delete was successful. If the delete operation failed, for
any reason, the pop-up window could have stayed open displaying the error message
that was returned.
EditEmp.asp
This page allows existing employee's records
to be updated or new ones to be added. If this is
an existing employee record, we will be passed the employee number as part of
the URL. If there is no employee number, then the page assumes that the user
wants to add a new employee record.
This page uses a stored procedure, emp_GetData,
to return the fields for this employee through a number of output parameters,
so you'll need to create the following stored procedure:
Remember that with Oracle,
we cannot simply execute a SELECT
statement inside a stored procedure to return some records as we can with SQL
Server!
CREATE
OR REPLACE PROCEDURE emp_GetData
(i_empno IN
NUMBER,
o_ename OUT
VARCHAR2,
o_job
OUT VARCHAR2,
o_mgr
OUT NUMBER,
o_sal
OUT NUMBER,
o_deptno OUT NUMBER)
AS
BEGIN
SELECT ename, job, mgr,
sal, deptno
INTO o_ename,
o_job,
o_mgr,
o_sal,
o_deptno
FROM emp
WHERE empno = i_empno;
END;
This time we have only one input parameter
and five output parameters that are used to store the employee details using
the SELECT...INTO
statement to transfer the values.
The ASP script has to do quite bit of
work to display this page. It populates the list of departments and managers
using a custom VBScript procedure that writes out a list of OPTION
statements based on a Recordset
of data, as we'll see shortly.
<% Option Explicit
%>
<!-- #include file="includes/ADOFunctions_inc.asp "
-->
<HTML>
<HEAD>
<TITLE>Employee
Details</TITLE>
</HEAD>
<BODY>
<CENTER><H2>Employee
Details</H2></CENTER>
<%
Dim objConnection
Dim objCommand
Dim objRSDepartments
Dim objRSManagers
Dim varEmpNo
Dim varEName
Dim varJob
Dim varMgr
Dim varSalary
Dim varDeptNo
We use a separate Recordset
object to store the list of departments and managers so that we can populate
the SELECT
list in the correct place. I always find it easier to transfer the record values
to local variables in one place.
On
Error Goto Next
Set
objConnection = GetDBConnection()
If
Request.QueryString("EmpNo") = "" Then
varEmpNo = 0
We create a database connection using
GetDBConnection,
and if there is no employee number passed in the URL, we set the employee number
to zero. If the user clicked on an employee's name, we would have been passed
the correct employee number.
For new employees, we use
an Oracle Sequence to generate the new employee number, which we'll cover shortly.
Else
varEmpNo = Request.QueryString("EmpNo")
Set objCommand = Server.CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
With objCommand
.CommandText = "{call emp_GetData(?,
?, ?, ?, ?, ?)}"
.CommandType = adCmdText
.Parameters(0).Value = varEmpNo
.Execute()
If we have an employee number then we
need to create a Command
object and specify the emp_GetData
stored procedure. This time we have six parameters with the first one being
the
input parameter, the employee number, and the remaining five output parameters
storing the employee's details.
varEName = .Parameters(1)
varJob = .Parameters(2)
varMgr = .Parameters(3)
varSalary = .Parameters(4)
varDeptNo = .Parameters(5)
End With
End
If
Once we've called the Execute
function, each of the Parameters
items will contain our employee's fields so it's just a case of transferring
them to our local variables.
Set
objRSDepartments = objConnection.Execute( _
"SELECT deptno, dname FROM dept ORDER BY dname")
Set
objRSManagers = objConnection.Execute( _
"SELECT empno, ename FROM emp ORDER BY ename")
Set
objCommand = Nothing
Set objConnection = Nothing
We use our connection object, objConnection,
to retrieve a list of departments and managers for
our SELECT lists
and then shut down the Command
and Connection
objects as soon as we've finished with them.
Sub
PopulateSelectOptions(ByVal objRecordset, ByVal varCurrentID)
Dim varHTML
Dim varSelected
objRecordSet.MoveFirst
Do While Not objRecordset.EOF
If CLng(varCurrentID) = Clng(objRecordset.Fields(0)) Then
varSelected = " SELECTED"
Else
varSelected = ""
End If
varHTML = varHTML & "<OPTION VALUE=" & objRecordset.Fields(0)
& _
varSelected & ">" & objRecordset.Fields(1) &
"</OPTION>"
objRecordset.MoveNext
Loop
Response.Write varHTML
End
Sub
%>
PopulateSelectOptions
is a general-purpose procedure that is passed a Recordset
of data and the ID of the default item to select. Its purpose is to navigate
through each record and create a collection of HTML OPTION
tags using the field at position 0 as the ID and field
1 as the text to display. If this was a full-blown application we'd probably
put this function in an include file so that other pages could use its functionality,
but as this is an example, we'll leave it in the ASP.
<FORM ACTION="EditEmp_HND.ASP?EmpNo=<%=varEmpNo%>"
METHOD="POST">
<TABLE>
<TR>
<TD>Name:</TD>
<TD><INPUT NAME="varEName" VALUE="<%=varEName%>"></TD>
</TR>
<TR>
<TD>Job:</TD>
<TD><INPUT NAME="varJob" VALUE="<%=varJob%>"></TD>
</TR>
Now we can define the FORM
that allows the user to enter the employee details. Notice that we append the
employee number, which can be zero for new employee records, to the query string
for the form action handler, EditEmp_HND.asp.
<TR>
<TD>Manager:</TD>
<TD><SELECT NAME="varMgr" SIZE="1">
<%
Call PopulateSelectOptions(objRSManagers, varMgr)
Set
objRSManagers = Nothing
%>
</SELECT></TD>
</TR>
This is the first time that we call PopulateSelectOptions
to create our list of OPTION
tags. We already have the <SELECT>
tag so PopulateSelectOptions
will generate the corresponding list of <OPTION>
tags for each record in objRSManagers.
<TR>
<TD>Salary:</TD>
<TD><INPUT NAME="varSalary"
VALUE="<%= varSalary %>"></TD>
</TR>
<TR>
<TD>Department:</TD>
<TD><SELECT NAME="varDeptNo" SIZE="1">
<%
Call PopulateSelectOptions(objRSDepartments, varDeptNo)
Set objRSDepartments = Nothing
%>
</SELECT></TD>
</TR>
<TR>
<TD></TD>
<TD>
<INPUT TYPE="SUBMIT" VALUE="Save">
<INPUT TYPE="RESET" VALUE="Reset">
<INPUT TYPE="BUTTON" VALUE="Cancel"
onclick="document.location.href='/';">
</TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
We finish off by completing the input
form, again using PopulateSelectOptions
to show a list of departments, and adding a Submit
to submit the form, a Reset
button to clear any edits and a Cancel
button to take the user back to the home page.
EditEmp_HND.asp
This page is the form handler that is
called when the user submits the data-entry form .
It calls
the parameterized stored procedure emp_Update
to update an existing record or add a new one
using emp_Add.
Again, we are going to create these new
stored procedures, so jump back to your SQL editor and execute the following
lines:
CREATE
OR REPLACE PROCEDURE emp_Update
(i_empno
IN NUMBER,
i_ename
IN VARCHAR2,
i_job
IN VARCHAR2,
i_mgr
IN NUMBER,
i_sal
IN NUMBER,
i_deptno
IN NUMBER)
AS
BEGIN
UPDATE emp SET
ename = i_ename,
job = i_job,
mgr = i_mgr,
sal = i_sal,
deptno = i_deptno
WHERE empno = i_empno;
END;
Now that we've created the stored procedure
for updates, we need to create an Oracle Sequence
object before we create the emp_Add
procedure. A sequence is an object that generates sequential numbers that we
can use as primary keys for our employee number column. Oracle does not support
the IDENTITY
column that you would use in SQL Server so we must create a Sequence
object to generate the numbers for us . Sequences
are created separately from the table that they are created for, so if a table
happens to be deleted (that is dropped) the sequence object will still
exist. Each time you request the next number in the sequence using the NEXTVAL
property, the sequence will automatically update itself irrespective of the
table to column that it was originally created for.
So from your SQL editor execute the
following statement to create the sequence:
CREATE SEQUENCE empno_seq START WITH
9000;
The sequence is called empno_seq
and starts at 9000. The reason why I've decided to start at 9000 is because
the emp
table already contains some records and, in my case, the largest employee number
was 7934, so I want to start at a number greater than 7934. A sequence has a
number of properties that you can call, but NEXTVAL
is the one we need to get the next number in the sequence.
Now that's done we can create the add
stored procedure by running the following SQL:
CREATE
OR REPLACE PROCEDURE emp_Add
(i_ename IN VARCHAR2,
i_job
IN VARCHAR2,
i_mgr
IN NUMBER,
i_sal
IN NUMBER,
i_deptno IN NUMBER)
AS
BEGIN
INSERT INTO emp(empno,
ename, job, mgr,
sal, deptno)
VALUES(empno_seq.NEXTVAL,
i_ename,
i_job,
i_mgr,
i_sal,
i_deptno);
END;
The ASP script is relatively simple:
<%
Option Explicit %>
<!--
#include file="includes/ADOFunctions_inc.asp
" -->
<HTML>
<HEAD>
<TITLE>Update Employee Details</TITLE>
</HEAD>
<BODY>
<CENTER><H2>Update Employee Details</H2></CENTER>
<%
Dim
objCommand
Dim
varEmpNo
Dim
varEName
Dim
varJob
Dim
varMgr
Dim
varSalary
Dim
varDeptNo
We will be using a Command
object in order to set the stored procedure's parameters and local variables
to store the value from the submitted form.
With
Request
varEmpNo = .QueryString("EmpNo")
varEName = .Form("varEName")
varJob = .Form("varJob")
varMgr = .Form("varMgr")
varSalary = .Form("varSalary")
varDeptNo = .Form("varDeptNo")
End
With
We transfer the form fields into local
variables.
Set objCommand = Server.CreateObject("ADODB.Command")
Set objCommand.ActiveConnection
= GetDBConnection()
If varEmpNo <>
0 Then
With objCommand
.CommandText
= "{call emp_Update(?,
?, ?, ?, ?, ?)}"
.CommandType
= adCmdText
.Parameters(0).Value
= varEmpNo
.Parameters(1).Value
= varEName
.Parameters(2).Value
= varJob
.Parameters(3).Value
= varMgr
.Parameters(4).Value
= CInt(varSalary)
.Parameters(5).Value
= varDeptNo
.Execute()
Response.Write
"Record for employee " & varEName & _
" has been updated."
End With
If we have employee number then it's just
a case of calling the emp_Update
stored procedure and pass in each of the values.
Else
With objCommand
.CommandText
= "{call emp_Add(?,
?, ?, ?, ?)}"
.CommandType
= adCmdText
.Parameters(0).Value
= varEName
.Parameters(1).Value
= varJob
.Parameters(2).Value
= varMgr
.Parameters(3).Value
= CInt(varSalary)
.Parameters(4).Value
= varDeptNo
.Execute()
Response.Write
"Record for employee " & varEName & " has been added."
End With
End If
Set objCommand = Nothing
%>
<P>
<A HREF="default.asp">Home</A>
</BODY>
</HTML>
In the case of a new record, we call the
emp_add
stored procedure and pass in the new
employee's details.
That concludes our brief ASP sample application
based on the scott
employee data. We've seen how it is possible to call stored procedures using
the {call procname?}
syntax to retrieve data for a single record and to manipulate records using
the Command.Parameters
collection. We made use of a standard include file to create our database connection
and a useful function to output a list of OPTION
tags based on a Recordset
of data.