Coldfusion loop over insert query values

Question

I am trying to loop over an insert query. I'm looping over the values to the return results are within a list.

<cfquery datasource="#OLMSdatasourceWrite#" result="myResult">
    INSERT INTO OLMS_Data_RatioScenarios
    (
        OLMS_Account_ID,
        OLMS_RatioScenario_Name
    )
    VALUES
    (
    <cfloop list="#AccountListWithSettings#" index="CurrentAccount">
        (<cfqueryparam cfsqltype="cf_sql_numeric" value="#CurrentAccount#" maxlength="255">, <cfqueryparam cfsqltype="cf_sql_clob" value="#requestBody.value#" maxlength="255">)
        <cfif CurrentAccount GT 1>
           ,
        </cfif>
    </cfloop>
    )
</cfquery>

<cfoutput>Inserted ID is: #myResult.generatedkey#</cfoutput>

The problem im running into here is that it keeps putting a "," after the final iteration

VALUES ( ( (param 1) , (param 2) ) , ( (param 3) , (param 4) ) , ( (param 5) , (param 6) ) , )

I need help figureing out the cfif statement to prevent this(NOTE: if i put the cfif above the query params it started the loop out like (,(param 1),(param 2),

You have your answer. I'm merely going to point out that you should double check what the index attribute of looping through a list actually represents. Also, if you insert more than one record, are you sure that myResult.generatedkey will give you what you need? — Dan Bracuk

Chris Peters Chris Peters · Accepted Answer · 2017-02-06T20:02:43

You need to track the number of items in the list and check that the current item's position is less than the length of the list:

<cfset numItems = ListLen(AccountListWithSettings)>
<cfset i = 1>

<cfloop list="#AccountListWithSettings#" index="CurrentAccount">
  <!--- Stuff inside of loop --->

  <!--- ...and then --->
  <cfif i lt numItems>
    ,
  </cfif>

  <cfset i++>
</cfloop>

Update, per @Leigh's comment: you can also take this approach. I add it as a 2nd approach because even though it's simpler, it's not usually how my mind thinks through this particular problem. To each his own. :)

<cfset i = 1>

<cfloop list="#AccountListWithSettings#" index="CurrentAccount">
  <cfif i gt 1>
    ,
  </cfif>

  <!--- Stuff inside of loop --->

  <cfset i++>
</cfloop>

Coldfusion loop over insert query values

1 Answers