Oracle8 and Oracle8i

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"> &nbsp;
               <INPUT TYPE="RESET" VALUE="Reset">&nbsp;
               <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.

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Owning a computer without programming is like having a kitchen and using only the microwave oven” - Charles Petzold