Cascade Record & Image Deletes

The Delete Code

First we need to add the functions for the image deletes. I use the functions from Marcellino Bommezijn’s excellent tutorial on image deletes. These can be added to the page or—better yet--in an include file.

<%
Function newFileSystemObject()
set newFileSystemObject=Server.CreateObject("Scripting.FileSystemObject")
End Function
%>
<%
Function fileExists(aFileSpec)
fileExists=newFileSystemObject.fileExists(aFileSpec)
End Function
%>

Find the UD delete SB and look for the comment in bold grey type below. This is where we create the File Scripting Object (FSO) that will handle the category image deletes, set variable values for the path and image names for rs_categories, then call Marcellino Bommezijn’s functions to delete any rs_categories image that exists.

' *** Delete Record: construct a sql delete statement and execute it
If (CStr(Request("MM_delete")) <> "" And CStr(Request("MM_recordId")) <> "") Then
  ' create the sql delete statement
MM_editQuery = "delete from " & MM_editTable & " where " & MM_editColumn & " = " & MM_recordId
If (Not MM_abortEdit) Then
' execute the delete
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection

Find the edit connection line of the delete SB shown bold in the line above—it’s the last line before the UD delete SB executes--and make some space because here’s where the magic happens.

' This is where we delete the category image file before we delete the record!
 Set File = CreateObject("Scripting.FileSystemObject")
 ImagePath = Server.MapPath("..\site_images\")
 ImagePath = ImagePath & "\" & (rs_categories.Fields.Item("Category_Image").Value)
 ' check if file exists and if true delete the category image file
 If fileExists(ImagePath) Then
 File.DeleteFile(ImagePath)
 End If

Now that we’ve taken care of the Product_Categories image, we must nest the repeat regions we created earlier and modify them to perform image deletes. This allows us to employ our repeat regions to loop through the rs_products and rs_versions tables and extract values for the image deletes. Insert the opening “While Wend” statement from the rs_products repeat region we created earlier.

' loop through rs_products
While ((Repeat1__numRows <> 0) AND (NOT rs_products.EOF)) 

Before we delete our product images, however, we need to delete the rs_versions images. Remember the rs_versions recordset we created above uses the rs_products(“P_ID”) value to select related rs_versions records marked for deletion and extract the image field values. So we add our rs_versions “While Wend” statement immediately below the rs_products “While Wend.”

' loop through and delete version images using a nested repeat behavior
While ((VRepeat1__numRows <> 0) AND (NOT rs_versions.EOF))

Now that we have collected all the image values, we create our rs_versions (FSO) and set variables for the image path and name values, and call Marcellino Bommezijn’s functions to perform the file delete as above. Make sure you make the modifications needed to pull image name values from rs_versions. If the path to your version images is different you will need to change that as well.

Set File = CreateObject("Scripting.FileSystemObject")
ImagePath = Server.MapPath("..\site_images\")
ImagePath = ImagePath & "\" & (rs_versions.Fields.Item("Version_Image").Value)
' check if file exists and if true delete the file
If fileExists(ImagePath) Then
File.DeleteFile(ImagePath)
End If  
‘close rs_versions repeat region
VRepeat1__index=VRepeat1__index+1
VRepeat1__numRows=VRepeat1__numRows-1
rs_versions.MoveNext()
Wend

Okay, now that we’ve taken care of the version images we close the rs_versions nested loop and re-enter the rs_products loop to perform the rs_products image delete. Again add the FSO and delete code.

’fall out of rs_versions loop and delete rs_product image
Set File = CreateObject("Scripting.FileSystemObject")
ImagePath = Server.MapPath("..\site_images\")
ImagePath = ImagePath & "\" & (rs_products.Fields.Item("P_Image").Value)
' check if file exists and if true delete the file
If fileExists(ImagePath) Then
File.DeleteFile(ImagePath)
End If 
Set File = Nothing   
‘close rs_products repeat region
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rs_products.MoveNext()
Wend
%>

That’s all there is to it. The stock UD generated code deletes the rs_categories record and the relationships we created in our access database cascades the delete to related records in the Products and Versions tables.

'Execute record delete
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
 MM_editCmd.ActiveConnection.Close
 If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>