Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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>
ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemariaFlag of United States of America image

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

Commented:
This problem has been solved!
Unlock 1 Answer and 298 Comments.
See Answers