INSERT RECORD & UPLOAD FILE WITH ONE BUTTON

Report · Sep 10, 2010

I'm developing an ASP page that collects data from users and drops it into a database on the serer. One of the fields on the form will be an "attachment" which could be a .docx, jpeg, etc...

I'm able to insert the record to the database and right the filename and path using the "file" field. I've also added a second form on the page which allows the user to select the same file again to upload it to the server.

I find two problems with this method immediatly: 1) Users should only have to select the file once and click on submit once 2) The path&file should be saved with the new location of the file for future retrieval and not the path&file of where it came from.

I've searched and tried using the FREE ASP Uploads but can't get it to work by saving the data to the database and uploading the data at the same time.

It seems that I need to be able to perform two actions from one submit button.

I'd greatly appreciate some tips/tricks/suggestions on how to do this if someone has done this before. I've spent 3 days reading and reading and testing and testing with no results. I'm not a pure asp programmer so I was hoping Dreamweaver had something built in but it seems that it is only for Cold Fusion pages.

Thanks in advance for any help.

Report · Sep 12, 2010

I just checked your other thread on this and saw that you asked a question about sharing a script - sorry for not replying in that thread. I don't have any scripts I can share, as I said I have only the commercial extensions. One is for ASP and I have another for PHP. If you'd like more info on these PM me and I give you the details. They do what you're looking for plus more - inserting the file name and location into the DB, uploading the file to a predetermined folder or creating a folder on the fly (based on a logged in user), progress bar, generate an email, etc etc. So if you've spent three days on this without success, then it may be a matter on what your time is worth!

But for now, what free scripts have you tried? Are you able to upload the file but just not in the same step as the insert? I may be able to help you get that working - and there is no reason it cant be accomplish with the same form submission.

Report · Sep 13, 2010

Lon -

Thanks for your continued effort to help me through this. I have been trying to use Upload.asp written by Jacob Gilley and ASPUpload. I can get them to work fine, I am just struggling to make everything connect on the insert record where I have the rest of the data I've collected from the users.

Here's the form I've asked the user to fill out. I would really like to make the "Attachment" field the file field and be able to browse the file, upload the file, and return to the form with the file information (title & Path) saved in this field. The plan is to keep all the files being uploaded in the same directory so the path should be hard coded. The users will then be able to update form or view it later and need to be able to retrieve the uploaded file.

I know how to restrict the file size and types of file to upload with the upload snippets of code I have already. I just can't get it all together as you can tell, I tried to use a upload file "checkbox" to upload the files first before filling out the form which seems very non-user-friendly.

Report · Sep 15, 2010

I think you're getting close - you've gotten past a couple majot hurdels, one being getting the upload script to work, even if it's by itself.

The key is, what does the script provide for an action, when you're using it by itself, as an action when the upload completes - a redirect perhaps? If so,then here's one possible solutions;

Place the upload field and insert fields in the same form, with only one button. So when the form is submitted that will execute both actions. You'll want the upload come before the insert, then when it's complete, leave out the redirect or other end action and let the code move on to the insert script. The upload has to come first because it needs the path to retrieve the file. When the insert is complete, then it will re-direct.

Then here's the other catch - before the insert, you're going to have to strip everything but the filename from the field before the insert record, assign the result to a variable and replace the form field in the insert script with your variable. The stripping part, you''have to google that up, that's one thing I let my extensions handle. But it would be something like this (but only in concept, not syntax)

myFileUploadVariable = Request.Form("myFileUploadField") minus everything but the filename, or everything after the last "/" or however it's done.

Once you get our variable set up and populated with just the filename, then set up your insert as usual, and locate the line of code for that Request.Form field, it will look something like this:

 MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 201, 1, 255, Request.Form("myFileUploadField")) ' adLongVarChar

Then just replace the form field with your variable -

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 201, 1, 255, myFileUploadVariable) ' adLongVarChar

You could skip all this, and just insert the whole path, and do the stripping later when you go to reference it for a download.

So let me know what you think and if this method works for you, there are a couple options at least - but this is the most basic and can be built on.

Report · Sep 17, 2010

I've gone with the FreeASPUpload. I've gotton the code to upload the file but now I get an error when I am trying to use ASP to connect to my database.

Microsoft JET Database Engine error '80004005'

Could not find file

I've given full permission the IUSR guest account and still can't hit the database.

However, I can insert a record into the database when I set up use Dreamweavers connections and insert record behavior without uploading the file.

<%@ Language=VBScript %>
<% 
option explicit 

Response.Expires = -1
Server.ScriptTimeout = 600
' All communication 
must be in UTF-8, including the response back from the 
request
Session.CodePage  = 65001
%>
<!-- #include file="freeaspupload.asp" -->
<%

  ' ****************************************************
  ' Change 
the value of the variable below to the pathname
  ' of a directory with write 
permissions, for example "C:\Inetpub\wwwroot"
  ' 
****************************************************
  Dim uploadsDirVar
  uploadsDirVar = 
"c:\inetpub\wwwroot\FacilityConditionTracking\files" 
  
  ' Note: this file uploadTester.asp is just an example to demonstrate
  ' 
the capabilities of the freeASPUpload.asp class. There are no plans
  ' to 
add any new features to uploadTester.asp itself. Feel free to add
  ' your 
own code. If you are building a content management system, you
  ' may also 
want to consider this script: http://www.webfilebrowser.com/
function OutputForm()
%>
    <form name="frmSend" method="POST" 
enctype="multipart/form-data" accept-charset="utf-8" action="uploadTester4.asp" 
onSubmit="return onSubmitForm();">
 <B>File 
names:</B><br>
    File 1: <input name="attach1" type="file" 
size=35><br>
    <br> 
 <!-- These input elements are 
obviously optional and just included here for demonstration purposes 
-->
 <B>Additional fields (demo):</B><br>
 Enter an 
AiM Work Order Number: <input type="text" name="AimWO"><br>
    
<input style="margin-top:4" type=submit value="Upload">
    
</form>
<%
end function
function TestEnvironment()
    Dim fso, fileName, testFile, 
streamTest
    TestEnvironment = ""
    Set fso = 
Server.CreateObject("Scripting.FileSystemObject")
    if not 
fso.FolderExists(uploadsDirVar) then
        TestEnvironment = 
"<B>Folder " & uploadsDirVar & " does not 
exist.</B><br>The value of your uploadsDirVar is incorrect. Open 
uploadTester.asp in an editor and change the value of uploadsDirVar to the 
pathname of a directory with write permissions."
        exit function
    
end if
    fileName = uploadsDirVar & "\test.txt"
    on error resume 
next
    Set testFile = fso.CreateTextFile(fileName, true)
    If 
Err.Number<>0 then
        TestEnvironment = "<B>Folder " & 
uploadsDirVar & " does not have write permissions.</B><br>The 
value of your uploadsDirVar is incorrect. Open uploadTester.asp in an editor and 
change the value of uploadsDirVar to the pathname of a directory with write 
permissions."
        exit function
    end if
    Err.Clear
    
testFile.Close
    fso.DeleteFile(fileName)
    If Err.Number<>0 
then
        TestEnvironment = "<B>Folder " & uploadsDirVar & " 
does not have delete permissions</B>, although it does have write 
permissions.<br>Change the permissions for 
IUSR_<I>computername</I> on this folder."
        exit 
function
    end if
    Err.Clear
    Set streamTest = 
Server.CreateObject("ADODB.Stream")
    If Err.Number<>0 
then
        TestEnvironment = "<B>The ADODB object 
<I>Stream</I> is not available in your 
server.</B><br>Check the Requirements page for information about 
upgrading your ADODB libraries."
        exit function
    end if
    
Set streamTest = Nothing
end function
function SaveFiles
    Dim Upload, fileName, fileSize, ks, i, fileKey
    Set Upload = New FreeASPUpload
    Upload.Save(uploadsDirVar)
    ' If something fails inside the script, but the exception is 
handled
    If Err.Number<>0 then Exit function
    SaveFiles = ""
    ks = Upload.UploadedFiles.keys
    if 
(UBound(ks) <> -1) then
    response.write "<h3>Upload 
Success!</h3>"
        SaveFiles = "<B>You have successfully 
uploaded:</B> "
                
                Dim 
CurrentTime
                CurrentTime = NOW()
                Dim 
cn,sql,theFile,sFileTitle
                Set cn = 
Server.CreateObject("ADODB.Connection")
                cn.Open 
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& 
Server.MapPath("Database/FacilitiesPlan.mdb") 
                
        for each fileKey in Upload.UploadedFiles.keys
        

                SaveFiles = SaveFiles & 
Upload.UploadedFiles(fileKey).FileName & " <br>"
               ' 
SaveFiles = SaveFiles & "<br>You can view this document here: "    

                'SaveFiles = SaveFiles & "http://www.mywebsite.co.uk/tempUploads/" 
& Upload.UploadedFiles(fileKey).FileName
        
                

                theFile = Upload.UploadedFiles(fileKey).FileName ' the 
filename of the actual file
                sFileTitle = 
Upload.Form(Upload.UploadedFiles(fileKey) & "_name") ' what the user entered 
in the filename field
    
                'insert query
    
                sql = "Insert into NotedIssues(attachment,AiMWO) 
values (" _
                & "'" & attach1 & "', " 
_
                & "'" & AiMWO & ")"
  
                
cn.Execute(sql)
        
        next
                    

                'Now that we have displayed the table data lets close the 
connection
                cn.Close
                Set cn = 
nothing
        
    else
        SaveFiles = "The file name specified 
in the upload form does not correspond to a valid file in the system."
    
end if
    'SaveFiles = SaveFiles & "<br>Enter a number = " & 
Upload.Form("enter_a_number") & "<br>"
    'SaveFiles = SaveFiles 
& "Checkbox values = " & Upload.Form("checkbox_values") & 
"<br>"
end function
%>
<HTML>
<HEAD>
<TITLE>Test Free ASP Upload 
2.0</TITLE>
<meta http-equiv="Content-Type" content="text/html; 
charset=utf-8" />
<style>
BODY {background-color: 
white;font-family:arial; 
font-size:12}
</style>
<script>
function onSubmitForm() 
{
    var formDOMObj = document.frmSend;
    if (formDOMObj.attach1.value 
== "")
        alert("Please press the Browse button and pick a 
file.")
    else
        return true;
    return 
false;
}
</script>
</HEAD>
<BODY>
<br><br>
<div style="border-bottom: #A91905 2px 
solid;font-size:16">Upload files to your server</div>
<%
Dim 
diagnostics
if Request.ServerVariables("REQUEST_METHOD") <> "POST" 
then
    diagnostics = TestEnvironment()
    if diagnostics<>"" 
then
        response.write "<div style=""margin-left:20; margin-top:30; 
margin-right:30; margin-bottom:30;"">"
        response.write 
diagnostics
        response.write "<p>After you correct this problem, 
reload the page."
        response.write "</div>"
    
else
        response.write "<div 
style=""margin-left:150"">"
        OutputForm()
        response.write 
"</div>"
    end if
else
    response.write "<div 
style=""margin-left:150"">"
    OutputForm()
    response.write 
SaveFiles()
    response.write "<br><br></div>"
end 
if
%>
</BODY>
</HTML>

Report · Sep 17, 2010

I think I see what you're trying to do, but not entirely - are you trying to upload and insert with the same form similar to what I was talking about in my previous post? If so, then with the script you're using, you'd be better off going back to our original solutions as shown in the screenshot of the form you posted.

and since the script gives you a confirmation after the upload without a redirect, I think it would be the better solution. I had actually already looked at that script a few days ago and did some tinkering and got the upload to work.

So, I did some more tinkering and got it to work along with an insert form. I did use the two step process - it just looked messy since the forms and content are dynamically displayed. Here's what I did, hopefully it will work for you too.

1. First, I took the script and took out the un-needed code such as the voting poll, and the other input fields. I also just kept it to one file upload field. (Because of this, I'm not sure how my solution will respond when using more than one)

2. Second, I placed my static form on the page below everything else. Tested it again for upload, still works - and removed the confirmation data from displaying. But this data already included the filename, so now I needed to get that into my form.

3. After a bit of fussing with the variables, with no success, I fell back on the session variable. Session variables when used willy-nilly are not best practice, but I just couldn't get the form to see the filename using a regular variable. I'm not sure if it's even possible given the way the script works, but I admit I didn't try everything.

4. Anyway, I declared the session var in the save files function code:

for each fileKey in Upload.UploadedFiles.keys

SaveFiles = SaveFiles & Upload.UploadedFiles(fileKey).FileName

Session("savedFile") = Upload.UploadedFiles(fileKey).FileName

5. I had already included another text field in my form to display the filename by setting the initial value to one of the variables. So, I changed the value to the session var by placing <%=Session("savedFile")%> in the Initial value box in the textbox properties. Tested again, the file uploaded still and, yayyy, the filename populated the textbox. (the textbox should either be changed to a hidden field, or locked from editing). Now, all I had to do was add the insert behavior.

6. That produced a compilation error on option explicit. So, I simply removed the option explicit declaration, tested one last time - Done!

Report · Sep 21, 2010

I have made progress and can now upload the file and insert a record into the Access DB from the same submit button. However, my insert statement was using literal values and not the vaules from the form. I'm not sure what the correct syntax is to pull the value out of the field on the form.

"Request.Form()"?? or something else.