Rick Curtis - Tutorial 2

Adding & Displaying new Records Using Stored Procedures

For many applications, you want users to be able to add new records. The basic Server Extensions that ship with UltraDev alow you to issue SQL INSERT statements, but only to one table. In many cases, you may need to insert data to multiple tables at the same time which linked by a common ID field. One example of this is the Orders and OrderDetails insert which involves a parent/child relationship between the two tables.

This tutorial is a continuation of the application built in Tutorial 1. My example comes from a Jobs database. Users can enter new job postings after logging on (covered in other tutorials by Hiran, Sune and Joee). Here is the basic database design

Company Login Page

You need to create a simple form page with two exit boxes named CompanyID and Password. Set the Form Method to POST and the Form Action to AddJob.asp. This page will pass the edit box login values for CompanyID and Password as Request values onto the JobAdd.asp page which sends these as parameters to a stored procedure (spCompanyLogin). See Figure 1.

Add a Job Page

After logging in users are taken to the Job Add Page, a form-based page that passes form values as parameters onto a Job Confirmation Page which displays a summary of the Job Postings in a repeat region. There are several recordsets used on this page.

• rsCompanyLogin - is a recordset based on a stored procedure (spCompanyLogin) that accepts the CompanyID and Password as parameters for the stored procedure and returns the appropriate records.
• rsCategory - is a recordset that runs a stored procedure (spCategory) to return all of the available job categories.

Figure 1

First create the rsCompanyLogin recordset on the page using the stored procedure (spCompanyLogin). You will need to use the advanced dialog box to link to the stored procedure. For the rsCompanyLogin set the following values for the recordset. These correspond to the names of the form fields being passed from the Login page and the names are the same as the parameter names in the stored procedure (except for the @ which UltraDev disallows). Now you will have to add two Request variables to the page that correspond to the Run-time values in the table below. The Request variables should be named CompanyID and Password. These are passed as parameters to the stored procedure and return the appropriate company recordset.

Now create the rsCategory recordset using the stored procedure (spCategory). You will need to use the advanced dialog box to link to the stored procedure. The rsCategory recordset is used to populate a table with categories that the user can select for her job entry.

Now create a table to hold the various form fields for adding new jobs. Each edit box, list box, etc. should have the same name as the parameters in your stored procedure minus the @ sign (see the stored procedure below). To help your user, add the rsCompanyName field to the page. This gives her feedback that you know who she is. Also add a Hidden Field within your form. The name of this hidden field should be set to CompanyID and the value of this field should be set to

<%=(rsCompanyLogin.Fields.Item("CompanyID").Value)%>

This hidden field is essential because of the database design. Since all of the company detail information is stored in a spearate table from the Jobs table, one of the required fields in the Jobs table is CompanyID. Your Hidden Field will pass this CompanyID value on to the AddConfirmation.asp page as a Request value.

Set the Form Method to POST and the Form Action to AddConfirm.asp. Here is the basic ASP code on the page.

<%
Dim rsCompanyLogin__CompanyID
rsCompanyLogin__CompanyID = "1"
if(Request("CompanyID") <> "") then rsCompanyLogin__CompanyID = Request("CompanyID")
%>

<%
Dim rsCompanyLogin__Password
rsCompanyLogin__Password = "1"
if(Request("Password") <> "") then rsCompanyLogin__Password = Request("Password")
%>

<%
set rsCompanyLogin = Server.CreateObject("ADODB.Recordset")
rsCompanyLogin.ActiveConnection = "dsn=Jobs;"
rsCompanyLogin.Source = "{call dbo.sp_CompanyLogin(" + Replace(rsCompanyLogin__CompanyID, "'", "''") + ",'" + Replace(rsCompanyLogin__Password, "'", "''") + "')}"
rsCompanyLogin.CursorType = 0
rsCompanyLogin.CursorLocation = 2
rsCompanyLogin.LockType = 3
rsCompanyLogin.Open rsCompanyLogin_numRows = 0
%>

<%
set rsCategory = Server.CreateObject("ADODB.Recordset")
rsCategory.ActiveConnection = "dsn=Jobs;"
rsCategory.Source = "{call dbo.sp_Category}"
rsCategory.CursorType = 0
rsCategory.CursorLocation = 2
rsCategory.LockType = 3
rsCategory.Open rsCategory_numRows = 0
%>

Add Confirmation Page - AddConfirm.asp

This is the page that does all the real work in this application. Calling this page inserts new records into two (or more) related tables and passes the new JobID onto two additional recordsets to be able to display the newly added data to the user. There are several recordsets used on this page as well as Request variables to transfer the information from the AddJob.asp form to the stored procedure. The Data Bindings screen shot in Figure 2 shows all the Recordsets and the Request variables. [Ignore the rs JobPopulations recordset - it responds just like the rsJobCategories recordset but I removed it for simplicity.]

Figure 2

• commJobAdd - is a command based on a stored procedure (commJobAdd) that inserts a new record into the Jobs table, extracts the new JobID from the insert and uses that to also insert into the child table JobCategory. The command also returns the value of JobID to the ASP page.
• rsJobSearch - is a recordset that accepts the JobID from the commJobAdd as a parameter and passes it to a stored procedure (spJobSearch) to return the data for the newly inserted record with that JobID.
• rsJobCategories - is a recordset that accepts a JobID from the commJobAdd as a parameter and passes it to a stored procedure (spJobCategories) to return the categoy for that JobID.

Command Stored Procedure

The commAddJob stored procedure requires that you configure the command properly (see Figure 3 below). When you set up the initial connection to the stored procedure in SQL 7 the Variables list box will automatically be populated with the declared @ variables from the stored procedure and the correct datatype. As you can see, the Direction for most variables is set for in which means that the run-time values are being passed in to the stored procedure. In order for the command to function properly, you will need to set the size for each datatype based on how you have configured the fields in your database table. Then you will need to add the appropriate Request("fieldname") run-time value. Remember that we labeled all of our textboxes and list boxes from the AddJob.asp form with the same name as the stored procedure variable (minus the @ sign). You can see the full list of Request variables in Figure 2 above.

Figure 3

rsJobSearch Recordset

Here you can see the rsJobSearch recordset (Figure 4). The rsJobCategories recordset looks exactly the same.Set the following parameters for both recordsets. The RUn-time value is critical since it pulls the newly created JobID from the spJobAdd stored procedure.

Then drag the recordset fields that you want to diaplay onto your field. The Data Bindings will look like Figure 4..

Figure 4

Tweak the Code

There are a few basic code tweaks that you will have to add on the JobConfirm.asp page to make everything work. We use the same strategy for this as we did in Tutorial 1 to filter one recordset on the page by another (for a parent/child relationship). You may need to make a few alterations in the code as follows:

1. The code for the commJobAdd command along with the defined variables for it must come at the very top of the page since it must execute first. If it isn't there, cut and paste it up at the top right after <%@LANGUAGE="VBSCRIPT"%>
   

Page Code

With these recordsets in place UltraDev will generate the following page code. The critical code that connects the output of teh insert stored procedure to the others that display the newly inserted record is shown in red. The first thing that UltraDev does is to declare all of the Request variables to pass to the spAddJob stored procedure. Then you will see the code for creating the command which inserts the various values from the Request variables into the appropriate tables. After that comes the code that we just tweaked above for creating the JobID variables and then the code for the rsJobSearch recordset and the rsJobCategories recordset.

<%@LANGUAGE="VBSCRIPT"%>

<%
Dim commAddJob__CompanyID
commAdd Job__CompanyID = ""
if(Request("CompanyID") <> "") then commAddJob__CompanyID = Request("CompanyID")

Dim commAddJob__JobTypeID
commAddJob__JobTypeID = ""
if(Request("JobTypeID") <> "") then commAddJob__JobTypeID = Request("JobTypeID")

Dim commAddJob__RegionID commAddJob__RegionID = "" if(Request("RegionID") <> "") then commAddJob__RegionID = Request("RegionID")

Dim commAddJob__JobTitle commAddJob__JobTitle = "" if(Request("JobTitle") <> "") then commAddJob__JobTitle = Request("JobTitle")

Dim commAddJob__StartDate commAddJob__StartDate = "" if(Request("StartDate") <> "") then commAddJob__StartDate = Request("StartDate")

Dim commAddJob__CloseDate commAddJob__CloseDate = "" if(Request("CloseDate") <> "") then commAddJob__CloseDate = Request("CloseDate")

Dim commAddJob__JobDescription commAddJob__JobDescription = "" if(Request("JobDescription") <> "") then commAddJob__JobDescription = Request("JobDescription")

Dim commAddJob__Qualifications commAddJob__Qualifications = "" if(Request("Qualifications") <> "") then commAddJob__Qualifications = Request("Qualifications")

Dim commAddJob__SalaryBenefits commAddJob__SalaryBenefits = "" if(Request("SalaryBenefits") <> "") then commAddJob__SalaryBenefits = Request("SalaryBenefits")

Dim commAddJob__Resp commAddJob__Resp = "" if(Request("Resp") <> "") then commAddJob__Resp = Request("Resp")

Dim commAddJob__Availability commAddJob__Availability = "" if(Request("Availability") <> "") then commAddJob__Availability = Request("Availability")

Dim commAddJob__ToApply commAddJob__ToApply = "" if(Request("ToApply") <> "") then commAddJob__ToApply = Request("ToApply")

Dim commAddJob__EmailJobs commAddJob__EmailJobs = "" if(Request("EmailJobs") <> "") then commAddJob__EmailJobs = Request("EmailJobs")

Dim commAddJob__CategoryID commAddJob__CategoryID = "" if(Request("CategoryID") <> "") then commAddJob__CategoryID = Request("CategoryID")

Dim commAddJob__PopulationID commAddJob__PopulationID = "" if(Request("PopulationID") <> "") then commAddJob__PopulationID = Request("PopulationID")
%>

<%
set commAddJob = Server.CreateObject("ADODB.Command") commAddJob.ActiveConnection = "dsn=Jobs;" commAddJob.CommandText = "dbo.spAddJob" commAddJob.Parameters.Append commAddJob.CreateParameter("RETURN_VALUE", 3, 4) commAddJob.Parameters.Append commAddJob.CreateParameter("@CompanyID", 3, 1, 4, commAddJob__CompanyID) commAddJob.Parameters.Append commAddJob.CreateParameter("@JobTypeID", 3, 1, 4, commAddJob__JobTypeID) commAddJob.Parameters.Append commAddJob.CreateParameter("@RegionID", 3, 1, 4, commAddJob__RegionID) commAddJob.Parameters.Append commAddJob.CreateParameter("@JobTitle", 200, 1, 100, commAddJob__JobTitle) commAddJob.Parameters.Append commAddJob.CreateParameter("@StartDate", 135, 1, 8, commAddJob__StartDate) commAddJob.Parameters.Append commAddJob.CreateParameter("@CloseDate", 135, 1, 8, commAddJob__CloseDate) commAddJob.Parameters.Append commAddJob.CreateParameter("@JobDescription", 200, 1, 4000, commAddJob__JobDescription) commAddJob.Parameters.Append commAddJob.CreateParameter("@Qualifications", 200, 1, 4000, commAddJob__Qualifications) commAddJob.Parameters.Append commAddJob.CreateParameter("@SalaryBenefits", 200, 1, 4000, commAddJob__SalaryBenefits) commAddJob.Parameters.Append commAddJob.CreateParameter("@Resp", 200, 1, 4000, commAddJob__Resp) commAddJob.Parameters.Append commAddJob.CreateParameter("@Availability", 200, 1, 200, commAddJob__Availability) commAddJob.Parameters.Append commAddJob.CreateParameter("@ToApply", 200, 1, 2000, commAddJob__ToApply) commAddJob.Parameters.Append commAddJob.CreateParameter("@EmailJobs", 200, 1, 100, commAddJob__EmailJobs) commAddJob.Parameters.Append commAddJob.CreateParameter("@CategoryID", 3, 1, 4, commAddJob__CategoryID) commAddJob.Parameters.Append commAddJob.CreateParameter("@PopulationID", 3, 1, 4, commAddJob__PopulationID) commAddJob.Parameters.Append commAddJob.CreateParameter("@JobID", 3, 2) commAddJob.CommandType = 4 commAddJob.CommandTimeout = 0 commAddJob.Prepared = true commAddJob.Execute()
%>

<%
Dim rsJobSearch__JobID rsJobSearch__JobID = "1" if(commAddJob.Parameters.Item("@JobID").Value <> "") then rsJobSearch__JobID = commAddJob.Parameters.Item("@JobID").Value
%>

<%
Dim rsJobCategories__JobID rsJobCategories__JobID = "1" if(commAddJob.Parameters.Item("@JobID").Value <> "") then rsJobCategories__JobID = commAddJob.Parameters.Item("@JobID").Value
%>

<%
set rsJobCategories = Server.CreateObject("ADODB.Recordset") rsJobCategories.ActiveConnection = "dsn=Jobs;" rsJobCategories.Source = "{call dbo.spJobCategories(" + Replace(rsJobCategories__JobID, "'", "''") + ")}" rsJobCategories.CursorType = 3
rsJobCategories.CursorLocation = 3
rsJobCategories.LockType = 1
rsJobCategories.Open rsJobCategories_numRows = 0
%>

<%
set rsJobSearch = Server.CreateObject("ADODB.Recordset") rsJobSearch.ActiveConnection = "dsn=Jobs;"
rsJobSearch.Source = "{call dbo.spJobID(" + Replace(rsJobSearch__JobID, "'", "''") + ")}"
rsJobSearch.CursorType = 0
rsJobSearch.CursorLocation = 3
rsJobSearch.LockType = 3
rsJobSearch.Open rsJobSearch_numRows = 0
%>

<html><head>

What Does it All Mean?

We are using a stored procedure to insert the data since we are inserting records to two tables (Jobs and JobCategory). When the user clicks the Add Job button on the AddJobs.asp form it sends all the form values as Request variables to the AddConfirm.asp page. The commJobAdd stored procedure runs as soon as the page loads. It takes the Request variables and uses that information to insert data first into the Jobs table.

The Jobs table uses JobID as the primary key field. SQL Server 7 has a special database value known as @@identity which equals the value of the primary key field for the newly inserted record. The stored procedure then uses the @@identity value as the JobID for the foriegn key value in the JobCategory table insert.

You will notice that we have also set up the stored procedure to pass an OUTPUT value back - @JobID which is the JobID value of the newly inserted job record. This JobID value is then passed first to the spJobSearch stored procedure which populates the rsJobSearch recordset and also to the spJobCategories stored procedure which populates the rsJobCategories recordset and displays all the newly added job information to the user.

I hope this technique is helpful in your application development work. Good luck.

Stored Procedures

spCompanyLogin

Alter Procedure sp_CompanyLogin

(@CompanyID int, @Password varchar)

AS

SELECT CategoryType.Category1, CompanyType.CompanyType, Population.PopulationType, Region.Region, Company.*

FROM CategoryType INNER JOIN
Company ON CategoryType.Category1ID = Company.CategoryTypeID INNER JOIN
CompanyType ON Company.CompanyTypeID = CompanyType.CompanyTypeID INNER JOIN
Population ON Company.PopulationID = Population.PopulationID INNER JOIN
Region ON Company.RegionID = Region.RegionID

WHERE CompanyID = @CompanyID AND Password = @Password AND Verify = 1

spCategory

Alter Procedure spCategory

As

SELECT * from CategoryType

ORDER BY Category1

return

commJobAdd

Alter PROCEDURE spAddJob

-- Declare variables for inserts to 2 tables

(@CompanyID [int], @JobTypeID [int], @RegionID [int], @JobTitle [varchar](100), @StartDate [datetime], @CloseDate [datetime], @JobDescription [varchar](4000), @Qualifications [varchar](4000), @SalaryBenefits [varchar](4000), @Resp [varchar](4000), @Availability [varchar](200), @ToApply [varchar](2000), @EmailJobs [varchar](100), @CategoryID [int], @JobID [int] OUTPUT)

AS

-- Insert into Jobs table

INSERT INTO Jobs ([CompanyID], [JobTypeID], [RegionID], [JobTitle], [StartDate], [CloseDate], [JobDescription], [Qualifications], [SalaryBenefits], [Resp], [Availability], [ToApply], [EmailJobs])

VALUES (@CompanyID, @JobTypeID, @RegionID, @JobTitle, @StartDate, @CloseDate, @JobDescription, @Qualifications, @SalaryBenefits, @Resp, @Availability, @ToApply, @EmailJobs)

-- Retrieve the automatically generated JobID VALUE from the Jobs table

SET @JobId = @@IDENTITY

-- Insert new values into JobCategory table

INSERT INTO JobCategory (JobID, CategoryID)

VALUES (@JobID, @CategoryID)

Return

spJobSearch

Alter Procedure spJobID

@JobID int

As

SELECT Jobs.*, Company.*, Region.Region, JobType.JobType

FROM Jobs INNER JOIN
Region ON Jobs.RegionID = Region.RegionID INNER JOIN
JobType ON Jobs.JobTypeID = JobType.JobTypeID INNER JOIN
Company ON Jobs.CompanyID = Company.CompanyID
WHERE JobID = @JobID AND Jobs.Verify = 1

spJobCategories

Alter Procedure spJobCategories

@JobID int

As
SELECT JobCategory.JobID, JobCategory.CategoryID, CategoryType.Category
FROM CategoryType INNER JOIN JobCategory
ON JobCategory.CategoryID = CategoryType.CategoryID
WHERE JobCategory.JobID = @JobID ORDER BY CategoryType.Category

return