Rick Curtis - Tutorial 4

Add or Delete Child Records related to Parent Table

This tutorial is a follow-up to Tutorials 1-3. If you haven't read the previous tutorials yet, please do so before reading this tutorial since I won't cover the material that is the same as in earlier tutorials.

In Tutorials 1-3 I introduced how to link Parent and Child recordsets on a page and how to add new Child records. Now, what if your users want to edit their information on the parent record and also add or delete child records? Here's how it is done using the same Jobs application.

Here is the basic database set up for Companies.

Login Page - Login.asp

In order to edit a record you must first have your user login to select the appropriate record. This is accomplished through a basic login page where users enter CompanyID and Password into edit boxes. These values are passed as Request variable to the Edit.asp page.

Edit Company Page - Edit.asp

This is the exact process as decribed in Tutorial 1 so I won't go through the details here. You will create the parent recordset for rsCompanyLogin recordset on the page using a stored procedure. Then you will add the rsCompanyCategories recordset and link the child table to the parent table by setting the run-time value of the rsCompanyCategories to

rsCompanyLogin.Fields.Item("CompanyID").Value.

Now create a table on the Edit.asp page and add the CompanyCategories value from the rsCompanyCategories. Set this table as a repeat region and show all the rows from the recordset. Next you need to select the CompanyCategories value in the repeat region and use the Go to Detail Page Server Behavior. Set the values like in Figure 2. One thing that sometimes happens when you open the Go to Detail behavior is you get a message "The original column name specified in this script was not found." Somehow UltraDev is getting confused. The dialog box will show the correct table but will show fields from some other table. To fix this select some third table (things will now be in sync between the two edit boxes). Now go back and select the correct table again and the correct field value.

Figure 2A

Figure 2B

Add/Delete Company Categories Page - EditCategory.asp

Creating the Delete section of the Page

Add the rsCompanyLogin recrodset to the page along with the CompanyID and Password Request variables. Add the rsCompanyCategory recordset to the page. Create a table to hold your first form. Insert a form button. Name the form Deleteform. Drag an edit box into the form. Use the Dynamic Text box Server Behavior to set the value of the text box to CategoryType from rsCompanyCategory. You also need to add the Go to Specific Record Server Behavior. If you wish you can also add navigation buttons to move through the rsCompanyCategory recordset. Select the form button and add the Delete Record Server Behavior. It should be set like as in Figure 3C.

Figure 3A

Figure 3B

Figure 3C

Now here is a very interesting side note. Look at Figure 3C above. The Unique Key column is set for the primary key of the CompanyCategory table (CompanyCatID). What that means is that when you delete a record, you will only delete one record at a time. However, if you change the Unique Key Column to CompanyID (and if there are multiple CompanyCategory records for that CompanyID) you will delete all of the records for that CompanyID at once. This allows you to do multi-row deletes in one instance. It is both a powerful and a potentially dangerous practice if your aren't careful.

Adding Records

Add another table to your form. Name this one Addform. Create a rsCategory recordset (this should be the same as you created on your original Add Company page). Place a list box in the form and use the Dynamic List box Server Behavior to populate the list box with values from he Category table. Now add the Insert Record Server Behavior on the form to insert a single new record. Set up the insert as in Figure 3D

Figure 3D

What Does it All Mean?

When you login you are taken to the Edit.asp page for that company. You can edit values from teh parent table on this page, but you can't edit the values from a child table. By selecting the company category from the repeat region, you are taken to the EditCategories.asp page at the correct record. You can now delete this record by pressing the delete button and/or move through any other records to delete them. Also on the page is another form that will let you add new company categories.

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