Cascade Record & Image Deletes

Creating the Recordsets

We need to create three recordsets on the delete page. One recordset—rs_versions—will be conditional to keep the page from crashing when deleting categories without products. We will also need to custom code the SQL query to use the rs_products("P_ID") value to select the records from the versions table marked for deletion.

The first recordset we’ll create is rs_categories. The only parameter we need to pass from our recordset navigation page to the delete record page is the Category_ID value. This will be the filter. If your navigation page uses links to select records for deletion, then the filter will be a URL querystring. If you use a drop down menu it will be a form variable. The rs_products recordset will use the same filter. For reference see the UD generated recordsets below:

<%
Dim rs_categories__MMColParam
rs_categories__MMColParam = "1"
if (Request.QueryString("Category_ID") <> "") then rs_categories__MMColParam = Request.QueryString("Category_ID")
%>
<%
set rs_categories = Server.CreateObject("ADODB.Recordset")
rs_categories.ActiveConnection = MM_yourdatasource_STRING
rs_categories.Source = "SELECT * FROM Product_Categories WHERE Category_ID = " + Replace(rs_categories__MMColParam, "'", "''") + ""
rs_categories.CursorType = 0
rs_categories.CursorLocation = 2
rs_categories.LockType = 3
rs_categories.Open()
rs_categories_numRows = 0
%>
<%
Dim rs_products__MMColParam
rs_products__MMColParam = "1"
if (Request.QueryString("Category_ID") <> "") then rs_products__MMColParam = Request.QueryString("Category_ID")
%>
<%
set rs_products = Server.CreateObject("ADODB.Recordset")
rs_products.ActiveConnection = MM_yourdatasource_STRING
rs_products.Source = "SELECT * FROM Products WHERE Category_ID = " + Replace(rs_products__MMColParam, "'", "''") + ""
rs_products.CursorType = 0
rs_products.CursorLocation = 2
rs_products.LockType = 3
rs_products.Open()
rs_products_numRows = 0
%>

Nothing fancy there, but it gets interesting now. Create the recordset rs_versions in the data bindings window. Don’t bother to set any filter we will do that in code view. After you’ve created rs_versions, open the Code Inspector. Notice the comment I’ve added to the code block and the following conditional statement. If rs_products is empty rs_versions is not created for two reasons. First the page will crash if it tries to create rs_versions without a rs_products(“P_ID”) value. And second there are no version images to delete because there are no versions. The next code change is in the SQL query statement. We add a “Where” directive and set the “Where” condition equal to the rs_products record ID number (“P_ID”). When you’re done, close the “if then” statement, save the file, and return to design view.

<%
‘set condition for versions recordset creation
If Not rs_products.eof then
set rs_versions = Server.CreateObject("ADODB.Recordset")
rs_versions.ActiveConnection = MM_yourdatasource_STRING
‘set versions recordset equal to product id
rs_versions.Source = "SELECT * FROM Versions WHERE P_ID =" & rs_products("P_ID")
rs_versions.CursorType = 0
rs_versions.CursorLocation = 2
rs_versions.LockType = 3
rs_versions.Open()
rs_versions_numRows = 0
end if
%>

When you get back to design view the first thing you should notice is that the rs_versions recordset has vanished from the data bindings window. Don’t panic—it’s supposed to do this. Go ahead and add the delete records server behavior to the page for rs_categories.

Okay, now that we have the recordsets we need to get all the image names for deletion. To accomplish this we need a repeat region to loop through the Products table and one to loop through the Versions table and collect image values for the delete. You can use any method to create two repeat regions that works for you. I pull a field value from the Products table and apply the repeat region behavior selecting show all records. I then return to code view, copy and paste a copy of the repeat region and change the variable names as shown. You could also drag field elements from the Products and Versions onto the page before modifying rs_versions and cut and paste the resulting code. Regardless of method you need two repeat regions on the page one for rs_products and one for rs_versions.

<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rs_products_numRows = rs_versions_numRows + Repeat1__numRows
%>
<%
'repeat behavior modified  for nesting
Dim VRepeat1__numRows
VRepeat1__numRows = -1
Dim VRepeat1__index
VRepeat1__index = 0
rs_versions_numRows = rs_versions_numRows + VRepeat1__numRows
%>