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

Posted on 2006-03-29
Last Modified: 2013-12-24

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">


<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>      
<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)

               1 232/myfile1.doc  
               2 232/myfile2.doc  
              1 232  
              2 232  
            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">)

Question by:whaleyk
    LVL 10

    Accepted Solution

    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">)

    Author Comment

    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 :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
    Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now