troubleshooting Question

I need to upload a document file and an image file when I create or edit a database record. Advice on how to proceed?

Avatar of Eric Bourland
Eric BourlandFlag for United States of America asked on
ColdFusion Language
298 Comments1 Solution750 ViewsLast Modified:
ColdFusion 8
MS SQL Server 2005
IIS 6

Hi. I've got a project I've been thinking about for a while, and I should get some advice about it before I begin.

In brief: I need to upload a document file and an image file (both) when I create or edit a database record. I need to associate the image and the document with the record.

Details:

I have a working application here, which allows an authorized user to edit a database record (or create or delete one):

http://ebwebwork.com/cep/admin/

The edit record interface looks like this:

http://ebwebwork.com/cep/admin/cep_document_edit.cfm?DocumentID=16

... a simple form to update a record.

I need to add two components to this interface at cep_document_edit.cfm:

1) a field to browse for and upload an image file (GIF, PNG, JPG) to folder c:\upload\cep-dc.org (outside of web root, for security)

2) another field to browse for and upload a document file (DOC or PDF) to folder c:\upload\cep-dc.org (outside of web root, for security)

Not too difficult so far.

The complications begin here:

1) I need to use CFCONTENT to deliver the images and documents from the folder, to the production web site; and I believe I will need to refer to each image and each document by a unique ID. Can that Unique ID be the unique record ID of the database record? Or does each image and document need a separate unique ID?

How should I think about, and go about this task?

The table is: tbl_CEP_Documents

Its columns are: DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified

Should I add two more columns: DocumentPath, ImagePath ... to contain the correct path to the document or image file in folder c:\upload\cep-dc.org?

Maybe that would work..... each document and image will be associated with a unique DocumentID. And I can use CFCONTENT to deliver the images and documents from the folder, to the production web site.

2) But, as Azadi noted here:

https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_25426799.html

... I can use a query string to identify a specific file to stream to the production web site (using CFCONTENT) based on the file's unique ID:

<img href="image.cfm?imgid=XXXX">

or

<a href="pdf.cfm?docid=YYYYY" target="_blank">

So: does each image and document need a unique ID besides the unique database record DocumentID?

If so, should I create two more tables, one each to contain information about documents and images? (docID, DocumentFileName, PathtoDocument, etc)

Does this question make sense and can I articulate it better?

Thank you for any advice.

Eric B
cep_document_edit.cfm:

<!---
Name:        cep_document_edit.cfm
Author:     Eric Bourland, following Ben Forta code in ColdFusion 8 web application construction kit, assistance from _agx_ and maestropsm
Description: CEP add and update documents form
Created:     2/24/2010
--->


<!--- Check that DocumentID has been provided --->
<!--- If yes, do method edit; else, do method add --->
<cfset EditMode=IsDefined("URL.DocumentID")>

<!--- If edit mode, then get row to edit --->
<cfif EditMode>

 <!--- Get the document record --->
 <cfinvoke component="cep_documents"
           method="get"
           documentid="#URL.DocumentID#"
           returnvariable="GetDocumentRecord">

 <!--- Save to variables --->
 <cfset DocumentTitle=Trim(GetDocumentRecord.DocumentTitle)>
 <cfset DocumentType=Trim(GetDocumentRecord.DocumentType)>
 <cfset DocumentAuthor=Trim(GetDocumentRecord.DocumentAuthor)>
 <cfset DocumentAbstract=Trim(GetDocumentRecord.DocumentAbstract)>
 <cfset DocumentPublicationDate=DateFormat(GetDocumentRecord.DocumentPublicationDate, "MM/DD/YYYY")>

 
 <!--- Form text --->
 <cfset FormTitle="Update a Document">
 <cfset ButtonText="Update">
 
<cfelse>

 <!--- Set default values for variables, so that variables are defined --->
 <cfset DocumentTitle="" />
 <cfset DocumentType="" />
 <cfset DocumentAuthor="" />
 <cfset DocumentAbstract="" />
 <cfset DocumentPublicationDate="" />
 <cfset SSMA_TimeStamp="" />
 <cfset DateRecordModified="" />

 

 <!--- Form text --->
 <cfset FormTitle="Add a Document">
 <cfset ButtonText="Add Document">

</cfif>


<!--- Page header --->
<cfinclude template="cep_header.cfm" />


<!--- form begins here--->

<!--- Add/update Document form --->
<cfform action="cep_document_process.cfm">

<cfif EditMode>
 <!--- Embed primary key as a hidden field --->
 <cfoutput>
 <input type="hidden" name="documentID" value="#GetDocumentRecord.documentID#" />
 </cfoutput>
</cfif>

<table align="center" bgcolor="white">
 <tr>
  <th colspan="2">
   <cfoutput>
<h2>#FormTitle#</h2>
   </cfoutput>
  </th>
 </tr>
 <tr>
  <td>
   <p><strong>Document Title</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentTitle"
            value="#DocumentTitle#"
            message="Document title is required!"
            required="Yes"
            validateAt="onSubmit,onServer"
            size="50"
            maxlength="100" />
  </td>
 </tr>
 <tr>
  <td>
   <p><strong>Document Type</strong></p>
  </td>
  <td>
   <select name="DocumentType" value="#DocumentType#" message="Please choose Document Type from the Select Menu" required="Yes" validateAt="onSubmit,onServer" >            <option value="Report">Report</option>
            <option value="Press Release">Press Release</option>
            <option value="Article">Article</option>
            <option value="Summary">Summary</option>
            <option value="Letter">Letter</option>
            <option value="Audio Transcript">Audio Transcript</option>
            <option value="Text Transcript">Text Transcript</option>
   </select>
            
          
  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentAuthor"
            value="#DocumentAuthor#"
            message="Enter Document Author Name"
            required="yes"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>



 <tr>
  <td>
   <p><strong>Document Abstract</strong></p>
  </td>
  <td>
   <cfoutput>
   <textarea name="DocumentAbstract" cols="40" rows="5" wrap="virtual">#DocumentAbstract#</textarea>
   </cfoutput>
  </td>
 </tr>
 
 
 <tr>
  <td>
   <p><strong>Publication Date</strong></p>
  </td>
  <td>
   <cfinput type="Text"
            name="DocumentPublicationDate"
            value="#DocumentPublicationDate#"
            message="Publication Date must be a valid date!"
            required="no"
            validate="date"
            validateAt="onSubmit,onServer"
            size="10"
            maxlength="10" />
  </td>
 </tr>




 <tr>
  <td colspan="2" align="center">
   <cfoutput>
   <input type="submit" value="#ButtonText#" />
   </cfoutput>
  </td>
   </tr>
</table>

</cfform>

<!--- Page footer --->
<cfinclude template="cep_footer.cfm" />



cep_documents.cfc:

<!---
Name:        cep_documents.cfc
Author:      Eric Bourland, following Ben Forta code in ColdFusion 8 web application construction kit, assistance from _agx_ and maestropsm
Description: CEP add and update documents CF component
Created:     2/24/2010
--->

<cfcomponent hint="CEP documents database access component">

 <!--- Set the datasource --->
 <cfset ds="ebwebwork">


 <!--- Get document list --->
 <cffunction name="list"
             returntype="query"
             hint="List all CEP Documents">

 <!--- in this query let's select every column in the table so that the variables can be available to the list of documents on http://ebwebwork.com/cep/admin/ --->

  <cfquery datasource="#ds#"
           name="GetDocumentList">
  SELECT DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified
  FROM tbl_CEP_Documents
  ORDER BY DocumentID ASC
  </cfquery>
  <cfreturn GetDocumentList> 
 <!--- give query name to cfreturn tag, above; if query is named GetDocumentList, then so is cfreturn --->
  </cffunction>


 <!--- Get document details --->
 <cffunction name="get"
             returntype="query"
             hint="Get document details">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#"
           name="GetDocumentDetails">
  SELECT DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentPublicationDate, SSMA_TimeStamp, DateRecordModified
  FROM tbl_CEP_Documents
  WHERE DocumentID=#ARGUMENTS.DocumentID#
  </cfquery>
  <cfreturn GetDocumentDetails>
  
   <!--- give query name to cfreturn tag, above --->
  
 </cffunction>


 <!--- Add a document --->
 <cffunction name="add"
             returntype="boolean"
             hint="Add a document">

  <!--- Method arguments --->
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="Document Title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="Document Publication Date">


  <!--- Insert document --->
  <cfquery datasource="#ds#">
  INSERT INTO tbl_CEP_Documents(DocumentTitle,
                    DocumentType,
                    DocumentAuthor,
                    DocumentAbstract,
                    DocumentPublicationDate,
                    SSMA_TimeStamp)
  VALUES(
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentTitle)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
  <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
  CURRENT_TIMESTAMP
         )         
  </cfquery>
  <cfreturn true>

 </cffunction>

 
 <!--- Update a document --->
 <cffunction name="update"
             returntype="boolean"
             hint="Update a document">
  <!--- Method arguments --->
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">
  <cfargument name="DocumentTitle"
              type="string"
              required="yes"
              hint="document title">
  <cfargument name="DocumentType"
              type="string"
              required="yes"
              hint="Document Type">
  <cfargument name="DocumentAuthor"
              type="string"
              required="yes"
              hint="Document Author">
  <cfargument name="DocumentAbstract"
              type="string"
              required="yes"
              hint="Document Abstract">
  <cfargument name="DocumentPublicationDate"
              type="date"
              required="yes"
              hint="This is the date, entered manually by the user, that the publication was published by CEP">
  <cfargument name="DateRecordModified"
              type="date"
              required="yes"
              hint="Date Record Modifed">

  <!--- Update document --->
 <cfquery datasource="#ds#">
  UPDATE tbl_CEP_Documents
  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar"  value="#Trim(ARGUMENTS.DocumentTitle)#">,
		DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentType)#">,
		DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAuthor)#">,
	    DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.DocumentAbstract)#">,
	    DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.DocumentPublicationDate)#">,
	    DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer"  value="#ARGUMENTS.DocumentID#">
  </cfquery>
  <cfreturn true>

 </cffunction>
 
 
 <!--- Delete a document --->
 <cffunction name="delete"
             returntype="boolean"
             hint="Delete a document">
  <cfargument name="DocumentID"
              type="numeric"
              required="yes"
              hint="document ID">

  <cfquery datasource="#ds#">
  DELETE FROM tbl_CEP_Documents
  WHERE DocumentID=#ARGUMENTS.DocumentID#
  </cfquery>
  <cfreturn true>
  
 </cffunction>

</cfcomponent>

Open in new window

ASKER CERTIFIED SOLUTION
gdemaria

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 298 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 298 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros