Solved

Update my file

Posted on 2014-04-11
4
178 Views
Last Modified: 2014-04-16
Hi,

I am not sure what I'm doing wrong to prevent it from updating my files.

I just want to update my files.  

I am doing something wrong but not sure what it is.

Thanks,
Lulu


<!--- begin update Uploaded files --->
<cfsetting showdebugoutput="yes">
 <cfset filelocation = expandpath('uploadedfiles/')> 
<cfset maxFiles = 3>
<cfset Attachments="">
      <cfloop from="1" to="#variables.maxFiles#" index="ii">
      <cftry>
             <cfset variables["Descp" & ii] = Form["FileDescription" & ii]>
             <cfset variables["FileName" & ii] = Form["FileName" & ii]>
			 <cfset variables["FileID" & ii] = Form["FileID" & ii]>

            <cfif StructKeyExists(form,"AttachFile" & ii) && len(trim(Form["AttachFile" & ii]))>

            <CFFILE ACTION="UPLOAD"
                        FILEFIELD="AttachFile#ii#"
                        DESTINATION="#variables.filelocation#"
                        NAMECONFLICT="makeunique">
     
             <cfset Attachments=ListAppend(variables.Attachments,cffile.serverfile)>  
          
        	</cfif>
            <cfcatch>
            <br>#cfcatch.message#<br>#cfcatch.detail#<br>
            #cfcatch.tagContext[1].template# Line #cfcatch.tagContext[1].line#
            </cfcatch></cftry>
      </cfloop>


      <cfquery name = "QLogFiles"  datasource="#strDSN#" username="#strUID#" password="#strPWD#" result="rs">
			Set Nocount On;
				<cfset cnt=0>
                   <cfloop list="#variables.Attachments#" index="ii">
            <cfset cnt++/>	

               update Files 
               SET 

				<cfif #variables["Descp" & cnt]# neq ''>
					FileName = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#variables["Descp" & cnt]#">,
				<cfelse>
					FileName =  <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#variables["FileName" & cnt]#">,
				</cfif>
          
            <cfif len(trim(variables.Attachments))> 
          <cfif FileExists(variables.filelocation & ii)>
      			<cfif IsDefined('ToBase64(variables.filelocation & ii)') && #ToBase64(variables.filelocation & ii)# neq ''>
					UploadedFile = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#ToBase64(variables.filelocation & ii)#">,
				</cfif>
                     
            	<cfif IsDefined('ToBase64(variables.filelocation & ii)') && #ToBase64(variables.filelocation & ii)# neq ''>
					uploadedasbinary = <cfqueryparam cfsqltype="cf_sql_blob" value="#FileReadBinary(variables.filelocation & ii)#">,
				</cfif>
         </cfif>
         </cfif>
   					CreatedDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Now()#">

       		WHERE FileID = #variables["FileID" & ii]#  
      </cfloop>
</cfquery>


<cfoutput>SQL: #rs.SQL#</cfoutput> 

<!--- end of update file --->

Open in new window

0
Comment
Question by:lulu50
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 39995022
Hi Lulu, I would like to see you exact code and what you need here is this like this:

1. Show the image or doc file on the screen.
2. upload another file and replace one.
3. delete existing files..

can you show your complete code here
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
ID: 39995165
I hope this helps, I am not sure how to do the file conversion to base64 to upload into the database, haven't done that before.   I left that out just to show you an example of how this code can flow.

It is not necessary to use variables with counters on them inside your loop.   I have made the code simpler by using regular variables such as variable.fileID

             <cfset variables.Descp    = Form["FileDescription" & ii]>
             <cfset variables.FileName = Form["FileName" & ii]>

The other important change was that I put the CFQUERY inside your loop.   It's not a big deal to call your update three times and makes the code a lot easier if you just get your values together, update the database and then go to the next row and do the same.

Lastly, rather than using a CFIF statement to see if the value of the form field is empty I added NULL="" parameter of the cfqueryparam.   If the value of the variable is empty, this will insert a NULL into the database.   I wasn't sure where you were going with the CFIF statements, maybe I got it wrong??   Let me know, I think there is an easier way.


      <cfloop from="1" to="#variables.maxFiles#" index="ii">
      <cftry>
             <cfset variables.Descp    = Form["FileDescription" & ii]>
             <cfset variables.FileName = Form["FileName" & ii]>
			 <cfset variables.FileID   = Form["FileID" & ii]>
			 <cfset Attachments = ""> <!---- assume there is no attachment --->

            <cfif StructKeyExists(form,"AttachFile" & ii) && len(trim(Form["AttachFile" & ii]))>
	            <CFFILE ACTION="UPLOAD"
	                        FILEFIELD="AttachFile#ii#"
	                        DESTINATION="#variables.filelocation#"
	                        NAMECONFLICT="makeunique">
	             <cfset Attachments= cffile.serverfile>  
        	</cfif>
	
	        <cfquery name = "QLogFiles"  datasource="#strDSN#" username="#strUID#" password="#strPWD#" result="rs">
	               update Files 
	               SET 
					  fileDescription = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#variables.fileDescription#" null="#NOT len(variables.fileDescription)#">
					 ,FileName = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#variables.fileName#" null="#NOT len(variables.fileName)#">
	   				 ,CreatedDate = <cfqueryparam cfsqltype="cf_sql_date" value="#Now()#">
	       	       WHERE FileID = #variables.FileID#  
            </cfquery>

       <cfcatch>
       <br>#cfcatch.message#<br>#cfcatch.detail#<br>
       #cfcatch.tagContext[1].template# Line #cfcatch.tagContext[1].line#
       </cfcatch>
	   </cftry>
      </cfloop>

Open in new window

0
 
LVL 16

Assisted Solution

by:Gurpreet Singh Randhawa
Gurpreet Singh Randhawa earned 250 total points
ID: 39995664
Lulu, To show what you have added in the database as base64, to convert it back to the string

use the following:
toString(tobinary(filnema))

Open in new window


For other things, @gd is guiding you good
0
 

Author Closing Comment

by:lulu50
ID: 40003905
Thank you.

I got it to work!!!!!!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question