• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

How to loop this structure and insert values to SQL table? Thanks!


I have a function which uploads files.  (Could be one file, could be many files).  I've created a structure which holds the values I *might* need to insert into a SQL table (I've tried to write this function as dynamically as possible).  I **think** I've created the structure correctly?  (But I'm new to this).

<cfset var.myFileList=arguments.myFileList>

<cfset fileInfo=StructNew()>
<cfset var.count="1">
<cfloop list="#var.myFileList#"  index="i">
<cffile
   action="upload"
   filefield="Form.#trim(i)#"
   destination="#arguments.directory##arguments.trackingNo#"
   nameconflict="#arguments.conflict#">

<cffile
  action="rename"
  source="#arguments.directory##arguments.trackingNo#\#file.serverFile#"
  destination="#arguments.directory##arguments.trackingNo#\#i#.#file.clientFileExt#"
  nameconflict="#arguments.conflict#">

<cfset var.fileName="#arguments.TrackingNo#/#i#.#File.ClientFileExt#">      
<cfset fileInfo.trackingNo[#var.count#]=arguments.trackingNo>
<cfset fileInfo.fileName[#var.count#]=var.fileName>
<cfset fileInfo.userDefinedName[#var.count#]=file.serverFile>
<cfset var.count=var.count+1>      
</cfloop>
<cfreturn fileInfo/>

In the example code above, I'm actually working with two uploads, so I need to add information about two uploads to my structure (looks like this)

struct
FILENAME struct
               1 232/myfile1.doc  
               2 232/myfile2.doc  
 
TRACKINGNO struct
              1 232  
              2 232  
 
USERDEFINEDNAME struct
            1 whippet.doc  
            2 greyhound.doc  


I can't figure out tho, how to loop this structure and insert the values into a Sql table.


<cfloop item="i" collection="#fileInfo#">
<cfquery datasource="mine">
  insert into table (fileName,trackingNo,userDefinedName,uploadDate)
  values (<cfqueryparam value = "#fileInfo.fileName#" cfsqltype="cf_sql_varchar">,
 <cfqueryparam value = "#fileInfo.trackingNo#" cfsqltype="cf_sql_integer">,
 <cfqueryparam value = "#fileInfo.userDefinedName#" cfsqltype="cf_sql_varchar">,
 <cfqueryparam value = "#now()#" cfsqltype="cf_sql_date">)
 </cfquery>
</cfloop>


0
whaleyk
Asked:
whaleyk
1 Solution
 
rob_lorentzCommented:
try this.

<cfloop from="1" to="#arrayLen(fileInfo.trackingNo)#" index="x">
      <cfquery datasource="mine">
            insert into table (fileName,trackingNo,userDefinedName,uploadDate)
            values (<cfqueryparam value = "#fileInfo.fileName[x]#" cfsqltype="cf_sql_varchar">,
                  <cfqueryparam value = "#fileInfo.trackingNo[x]#" cfsqltype="cf_sql_integer">,
                  <cfqueryparam value = "#fileInfo.userDefinedName[x]#" cfsqltype="cf_sql_varchar">,
                  <cfqueryparam value = "#now()#" cfsqltype="cf_sql_date">)
      </cfquery>
</cfloop>
0
 
whaleykAuthor Commented:
Thanks very much Rob.  Received the error:
Object of type class coldfusion.runtime.Struct cannot be used as an array

so changed to:
<cfloop from="1" to="#structCount(fileInfo.trackingNo)#" index="x">

Works :-)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now