|
| |
|
|
walrus
Posts: 547
Joined: 3/13/2003
From: London
Status: offline
 |
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...
_____________________________
I hope The Boss isn't reading this, ...she thinks I program everything!
|
|
|
|
walrus
Posts: 547
Joined: 3/13/2003
From: London
Status: offline
|
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?
_____________________________
I hope The Boss isn't reading this, ...she thinks I program everything!
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
walrus
Posts: 547
Joined: 3/13/2003
From: London
Status: offline
|
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
_____________________________
I hope The Boss isn't reading this, ...she thinks I program everything!
| |
|
|
|
walrus
Posts: 547
Joined: 3/13/2003
From: London
Status: offline
|
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?
_____________________________
I hope The Boss isn't reading this, ...she thinks I program everything!
| |
|
|
|
walrus
Posts: 547
Joined: 3/13/2003
From: London
Status: offline
|
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"
???
_____________________________
I hope The Boss isn't reading this, ...she thinks I program everything!
| |
|
|
|
walrus
Posts: 547
Joined: 3/13/2003
From: London
Status: offline
|
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.
_____________________________
I hope The Boss isn't reading this, ...she thinks I program everything!
| |
|
|
|
walrus
Posts: 547
Joined: 3/13/2003
From: London
Status: offline
|
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>
_____________________________
I hope The Boss isn't reading this, ...she thinks I program everything!
| |
|
|
|
walrus
Posts: 547
Joined: 3/13/2003
From: London
Status: offline
|
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.
_____________________________
I hope The Boss isn't reading this, ...she thinks I program everything!
| |
|
|
|
walrus
Posts: 547
Joined: 3/13/2003
From: London
Status: offline
|
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.
_____________________________
I hope The Boss isn't reading this, ...she thinks I program everything!
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
