Using Pure ASP Upload with custom INSERT commands

How to use the Pure ASP Upload Dreamweaver extension with a SQL Server Stored Procedure or your own custom code for INSERT commands rather than using Dreamweaver’s Insert Record server behaviour.

Using a Stored Procedure with a SQL Server database or creating your own custom INSERT command with an Access database it’s fairly simple to retrieve the identity of the newly created record, something which is often useful with a record insert. For more information about retrieving record identities with Access and SQL Server see my site: http://www.drdev.net/

1. Create your form, including a File Field to browse to the file you wish to upload.

2. Now add the Pure ASP Upload server behaviour as you would normally, selecting the options you require. Don’t specify a redirect URL, you can add that after your insert command code if required.

3. Now create your insert command as you wish, but instead of using Request.Form(“formElementName”) to collect the values from the form elements, as you would usually, use CStr(UploadFormRequest("formElementName ")).

For example, you might normally collect the form elements into variables like this:

strFileName = Request.Form(“fileUpload”)
strTitle = Request.Form(“txtTitle”)
strDescription = Request.Form(“txtDescription”)

With the Pure ASP Upload behaviour, modify the collection of the values from the form as follows:

strFileName = CStr(UploadFormRequest(“fileUpload”))
strTitle = CStr(UploadFormRequest(“txtTitle”))
strDescription = CStr(UploadFormRequest(“txtDescription”))

That’s it, you can now use the values in the variables with your own insert command whether it uses a Stored Procedure or it is simply your preferred method of inserting a record in an Access or SQL Server database.

Here’s an example insert command for an Access database which retrieves the identity of the newly created record and redirects the user to a new page. Notice I’ve used the Replace single quotes with two single quotes in this example to stop the INSERT command fowling up if they were included in the string values entered in the form:

If CStr(UploadFormRequest("Submit")) = "Upload File" Then
 strFileName = Replace(CStr(UploadFormRequest(“fileUpload”)),”’”, “’’”)
 strTitle = Replace(CStr(UploadFormRequest(“txtTitle”)) ,”’”, “’’”)
 strDescription = Replace(CStr(UploadFormRequest(“txtDescription”)) ,”’”, “’’”)

 Set commInsert = Server.CreateObject("ADODB.Connection")
 commInsert.Open MM_YourConn_STRING
 commInsert.Execute("INSERT INTO tblFiles(strFileName, strTitle, strDescription) VALUES('" & strFileName & "', '" & strTitle & "', '" & strDescription & "');") ' Execute the insert command
 Set rsNewID = commInsert.Execute("SELECT @@IDENTITY") ' Create a recordset and SELECT the new Identity
 intNewID = rsNewID(0) ' Store the value of the new identity in variable intNewID
 
 rsNewID.Close
 Set rsNewID = Nothing
 commInsert.Close
 Set commInsert = Nothing
 
 Response.Redirect("FileDetail.asp?NewID=" & intNewID)
End If