Rick Curtis - Tutorial 3

Adding Multiple Records to a Child Table Using Stored Procedures

This tutorial is a follow-up to Tutorial 2. If you haven't read Tutorial 2 yet, please do so before reading this tutorial.

For many applications, you want users to be able to add new records. The basic Server Extensions that ship with UltraDev allow 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. Tutorial 2 dealt with the simple case of adding one new parent record and adding one corresponding child record. But what if you want to add one parent record and multiple child records? Here's how it is done using our same Jobs application. I won't cover the material that is the same as in Tutorial 2.

This is a great example of why you should use stored procedures. I was able to change my application to allow multi-row inserts on the child table almost entirely by changing the stored procedure code. I only had to make one change in all of the UltraDev generated pages from the example in Tutorial 2 (explained below). By encapsulating your application logic in your database rather than in your Web page you save yourself significant development time in the long run and the Web application is much more portable. My thanks to Chris at the SQL Server Programming Newsgroup for his help on the stored procedure.

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 1

• 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 category 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 1 above.

Are you ready for the one change to the UltraDev code? To make this page work with multi-row insert I had to make a lot of changes to the commJobAdd stored procedure code. The only change I had to do in UltraDev was to open the commAddJob command and change the following variable:

The Default value stayed the same--(nothing)-- and the Run-time value stayed the same--Request("CategoryID")

Figure 2

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 foreign 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 (see Tutorial 2) which populates the rsJobSearch recordset and also to the spJobCategories stored procedure (see Tutorial 2) 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

commJobAdd

For those of you familiar with stored procedures you will notice a few changes from the stored procedure we used in Tutorial 2. Here we have defined @CategoryID and @PopulationID as varchar since we will be passing the contents of a multi-select list to the stored procedure. If the user selects only one item in the list it will be sent as "2" if 2 is the CategoryID. If multiple items are selected it will be sent as a comma delimited list "2,3,5". Although the CategoryID field is an integer we must convert the values sent to varchar so it can be properly parsed by the stored procedure. We are creating a dynamic SQL statement in order to properly parse the CategoryID delimited list. We create a new variable @CatInsert to serve as the dynamic SQL statement and then execute the statement. You can follow the sequence by looking at the code in red below. SQL allows the use of the INSERT...SELECT statement when you have more than one row to insert. Here's what the code in red is doing.

DECLARE @CatInsert varchar(2000) -- Declare a new variable for the dynamic SQL statement

SET @CatInsert = 'INSERT INTO JobCategory (JobID, CategoryID) SELECT ' + CONVERT(varchar,@JobID) + ', Category1ID From CategoryType Where Category1ID IN (' + @CategoryID + ')' -- Create a SELECT statement that includes the new JOBID and compares whatever is in @CATEGORYID list with against the values in the CategoryType Lookup table.

If you have only one value chosen in the list box you will get a single row inserted into the JobCategory table:

If you have only one value chosen in the list box you will get multiple rows inserted into the JobCategory table:

Alter PROCEDURE "spAddJob"

-- Author: Rick Curtis
-- Date: July 21, 2000
-- Purpose: Insert new entry into Jobs table and related data into child tables JobCategory and JobPopulations
-- Declare variables for inserts to all 3 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 [varchar](100), @PopulationID [varchar](100), @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

DECLARE @CatInsert varchar(2000)

SET @CatInsert = 'INSERT INTO JobCategory (JobID, CategoryID) SELECT ' + CONVERT(varchar,@JobID) + ', Category1ID From CategoryType Where Category1ID IN (' + @CategoryID + ')'

exec(@CatInsert)

-- Insert new values into JobPopulation table

DECLARE @PopInsert varchar(2000)

SET @PopInsert = 'INSERT INTO JobPopulation (JobID, PopulationID) SELECT ' + CONVERT(varchar,@JobID) + ', PopulationID From Population Where PopulationID IN (' + @PopulationID + ')'

exec(@PopInsert)

Return