delete database records and files

delete database records and files (Full Version)

All Forums >> [Web Development] >> ASP and Database

Message

walrus -> delete database records and files (1/23/2005 17:00:12)

I have several "real estate" sites that use Access databases to store property records along with photos of the properties. I use the Lewis Moten III 'Without COM' script to upload records to the database so that the images are stored as files outside the database in a separate folder.
This all work really well (thanks to Spooky's guidance) but I can't work out how to use the DELETE function to delete both the database record and the associated files at the same time. At the moment I DELETE the database record and simply leave the image file on the server.
I assume this would be easy if I stored the files within the database, but they are stored seperately.
I am unable to grasp the concept of how to administer this global deletion. If anyone can point me in the right direction I am sure I can work out the rest. I just need a helping hand to get going.

This probably goes in there somewhere

Dim Upload
Dim FileName
Dim Folder
Set Upload = New clsUpload
FileName = Upload("File1").FileName
Folder = Server.MapPath("Uploads") & "\"
Upload.DeleteFile Folder & FileName
Upload("File1").SaveAs Folder & FileName
Set Upload = Nothing

Here is an example of the typical code I am using to uolad the data originally


<%
Dim Upload
Dim FileName1
Dim FileName2
Dim Folder

Set Upload = New clsUpload

' Grab the file name
FileName1 = Upload.Fields("File1").FileName
FileName2 = Upload.Fields("File2").FileName

' Get path to save file to
Folder = Server.MapPath("photos") & "\"

' Set the file name to be unique.
FileName1 = Upload.UniqueName(Folder, FileName1)
FileName2 = Upload.UniqueName(Folder, FileName2)

' Save the binary data to the file system
Upload("File1").SaveAs Folder & FileName1
Upload("File2").SaveAs Folder & FileName2

' Check to see if file extension is valid
Select Case Ext
	Case "GIF", "BMP", "PNG", "JPG"
		FileOK = True
	Case Else
		FileOK = False
End Select

' If file size is greater then 0.25 MB
If Upload("File1").Length > 256000 Then

	' Notify user of the error.
	Response.Write "File size must be 250kb or less"
	
	' Stop all execution past this line.
	Response.End
	
End If

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
objConn.Open Application("salesdata_ConnectionString")

objRs.Open "Sales", objConn, 3, 3
objRs.AddNew

objRs.Fields("Sales_Door_Number").Value = Upload.Fields("Sales_Door_Number").value
objRs.Fields("Sales_Building").Value = Upload.Fields("Sales_Building").value
objRs.Fields("Sales_Street").Value = Upload.Fields("Sales_Street").value
objRs.Fields("Sales_Town").Value = Upload.Fields("Sales_Town").value
objRs.Fields("Sales_Area").Value = Upload.Fields("Sales_Area").value
objRs.Fields("Sales_Postcode").Value = Upload.Fields("Sales_Postcode").value
objRs.Fields("Sales_Price").Value = Upload.Fields("Sales_Price").value
objRs.Fields("Sales_Type").Value = Upload.Fields("Sales_Type").value
objRs.Fields("Sales_Beds").Value = Upload.Fields("Sales_Beds").value
objRs.Fields("Sales_Tenure").Value = Upload.Fields("Sales_Tenure").value
objRs.Fields("Sales_Description").Value = Upload.Fields("Sales_Description").value
objRs.Fields("Sales_Details").Value = Upload.Fields("Sales_Details").value
objRs.Fields("Sales_Bullet1").Value = Upload.Fields("Sales_Bullet1").value
objRs.Fields("Sales_Bullet2").Value = Upload.Fields("Sales_Bullet2").value
objRs.Fields("Sales_Bullet3").Value = Upload.Fields("Sales_Bullet3").value
objRs.Fields("Sales_Bullet4").Value = Upload.Fields("Sales_Bullet4").value
objRs.Fields("Sales_Bullet5").Value = Upload.Fields("Sales_Bullet5").value
objRs.Fields("Sales_Status").Value = Upload.Fields("Sales_Status").value
objRs.Fields("Sales_Office").Value = Upload.Fields("Sales_Office").value
objRs.Fields("Sales_Office_Tel").Value = Upload.Fields("Sales_Office_Tel").value
objRs.Fields("Sales_Photo_Main").Value = Filename1
objRs.Fields("Sales_Photo_2").Value = Filename2

objRs.Update
objRs.Close
Set objRs = Nothing
Set objConn = Nothing
Set Upload = Nothing

%>

Thanks if you can help...

Spooky -> RE: delete database records and files (1/23/2005 23:03:53)
What does your delete record code look like?

walrus -> RE: delete database records and files (1/24/2005 10:49:28)
Hi Spooky, Im simply using the DELETE and UPDATE clauses within the DRW with the field taken from a form, ie delete all records WHERE ID=::ID:: The pages are always on a DIET (of course) so that I can make any changes to the asp code. Does this help?

rdouglass -> RE: delete database records and files (1/24/2005 13:21:09)
quote: but I can't work out how to use the DELETE function to delete both the database record and the associated files at the same time Can't delete physical files outside the DB at the same time AFAIK. I use the FSO's (File System Objects) something like this: <% set fs=createobject("scripting.filesystemobject") DefaultFile= myFolderPath & "\" & trim(myFileName) If fs.fileexists(DefaultFile) then fs.deletefile DefaultFile Response.write(myFileName & " file deleted.<br>") Else Response.write("There was an error with the " & trim(myFileName) & " file.<br>") End If %> That any help?

Spooky -> RE: delete database records and files (1/24/2005 13:35:51)
Just to qualify "not at the same time", you will perform the delete from database, then immediately after in the same script, you can delete from the file system using a script similar to the one above.

walrus -> RE: delete database records and files (1/24/2005 13:40:11)
So how do incorporate this into the DRW. DefaultFile= myFolderPath & "\" & trim(myFileName) How do I tell this script what the myFileName is. The rest I understand. Thanks

Spooky -> RE: delete database records and files (1/24/2005 13:45:33)
Folder = Server.MapPath("photos") & "\" & filename However, you would have to have filename set as a variable before / after deleting the record.

walrus -> RE: delete database records and files (1/24/2005 13:54:05)
Thanks. The filename is contained in the field "photo_main". I just simply do know how to make this value appear in your example. I now know where it goes, just not it would look like. [8\|]

Spooky -> RE: delete database records and files (1/24/2005 14:50:33)
How are you actioning the delete? Could you pass the file name from one page to the other as part of the form / url post?

walrus -> RE: delete database records and files (1/24/2005 15:12:30)

At the moment, I simply delete the record based on the Key field

<%
fp_sQry="DELETE * FROM Results WHERE ID =::ID::"
fp_sDefault=""
fp_sNoRecords="record deleted"
fp_sDataConn="upload"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=12
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

This is passed from a previous page where the user has chosen which record they wish to action from a list provided with a DRW. This is pretty basic stuff using a hyperlink with paramaters.

The same format applies to updating records.

<%
fp_sQry="UPDATE Results SET Property_Building='::Property_Building::', Property_Street='::Property_Street::', Property_Town='::Property_Town::', Property_Area='::Property_Area::', Property_Postcode='::Property_Postcode::', imagepath='::imagepath::', Property_Beds='::Property_Beds::', Property_Type='::Property_Type::', Property_Price=::Property_Price::, Tenure='::Tenure::', Property_Description='::Property_Description::', Property_Details='::Property_Details::', x_Company='::x_Company::', x_Phone='::x_Phone::', x_Email='::x_Email::', x_Website='::x_Website::' WHERE ID=::ID::"
fp_sDefault="Property_Building=&Property_Description=&x_Website="
fp_sNoRecords="Property has been updated"
fp_sDataConn="upload"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=22
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

The field in this example is "imagepath"
The problem with the delete example is that the database record is deleted but the image file remains on the server. The user has no interest in manually deleting the file, mainly because they don't know the filename.
Does that make sense?

Spooky -> RE: delete database records and files (1/24/2005 15:22:41)
So - pass 2 values from the hyperlink deletepage.asp?ID=1234&Image=abcd.jpg Then, you can base your delete on the querystring for "image" (if the database record is validated)

walrus -> RE: delete database records and files (1/24/2005 15:50:38)
quote: Folder = Server.MapPath("photos") & "\" & filename would become Folder = Server.MapPath("photos") & "\" & "abcd.jpg" ???

Spooky -> RE: delete database records and files (1/24/2005 15:58:38)
Folder = Server.MapPath("photos") & "\" & request.querystring("image") Assuming the path is correct. Id also validate that the original ID exists and only delete the image if the database delete occurs.

walrus -> RE: delete database records and files (1/24/2005 16:23:10)
quote: request.querystring aahhhh!! New one on me. So how would one validate existance of the orginal ID, or should I save that for a future post? Thanks guys, I'll try to get this working and let you know.

Spooky -> RE: delete database records and files (1/24/2005 16:27:36)
Yeah, I wouldnt use the DRW myself - show the whole delete pages code, and Ill see what I can suggest

walrus -> RE: delete database records and files (1/25/2005 14:43:05)

Thanks Spooky,
Im not making any progress with the FSO but this code works fine...

<body>
<table>
  <tbody>
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="DELETE * FROM Letting WHERE LettingID=::LettingID::"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=27 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="salesdata"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&LettingID=3&Letting_Office=202&Letting_Office_Tel=202&Letting_Door_Number=202&Letting_Building=202&Letting_Street=202&Letting_Town=202&Letting_Area=202&Letting_Postcode=202&Letting_Rent=3&Letting_Type=202&Letting_Beds=202&Letting_Photo_Main=202&Letting_Photo_2=202&Letting_Photo_3=202&Letting_Photo_4=202&Letting_Photo_5=202&Letting_Description=203&Letting_Details=203&Letting_Notes=202&Letting_PDF=202&Letting_Bullet1=202&Letting_Bullet2=202&Letting_Bullet3=202&Letting_Bullet4=202&Letting_Bullet5=202&Letting_Status=202&"
fp_iDisplayCols=27
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
      <td>
<%=FP_FieldVal(fp_rs,"LettingID")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Office")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Office_Tel")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Door_Number")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Building")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Street")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Town")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Area")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Postcode")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Rent")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Type")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Beds")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Photo_Main")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Photo_2")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Photo_3")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Photo_4")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Photo_5")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Description")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Details")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Notes")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_PDF")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Bullet1")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Bullet2")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Bullet3")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Bullet4")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Bullet5")%></td>
      <td>
<%=FP_FieldVal(fp_rs,"Letting_Status")%></td>
    </tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
</body>

Spooky -> RE: delete database records and files (1/25/2005 14:48:12)
The code is attempting to display the result of a deleted record - it doesnt does it?

walrus -> RE: delete database records and files (1/25/2005 15:07:53)
No, it deletes the record, but I included a list of the fields in case you need to know what they were. (please ignore that they are in a table) I have been trying this format but it doesn't pick up the field <% Dim Upload Dim FileName Dim Folder Set Upload = New clsUpload FileName = Upload("Letting_Photo_Main").FileName Folder = Server.MapPath("photos") & "\" Upload.DeleteFile Folder & FileName Upload("Letting_Photo_Main").SaveAs Folder & FileName Set Upload = Nothing %> I have feeling I may be on the wrong track here.

walrus -> RE: delete database records and files (3/6/2005 17:09:34)
quote: So - pass 2 values from the hyperlink deletepage.asp?ID=1234&Image=abcd.jpg This is the key. Nice and simple. It took me a while to get going because the delete file script was in a separate page accessed by Response.Redirect which didn't work. It works by simply having the delete code before the <html> and leaving the DRW as it was. I haven't tried other ordering. I searched high and low for the answer to this. As usual the answer was here at outfront. Thanks guys.

Page: [1]

0.078125