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.
Comments