We help IT Professionals succeed at work.

document upload application quit working after moving to Lucee CFML

218 Views
Last Modified: 2017-05-04
Lucee version 5.1.3.18
Ubuntu Linux 16.04.1
MS SQL Server 2014

Hello, friends,

I have been struggling with this task for several days and it's time I asked for some help.

In brief: I moved my CFML application from Adobe ColdFusion to Lucee, and now the application no longer uploads documents to my web server.

Details:

This application, created in 2010 with considerable and very patient help from gdemaria and _agx_, is just a form that allows the user to create a database record (a document) and upload one or more files to associate with the record.

Last week I moved this code to the Lucee CFML processor. Lucee processes the form partially. It uploads the document thumbnail image -- around line 48 -- right at the start of the doSave action. Then, the form goes directly to the cflocation URL, without doing anything else. No database records are created when the form submits; no files are uploaded.

There's no error message. Lucee is finicky about the code it processes, so I am thinking there is something here that Lucee objects to. I am just not sure what it is.

I would like to DUMP out the variables to see if Lucee is stumbling on any variables -- but I am not sure where to place the CFDUMP. Or what the CFDUMP should test for.

For example:

<cfdump var="#DocumentID#">
<cfabort>
   
But I am not sure that I want or need to test for variable DocumentID.

The application code is below; I have tidied it as much as I can before presenting it here. Thank you as always for advice.

Eric

<!---- Application name: EditDocuments.cfm
Author:      Eric B, following Ben Forta code in ColdFusion 8 web application construction kit; gdemaria, _agx_
Description: insert and update a document on a web site; a document comprises files (PDF, DOCX, and so on), meta data, and an image thumbnail file
Created:     2/24/2010 - 10/5/2010
Revised: May 2017 / Eric B / for use with the Lucee CFML Processor ---->


 <!--- Set default value for FileID in scope FORM --->
<cfparam name="form.FileID" default="">

 <!--- Set default value for NumberFiles in scope FORM --->
<cfparam name="form.NumberFiles" default="">

 <!--- Set default value for SelectDocumentTopics in scope FORM --->
<cfparam name="form.SelectDocumentTopics" default="0">


 <!--- Set default value for DocumentID in scope URL --->
<cfparam name="url.DocumentID" default="">

 <!--- Define DocumentID in scope FORM, then set form.DocumentID equal to the DocumentID passed in the URL: for use later in the application --->
<cfparam name="form.DocumentID" default="#url.DocumentID#">

<!--- default isPressRelease is 0 (false) --->
<cfparam name="form.isPressRelease" default="0">

<!--- default filelinksortorder is 0 --->
<cfparam name="form.FileLinkTextSortOrder" default="0">

<!--- default slideshowFeature is 0 --->
<cfparam name="form.slideshowFeature" default="0">

  
 <!---- begin CFTRY; catch any errors, whether you throw them or the database does; and to test that files were uploaded successfully, or not  ---->
 <cftry>  
 

<!---- populate this with an error message ---->
<cfset variables.error = ""> 

<!--- begin form.doSave --->

<!--- when the user clicks the Save Button, these events can occur: Add a document; Update a document; Delete a Document; Delete a file that is associated with a document --->

<cfif IsDefined("FORM.doSave")>

<!--- BEGIN: if form field DocumentImage contains a file, then upload the file --- this function updates column DocumentImage in CEPDocs--->
            <cfif IsDefined('form.DocumentImage') AND form.DocumentImage neq "">      
                   
      <!--- BEGIN: upload an image file to column DocumentImage using cffile --->       
          <!--- this is a document thumbnail image --->       
            <cffile action="upload" filefield="DocumentImage" destination="/home/cep-dc/upload" nameconflict="makeunique">
            <cfset uploadedImage  = #cffile.serverFile#>
                  
                  
                            <!--- list allowed file extensions and compare them to cffile.serverFileExt --->       
                  <cfif listFindNoCase("jpg,png,gif", cffile.serverFileExt) eq 0>
                            <cfthrow message="Your thumbnail file did not upload. You may upload only permitted file types.">
                  </cfif>
                  
            <cfelse>
            
         <!--- SET uploadedImage to ""  --->       			
			<cfset uploadedImage = "">   
            
            
<cfif structKeyExists(FORM, "deleteDocumentImage")>

<!--- query to see that DocumentImage file is checked to be deleted --->
<cfquery name="getDocumentImageToDelete" datasource="cep-dc">
    SELECT DocumentImage
    FROM CEPDocs
    WHERE DocumentID = <cfqueryparam value="#form.deleteDocumentImage#" cfsqltype="cf_sql_integer">
</cfquery>


<!--- if DocumentImage file is checked to be deleted, then do this: --->

      <cfif getDocumentImageToDelete.recordCount>
              <!---- If the db record exists, delete it .... ---->
              <cfquery name="deleteDocumentImage" datasource="cep-dc">
              UPDATE CEPDocs
			  SET DocumentImage = NULL
			  WHERE DocumentID = <cfqueryparam value="#form.deleteDocumentImage#" cfsqltype="cf_sql_integer">
             </cfquery>

              <!---- Only delete the physical file if it exists (i.e. avoid errors) .... ---->
             <cfset pathToFile = "/home/cep-dc/upload/&findDocument.DocumentImage">
             <cfif FileExists(pathToFile)>
                  <cffile action="delete" file="#pathToFile#">
             </cfif>
      </cfif>
</cfif>


            </cfif>
            <!----- END form.DocumentImage neq "" ----->
                  


<!--- make sure that documentTitle and documentType are entered --->  
    
	<cfif len(form.DocumentTitle) eq 0>
			  <cfthrow message="Document Title is required">
	</cfif>
    
    <cfif len(form.DocumentType) eq 0>
			  <cfthrow message="Document Type is required">
	</cfif>


 <!--- in this query select NOTHING from table CEPDocs, and simply check if DocumentTitle exists --->
 
 <cfquery datasource="cep-dc" name="CheckDocumentTitle">
  SELECT 'Nothing' FROM CEPDocs
  WHERE DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.DocumentTitle#">
  AND DocumentID <> <cfqueryparam cfsqltype="cf_sql_integer" value="#val(Form.DocumentID)#">
  </cfquery>
   
 
  
   <!--- if DocumentTitle exists, display error; refuse record insert --->
   
	<cfif CheckDocumentTitle.recordcount GT 0>
		   <cfthrow message="The Document Title is already taken; please enter another title">
	</cfif>
    
   <!--- begin statements to update, insert database records --->   
    
<cftransaction>  
 
    <!--- begin cfif val(form.DocumentID) --->   
    <cfif val(form.DocumentID)> 
     
    <!--- the action is UPDATE; a DocumentID Exists ---> 
        <cfquery name="UpdateDocument" datasource="cep-dc">  
                                  UPDATE CEPDocs  
                                  SET   DocumentTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,  
                                        DocumentType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,  
                                        DocumentAuthor = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,  
                                        DocumentAbstract = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,  
                                        DocumentKeyword = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentKeyword)#">,  
                    <cfif len(trim(uploadedImage))>  
                                        DocumentImage = <cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedImage#" null="#not len(uploadedImage)#">,  
                    </cfif>  
                                        DocumentPublicationDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">,
                                        slideshowFeature = <cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.slideshowFeature)#">,
                                        DateRecordModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">  
                                  WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.DocumentID)#">  
                        </cfquery>  
                         
   <cfelse> <!---- document does not exist, so insert record into database ---->  
        <cfquery name="InsertDocument" datasource="cep-dc" result="newDocument">  
         INSERT INTO CEPDocs  
                   (  
                   DocumentTitle,  
                   DocumentType,  
                   DocumentAuthor,  
                   DocumentAbstract,  
                   DocumentKeyword,  
                   DocumentImage,  
                   DocumentPublicationDate,
                   slideshowFeature, 
                   SSMA_TimeStamp  
                      )  
            VALUES(  
                          <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentTitle)#">,  
                          <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentType)#">,  
                          <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAuthor)#">,  
                          <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentAbstract)#">,  
                          <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.DocumentKeyword)#">,  
                          <cfqueryparam cfsqltype="cf_sql_varchar" value="#uploadedImage#" null="#not len(uploadedImage)#">,  
                          <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(form.DocumentPublicationDate)#">, 
                          <cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.slideshowFeature)#">, 
                          <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">  
                 )         
        </cfquery>  
                      
            <!--- use the result attribute value to set the form field value --->  
        <cfset form.DocumentID = newDocument.IDENTITYCOL>  
  </cfif>  
        
  <!---- Save Topics, Subtopics, and Sub-subtopics ----->  
  
  <!----- remove all topics/subtopics/sub-subtopics and add the selected ones below ---->  
        <cfquery name="UpdateDocumentDeleteTopics" datasource="cep-dc">  
           DELETE FROM DocHasTopic  
           WHERE DocumentID = <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">  
        </cfquery>  
          
         
      <!--- topics/subtopics/sub-subtopics are put into lists then assigned to variables: --->
      <!--- variables are allTopicID, allSubTopicID, allSubSubTopicID --->
      <!--- these variables are used later in queries to update document topics/subtopics/sub-subtopics  --->
<cfset allTopicID = "">  
<cfset allSubTopicID = "">  
<cfset allSubSubTopicID = "">

<!---
        Each set of IDs is delimited by a ":". The format is:    
                #TopicID#:#SubtopicID#:#SubsubtopicId#

        If listLen equals 3, it's a SUB-SubTopic (ie 1:20:5)
        If listLen equals 2, it's a SubTopic (ie 1:20)
        If listLen equals 1, it's a Topic (ie 1)
--->
<cfloop index="aSet" list="#form.selectDocumentTopics#">  
      <!--- this is a SUB-Subtopic --->
      <cfif listLen(aSet,":") eq 3>
            <cfset allSubSubTopicID =  listAppend(allSubSubTopicID, ListGetAt(aSet,3,":"))>
      <!--- this is a SubTopic --->
      <cfelseif listLen(aSet,":") eq 2>
            <cfset allSubTopicID =  listAppend(allSubTopicID, ListGetAt(aSet,2,":"))>
      <!--- this is a Topic --->
      <cfelseif listLen(aSet,":") eq 1>
            <cfset allTopicID =  listAppend(allTopicID, ListGetAt(aSet,1,":"))>
      </cfif>  
</cfloop> 






  <!---- Update topics ----->  
          
        <cfquery name="InsertDocTopics" datasource="cep-dc">   
        INSERT INTO DocHasTopic (   
            DocumentID   
          , DocumentTopicID  
        ) SELECT <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">   
               , tp.DocumentTopicID  
        FROM DocTopic tp  
         <cfif len(allTopicID)>  
          WHERE tp.DocumentTopicID  IN ( <cfqueryparam value="#allTopicID#" cfsqltype="cf_sql_integer" list="true">  )   
         <cfelse>  
          WHERE 1 = 2  
         </cfif>  
        </cfquery>  

  <!---- Update subtopics ----->  
          
        <cfquery name="InsertDocSubTopics" datasource="cep-dc">   
        INSERT INTO DocHasTopic (   
                   DocumentID  
                ,  DocumentTopicID   
                ,  DocumentSubTopicID   
        ) SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">   
                , tp.DocumentTopicID  
                , sub.DocumentSubtopicID   
          FROM DocTopic tp  
            LEFT JOIN DocSubtopic sub ON sub.DocumentTopicID = tp.DocumentTopicID  
        <cfif len(allSubTopicID)>  
          WHERE   sub.DocumentSubTopicID  IN ( <cfqueryparam value="#allSubTopicID#" cfsqltype="cf_sql_integer" list="true"> )   
        <cfelse>  
          WHERE 1 = 2  
         </cfif>  
       </cfquery>  
       
  <!---- Update sub-sub-topics ----->  
       
<cfquery name="InsertDocSubSubTopics" datasource="cep-dc">     
        INSERT INTO DocHasTopic (     
                   DocumentID 
                ,  DocumentTopicID    
                ,  DocumentSubTopicID  
                ,  DocumentSubSubTopicID      
        ) SELECT   <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer"> 
		        , sub.DocumentTopicID    
                , sub.DocumentSubtopicID  
                , subsub.DocumentSubSubtopicID     
          FROM DocSubtopic sub 
          INNER JOIN DocSubSubtopic subsub ON subsub.DocumentSubTopicID = sub.DocumentSubTopicID   
                    
        <cfif len(allSubSubTopicID)>    
          WHERE DocumentSubSubtopicID IN ( <cfqueryparam value="#allSubSubTopicID#" cfsqltype="cf_sql_integer" list="true"> )     
        <cfelse>    
          WHERE 1 = 2
         </cfif>    
       </cfquery> 


</cftransaction>
 
    
    
	   <!--- ============= BEGIN query to update columns FileLinkText, FileLinkTextSortOrder in CEPFiles ============= --->   
              <!--- this section must be placed within the Save Action --->  
 
        <cfloop index="kk" from="1" to="#val(form.numberFiles)#"> 
            <cfset form.updateFileLinkText = form['updateFileLinkText' & kk]>
            <cfset form.FileLinkTextSortOrder = form['FileLinkTextSortOrder' & kk]>
            <cfset form.FileID = form['FileID' & kk]>
        
             <cfquery name="query_updateFileLinkText" datasource="cep-dc">
                UPDATE CEPFiles
                SET FileLinkText = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.updateFileLinkText)#">
                ,FileLinkTextSortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.FileLinkTextSortOrder)#">
                WHERE FileID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.FileID)#">
             </cfquery>            
         
        </cfloop>
     
        <!--- ============= END query to update column FileLinkText in CEPFiles ============= --->   
    

    
<!--- remember cfif IsDefined("FORM.doSave") is still defined -- remember to close the cfif later --->
    
  
				<!--- test to see whether or not a file is being uploaded -- does form field FileName contain a file? --->         
  
		    	<!--- BEGIN: if form field FileName contains a file, then upload the file and the file's metadata --- this function updates the CEPFiles--->
      		<cfif form.fileName is not "">
     
              
			    <!--- BEGIN: upload a file using cffile --->       
     			<!--- cffile upload --->       
   			      <cffile action="upload" filefield="FileName" destination="/home/cep-dc/upload" nameconflict="overwrite">

			<cfif listFindNoCase("doc,docx,jpg,png,gif,pdf,ppt,pptx,xls,xlsx,txt", cffile.serverFileExt) eq 0>
				    <cfthrow message="Your file did not upload. You may upload only permitted file types.">
			</cfif>
            

			<!---- query to insert new record into CEPFiles, with a file  (DOC, JPG, PDF, etc.) to upload ---->
			<cfquery name="InsertFile" datasource="cep-dc">
			 INSERT INTO CEPFiles
     			(
                DocumentID,
                FileName,
                FileLinkText,
				FileExtension,
				FileType,
				FileSize,
                isDeleted,
                isPressRelease,
                FileLinkTextSortOrder
                )
		     VALUES(
        		  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.DocumentID)#">
				  ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#cffile.clientFile#">
                  ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.FileLinkText)#">
				  ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#cffile.clientFileExt#">
				  ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#cffile.contentType#">
				  ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#cffile.fileSize#">
                  ,0
                  ,0
                  ,<cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.FileLinkTextSortOrder)#">
		           )         
				</cfquery>  
  
		   <!--- END query to insert new record into CEPFiles --->  
 
 
     
              <!--- DELETE A FILE --->
      <!--- this query must be placed within the Save Action --->
 
    
    	   <!--- begin CFELSEIF isdefined(form.deleteFileID) --->
		 <cfelseif isDefined("form.deleteFileID")>        
   
	<!---- query to set a file to isDeleted = 1 ---->
    	   <cfquery name="deleteFile" datasource="cep-dc">
       			UPDATE CEPFiles
           		SET IsDeleted = 1
        		WHERE FileID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#form.deleteFileID#" list="yes">)
      		</cfquery>


    <!--- SET OR UNSET A FILE TO isPressRelease --->
      <!--- this query must be placed within the Save Action --->
      
      <!--- Use #form.documentID# to identify all files associated with the current document --->
 
    	   <!--- begin CFELSEIF isDefined(form.isPressRelease) --->
           <!--- use #form.isPressRelease# to exclude checked files --->
		 
		 
		 <cfelseif isDefined("form.isPressRelease")>
   
   <cftransaction>
   <!---- query to handle unchecked files (set isPressRelease = 0---->
  <cfquery name="uncheckedFiles" datasource="cep-dc">
            UPDATE CEPFiles
            SET isPressRelease = 0
            WHERE documentID = <cfqueryparam value="#form.documentID#" cfsqltype="cf_sql_integer"> 
            <cfif listLen(form.isPressRelease)>
            AND FileID NOT IN 
                        (
                              <cfqueryparam value="#form.isPressRelease#" list="true" cfsqltype="cf_sql_integer">
                        )
            </cfif>
      </cfquery>



	<!---- query to handle checked files (set isPressRelease = 1) ---->

      <cfif listLen(form.isPressRelease)>
            <cfquery name="checkedFiles" datasource="cep-dc">
            UPDATE CEPFiles
            SET isPressRelease = 1
            WHERE FileID IN 
                        (
                              <cfqueryparam value="#form.isPressRelease#" list="true" cfsqltype="cf_sql_integer">
                        )
            </cfquery>
      </cfif>
      
</cftransaction>


            </cfif>
<!----- END form.fileName is not "" test to see if file is being uploaded ---->



	<!--- if insert record succeeded, and / or file upload succeeded, and / or FileLinkText were updated, then add token --->
       <!--- done? relocate --->

<cfif val(url.DocumentID)>
<cflocation url="/admin/EditDocuments.cfm?DocumentID=#val(url.DocumentID)#" addtoken="yes">

<cfelse>                     
<cflocation url="/admin/index.cfm" addtoken="no">
				     
</cfif>
        
                    
    <!--- END: Save action --->
    

  
        <!--- BEGIN: Delete action --->


			   <!--- delete a database record --->
					   <!--- begin CFELSEIF isdefined(form.doDelete) --->
					        <cfelseif isDefined("form.doDelete")>        
					        <!---- query to delete record ---->
                            
                            <cftransaction>
					       	<cfquery name="DeleteDocument" datasource="cep-dc">
					         DELETE FROM CEPDocs
					         WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.DocumentID)#">
					        </cfquery>
                                                        
                            <cfquery name="UpdateDocumentDeleteTopics" datasource="cep-dc">
							   DELETE FROM DocHasTopic
							   WHERE DocumentID = <cfqueryparam value="#form.DocumentID#" cfsqltype="cf_sql_integer">
							</cfquery>
                            </cftransaction>
              
 					        <!---- after delete, go to index page ---->
						 <cflocation url="index.cfm" addtoken="no">

	    <!--- END: Delete action --->
        
  <!--- END form.doSave --->
</cfif>

       <!--- this CFCATCH will trap errors -- the ones you threw or just regular database issues --->
<cfcatch type="Any">
     <cfset variables.error = cfcatch.message>
</cfcatch>


		  <!--- END CFTRY --->  
			</cftry>



<!--- fetch the data from the database only when there are no errors.
      if an error exists, then let the form variables pass back into the form to display ---->
 
  <cfif len(variables.error) eq 0>
    
			  <!--- get data from table CEPDocs and convert the data into form variables --->
			  <cfquery name="getDocumentDetails" datasource="cep-dc">
				    select * from CEPDocs where DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.DocumentID)#">
			  </cfquery>

  			<cfloop index="aCol" list="#getDocumentDetails.columnList#">
			       <cfset "form.#aCol#" = getDocumentDetails[aCol][getDocumentDetails.currentRow]>
			  </cfloop>
    
	</cfif>




<!--- BEGIN HTML / CSS PAGE HEADER --->
<cfinclude template="/admin/admin_header.cfm">

<cfinclude template="/admin/adminNav.cfm">



<!--- if there an error, display error in human readable form --->

<cfif len(variables.error)> 
			 <cfoutput>
			 <div style="border: 1px solid red; padding: 10px; margin:20px; width:400px;">#variables.error#</div>
			 </cfoutput>
</cfif>


				<!----- if record already exists (it will have an ID) then update it; otherwise, add new record... ----->
				<cfif val(form.documentID)>
					  <cfset FormTitle="Edit Document">
					  <cfset ButtonText="Update This Document">
  					  <cfset FileLinkTextButton="Update File Link Text">
				<cfelse>
						<cfset FormTitle="Add a Document">
						<cfset ButtonText="Add Document">

				</cfif>
                
      
<cfparam name="url.cftoken" default="">

<cfif len(url.cftoken)> 

<div class="center"><button class="medium green"><i class="fa fa-check-square"></i> Update succeeded. Good work.</button></div>

</cfif>
                
  
<!--- Add or Update Document Form begins here --->
<form method="post" enctype="multipart/form-data" onSubmit="return require_CategoryAndSelection()" name="CEP_Topics_SubTopics_Select">



  <cfoutput>

      <h2>#FormTitle#</h2>

  </cfoutput>
  
  

 
<table>
<thead>
<cfif val(form.documentID)>
 <tr>
  <td>
<p><strong>Document ID: <cfoutput>#url.DocumentID#</cfoutput></strong></p>
  </td>
 </tr>
 <cfelse>
 </cfif>
</thead>

<tbody>

 <tr>
  <td>
   <p><strong>Document Title</strong><br />

<cfoutput>   <input type="Text"
            name="DocumentTitle"
            value="#form.DocumentTitle#"
            size="100"
            maxlength="512" tabindex="1" required /></cfoutput></p>
  </td>
 </tr>

 <tr>
  <td>
  
 
   <p><strong>Document Type</strong></p> 
  
<!--- get DocumentTypeTitle from LookupDocType --->
<cfquery datasource="cep-dc" name="GetCEPDocumentType">
        SELECT DocumentTypeTitle
        FROM LookupDocType
        ORDER BY DocumentTypeTitle
</cfquery>

  
  <!--- query getSelectedDocType requests DocumentID value already assigned to the current record --->
  <cfquery datasource="cep-dc" name="getSelectedDocType">
     SELECT DocumentID, DocumentType
     FROM CEPDocs
     WHERE DocumentID = <cfqueryparam value="#val(form.DocumentID)#" cfsqltype="cf_sql_integer">
</cfquery>  
            
 <cfset selectedDocType = valueList(getSelectedDocType.DocumentType)>              

            
    <select size="8" name="DocumentType" tabindex="2">
      <option value=""> Select a Document Type: </option>
      <cfoutput query="GetCEPDocumentType">
            <option value="#GetCEPDocumentType.DocumentTypeTitle#" <cfif listFindNoCase(selectedDocType, GetCEPDocumentType.DocumentTypeTitle)>selected</cfif>> 
           #GetCEPDocumentType.DocumentTypeTitle#
        </option>
     </cfoutput>
</select> 
  

  </td>
 </tr>


 <tr>
  <td>
   <p><strong>Document Author</strong><br />

<cfoutput>   <input type="Text"
            name="DocumentAuthor"
            value="#form.DocumentAuthor#"
            size="100"
            maxlength="512" tabindex="3" required /></cfoutput></p>
  </td>
 </tr>
 
 
<!--- query getTopics requests values from columns in DocTopic and DocSubtopic, for use later in the insert_update.cfm template --->

<cfquery datasource="cep-dc" name="getTopics">
           SELECT tp.DocumentTopicID
                  , tp.DocumentTopic
                  , sub.DocumentSubTopicID
                  , sub.DocumentSubTopicTitle
                  , subsub.DocumentSubSubTopicID
                  , subsub.DocumentSubSubTopicTitle
           FROM DocTopic tp
           LEFT JOIN DocSubtopic sub
           ON sub.DocumentTopicID = tp.DocumentTopicID
           LEFT JOIN DocSubSubtopic subsub
           ON subsub.DocumentSubTopicID = sub.DocumentSubTopicID
           ORDER BY tp.DocumentTopic, sub.DocumentSubTopicTitle
               ,subsub.DocumentSubSubTopicTitle
</cfquery>


  <tr>
  <td>
    <p><strong>Assign this Document to CEP Topics, Subtopics, and Sub-Sub-Topics</strong> <span class="tooltip question" title="Assign this document to Topics, Subtopics, and Sub-Sub-Topics.">?</span></p>
    
    
      <p>Choose at least one topic, subtopic, or sub-sub-topic. To select more than one item, hold the Ctrl or the Shift key (on your keyboard) and select topics with the mouse cursor.</p>


   
<!--- this query requests existing topics, subtopics, sub-subtopics for the current documentID --->
       <cfquery datasource="cep-dc" name="GetSelectedTopics">
              SELECT DocumentTopicID
              , DocumentSubTopicID
              , DocumentSubSubTopicID
              , cast(DocumentTopicID AS varchar) + ':' + isNull(cast(DocumentSubTopicID AS varchar),'') + ':' + isNull(cast(DocumentSubSubTopicID AS varchar),'') AS JoinedID
              FROM DocHasTopic
              WHERE DocumentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.documentID)#">
       </cfquery>
     
<!--- convert the DocumentTopicID, DocumentSubTopicID, DocumentSubSubTopicID values to a list --->
      <cfset form.SelectDocumentTopics = ValueList(GetSelectedTopics.JoinedID)>


<!--- This select menu lists Topics, Subtopics, and Sub-Sub-Topics --->

<!--- also, if Topics, Subtopics, and Sub-Sub-Topics are already selected for form.documentID --->

<!---  ... then display them in select menu using query GetSelectedTopics --->

<!--- query getTopics requests Topics, Subtopics, and Sub-Sub-Topics --->


<select name="SelectDocumentTopics" size="15" tabindex="4" multiple> 
 <cfoutput query="getTopics" group="DocumentTopic"> 
   <option value="#getTopics.DocumentTopicID#"  <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#::")>selected='selected' </cfif>>#getTopics.DocumentTopic#</option>

    <cfoutput group="DocumentSubTopicTitle">   <!---- group loop showing subtopics ----> 
       <cfif val(getTopics.DocumentSubTopicID)> 
         <option value="#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#:"
         <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#:")> selected='selected' </cfif>>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#getTopics.DocumentSubTopicTitle# </option>
       </cfif> 
       
       <cfoutput> <!--- group loop showing sub-sub topics ---->
       <cfif val(getTopics.DocumentSubSubTopicID)> 
       <option value="#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#:#getTopics.DocumentSubSubTopicID#"
            <cfif listFind(form.SelectDocumentTopics,"#getTopics.DocumentTopicID#:#getTopics.DocumentSubTopicID#:#getTopics.DocumentSubSubTopicID#")> selected='selected' </cfif>>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#getTopics.DocumentSubSubTopicTitle#
       </option>
       </cfif> 
       </cfoutput>
    </cfoutput> 
</cfoutput> 
</select>


  </td>
 </tr>
 
 <tr>
  <td>
   <p><strong>Document Abstract</strong><br />
   <cfoutput>
   <textarea name="DocumentAbstract" wrap="virtual" style ="width:600px; height:250px;">#form.DocumentAbstract#</textarea>
   </cfoutput></p>
  </td>
 </tr>
 
  <tr>
  <td><p><span class="green"><i class="fa fa-check-square fa-2x"></i></span> Hint: Enter as many relevant keywords as possible for this document. Keywords help others find this document in document searches. Enter keywords <em>without</em> punctuation, in a simple list, as so: common core stare standards exit examination public school facts</p>
  
   <p><strong>Document Keywords</strong> <span class="tooltip question" title="Enter keywords as a simple list, without punctuation.">?</span><br />

<cfoutput>   <input type="Text"
            name="DocumentKeyword"
            value="#form.DocumentKeyword#"
            size="100" /></cfoutput></p>
  </td>
 </tr>
 
 
  <!--- begin DocumentImage table row ---> 

  <tr>
  <td>

      

 
 
     <div class="float-right-img width300px"><img src="/img/Listen-to-Us-Teacher-Views-and-Voices.jpg" alt="Sample Document Cover Image, 300px wide" class="border1" /><br />Here is an example of a document cover image. Set image width to 300 pixels. The cover image will automatically display properly on publications pages or on the front page slide show, as needed.</div>   
    
    <div class="float-left-img width425px"><span class="green"><i class="fa fa-star fa-2x"></i></span> You can include a thumbnail image of the document cover. Doing so will help users locate the document on your web page. If you include a cover image, use Adobe Photoshop, Adobe Photo Elements, or another image editor to make the image dimensions <strong>300px wide</strong>. Use JPG, PNG, or GIF format.</div>
    

  
   <p><strong>Document Thumbnail Image</strong> <span class="tooltip question" title="Browse local computer for thumbnail image.">?</span>

<input type="file" size="50" accept="image/gif,image/jpeg,image/x-png" name="DocumentImage" maxlength="255" /></p>

 
    <!--- query to get columns and values from CEPFiles, for use below --->
<cfquery name="getDocumentImage" datasource="cep-dc">
    SELECT DocumentImage
    FROM CEPDocs
    WHERE DocumentID=<cfqueryparam value="#val(form.DocumentID)#" cfsqltype="cf_sql_integer"> 
</cfquery>


<!--- begin table to display the file called by query getDocumentImage ---> 

<cfif getDocumentImage.recordCount>
   <table class="striped">
   <thead>
     <tr>
      <td><strong>Thumbnail image file associated with this CEP document:</strong></td>
      <td><strong>Mark for Deletion</strong> <span class="tooltip question" title="If you want this file to no longer associate with this document, you can mark the file for deletion; the file will be deleted from the server.">?</span></td>
     </tr>
     </thead>
	 
      <tbody>
     <tr>

<cfoutput>
        <!---  get list of file names --->  
       <td>#getDocumentImage.DocumentImage#</td>
       

         <!--- Mark For Deletion checkbox --->      
       <td><input type="checkbox" name="deleteDocumentImage" value="#val(form.DocumentID)#" class="border0" /></td>

     </cfoutput>
    
</tr>
</tbody> 
   </table>

  <cfelse>
   <p><strong>At present, no thumbnail image file is attached to this document.</strong></p>
  </cfif>
<!--- end table to display the file called by query getDocumentImage ---> 



  </td>
 </tr>
 <!--- end DocumentImage table row; the table markup gets confusing; be careful ---> 



 
 <tr>
  <td>
   <p><strong>Publication Date (use form MM/DD/YYYY; please click the calendar icon, below)</strong><br />
<cfoutput>   <input name="DocumentPublicationDate"
            value="#DateFormat(DocumentPublicationDate, "mm/dd/yyyy")#"
            size="10"
            maxlength="10"
            type="text" mask="MM/DD/YYYY" /></cfoutput></p>
  </td>
 </tr>
 
 <tr>
 <td>
   <label for="slideshowFeature"><h3>Feature this document in the slideshow on the front page?</h3></label>
  
<span class="blue"><i class="fa fa-lightbulb-o fa-2x"></i></span> <strong>Should this document appear in the front page slideshow?</strong>

<cfoutput><input type="checkbox" name="slideshowFeature" value="1" <CFIF val(getDocumentDetails.slideshowFeature) IS '1'>checked</CFIF> /></cfoutput>


<span class="form_hint">Should this document appear in the front page slideshow?</span> 
               
<p>&nbsp;</p>
 </td>
 </tr>
 
 </tbody>
 </table>

  <!--- END TABLE ADMIN; DISPLAY DOCUMENT DETAILS --->
  



   <!--- display the files currently associated with the document --->

<cfif val(form.documentID)>
<p>Below, please see the files currently associated with Document ID <strong><cfoutput>#url.DocumentID#</cfoutput></strong>. If there are no files associated with the document, then no files will appear below. You have the option to upload a file (PDF, DOC, image file), which will associate the file with this document.</p>
<cfelse>
<p>This is a new document. You have the option to upload a file (PDF, DOC, image file), which will associate the file with this document. Large files will take some time to upload; please be patient.</p>
</cfif>


   <!--- query to get columns and values from CEPFiles, for use below --->
<cfquery name="getFiles" datasource="cep-dc">
    SELECT FileID,DocumentID,FileName,FileLinkText,FileExtension,FileType,FileSize,isPressRelease,isDeleted,FileLinkTextSortOrder
    FROM CEPFiles
    WHERE DocumentID = <cfqueryparam value="#val(form.DocumentID)#" cfsqltype="cf_sql_integer"> 
    AND isDeleted = 0
  </cfquery>
  

  <cfif getFiles.recordCount>
   <table class="width600px striped">
   <thead>
     <tr>
      <td><strong>File ID</strong></td>
      <td><strong>Files associated with this CEP document:</strong></td>
      <td><strong>File Link Text</strong> <span class="tooltip question" title="What link text should the user click on to open this file?">?</span></td>
      <td><strong>Mark for Deletion</strong> <span class="tooltip question" title="If you want this file to no longer associate with this document, you can mark the file for deletion; the file will be deleted from the server.">?</span></td>
          <td><strong>Press Release</strong> <span class="tooltip question" title="Mark this file as a press release.">?</span></td>
          <td><strong>File Link Text Sort Order</strong> <span class="tooltip question" title="Set sort order for this file; enter a single- or two-digit numeral only.">?</span></td>
         
     </tr>
     </thead>
 
 <tbody>    
    <!--- loop through files called by query getFiles ---> 
     <cfloop query="getFiles">
     
     
	  
	 <cfoutput>
     <input type="hidden" name="numberFiles" value="#getFiles.recordCount#">
          

      
     <tr>

        <!---  get list of file IDs --->  
       <td>#getFiles.FileID#</td>



        <!---  get list of file names --->  
       <td>#getFiles.FileName#</td>
       

        <!--- input field to update file link text --->   
        
        <cfset CTR = getFiles.currentRow>
          
       <td><input type="Text"
            name="updateFileLinkText#CTR#"
            value="#getFiles.FileLinkText#"
            size="50"
            maxlength="250" />
            <input type="hidden" name="FileID#CTR#" value="#getFiles.FileID#" />
       </td>
               
      
         <!--- Mark For Deletion checkbox --->      
       <td><input type="checkbox" name="deleteFileID" value="#getFiles.FileID#" class="border0" /></td>

     
       <td>
       
   <!--- isPressRelease checkbox --->  
   
   <!--- Give isPressRelease checkboxes name "isPressRelease". Give value #FileID# to checkboxes: --->  

    <input type="checkbox" name="isPressRelease" value="#FileID#" class="border0"
                         <cfif getFiles.isPressRelease>checked</cfif> />

      
</td>


       <td>
       
   <!--- Set Sort Order for files --->  
   

<input type="text" name="FileLinkTextSortOrder#CTR#" value="#getFiles.FileLinkTextSortOrder#"
   	size="1" maxlength="2" />

      
</td>

</tr>

   
     </cfoutput>

<!--- close loop through files called by query getFiles ---> 
     </cfloop>
     </tbody>
   </table>

  <cfelse>
   <p><strong>At the moment, no files are attached to this document.</strong></p>
  </cfif>

<p><strong>Upload a <em>new</em> file: PDF, TEXT, DOC, DOCX, XLS, XLXS, PPT, PPTX, or image files only:</strong> <span class="tooltip question" title="Browse your local computer disk to find a file to upload.">?</span>

  <!--- input field for file upload --->
  

<input type="file" size="50" accept="application/vnd.openxmlformats-officedocument.presentationml.presentation,application/vnd.ms-powerpoint,application/vnd.openxmlformats-officedocument.wordprocessingml.document,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/pdf,image/gif,image/jpeg,image/x-png" name="FileName" maxlength="255" />
			</p>
            
        <!--- input field for file link text --->      
   <p><strong>Enter File Link Text:</strong> <span class="tooltip question" title="What text should the user click on to open this file?">?</span><br />

<cfoutput>   <input type="Text"
            name="FileLinkText"
            value=""
            size="50"
            maxlength="250" /></cfoutput></p>
  
   
   <!--- submit form to ColdFusion for processing; this is the DoSave function, which will add or edit a record --->


<div class="center">
  <cfoutput>
   
  <button name="doSave" type="submit" class="blue">#ButtonText#</button>
    
  </cfoutput>
  </div>

<cfif val(form.documentID)>

<hr />


  <p><span class="red"><i class="fa fa-warning fa-4x"></i></span> The button below deletes this document, its database record, and its associated files. Use deliberately and with caution.</p>



<!--- submit form to ColdFusion for processing; this is the DoDelete function, which deletes a Document record --->

<div class="center">
<button name="doDelete" type="submit" class="small red" onClick="return confirm('Are you sure you wish to delete this document? After you delete it it is gone forever.')"><i class="fa fa-warning"></i> Delete This Document</button>
</div>

<cfelse>

</cfif>


<!--- END FORM --->
</form>

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

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
gdemaria! It's really good to hear from you. Also, yes, 2010. Goodness. Also also I really hate to drag you into this application again. =)

I followed your advice. YES! I finally got an error message I can work with. Error is:

Detail 	Cannot insert explicit value for identity column in table 'DocHasTopic' when IDENTITY_INSERT is set to OFF.
SQL 	INSERT INTO DocHasTopic (
DocumentID
, DocumentTopicID
) SELECT 1476
, tp.DocumentTopicID
FROM DocTopic tp

WHERE tp.DocumentTopicID IN ( 25 )
DatabaseName 	Microsoft SQL Server
DatabaseVersion 	12.00.5203
DriverName 	Microsoft JDBC Driver 4.0 for SQL Server
DriverVersion 	4.0.4621.201
Datasource 	cep-dc
Stacktrace 	The Error Occurred in
/home/cep-dc/domains/test.cep-dc.org/public_html/admin/EditDocuments.cfm: line 239

    237: WHERE 1 = 2
    238: </cfif>
    239: </cfquery>
    240:
    241: <!---- Update subtopics -----> 

Open in new window


This error makes me want to go look at the SQL Server datasource and check the identity setting on table DocHasTopic, which is the join table. Doing that now.

Hmm. The error seems to say that Lucee expects Identity Specification to be set to YES for ... a column in table DocHasTopic?

I set Identity Specification to YES for column DocumentID. Since this is a Join table I did not set a primary key.

I think Lucee wants me to change something in the SQL Server 2014 join table, DocHasTopic. I am not sure what, though. I attach a capture of table DocHasTopic.

Should I set all columns in the table to  Identity Specification YES?

Eric
DocHasTopic.gif

Author

Commented:
Of course I thought of this just as I pushed Send. I should set  Identity Specification to NO for column DocumentID.

Hmmm .... yes! The record got put to the database.

Progress. I am doing some more testing. This is working out.

Author

Commented:
I think it is solved. It was the identity setting in the DocHasTopic table. I am doing a little more testing.
databaseproblemsolved.gif
CERTIFIED EXPERT

Commented:
Hey Eric - just want to make sure that you want to force a particular ID into your identity column...  typically, the reason to have an identity column is to allow that ID to be auto generated for you.   It has to be unique so allowing it to come up with the next available number that is not used is usually the way to go.

Author

Commented:
gdemaria,

>>> the reason to have an identity column is to allow that ID to be auto generated for you.

Makes sense to me.

Since DocHasTopic is a join table, I am thinking I don't need an identity column.

I am looking back at the older, MS SQL Server 2012 database table -- none of the columns in the old table DocHasTopic are identity columns. I will keep it the same, here, in MS SQL Server 2014 and Lucee.

I think it is working. =)

Eric
CERTIFIED EXPERT

Commented:
Glad to hear

Author

Commented:
Thank you as always to gdemaria. The application is working like a charm.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
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
Empower Your Career
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

Ask ANY Question

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

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.