Skip to main content
M
m_____1
Participating Frequently
July 17, 2009
Question

cfquery sql update loop problem

  • July 17, 2009
  • 4 replies
  • 4593 views

Trying something a bit tricky, need some ideas.

I've got an array of objects that I pass to a cfc from flex, I'm trying to save this array to the database.

Here's what I have that doesn't work.

<cfquery name="setObjDetail" datasource="test">
     UPDATE ObjTable
     SET 
     <cfloop index="i" from="1" to="#len(objDetails)#">
          #objDetails.Name# = '#objDetails.Value[1]#' ,
     </cfloop>
WHERE OBJID = <cfqueryparam value="#objID#" cfsqltype="cf_sql_integer">
</cfquery>

The error I'm getting is

faultCode:Server.Processing faultString:'Unable to invoke CFC - Complex object types cannot be converted to simple values.' faultDetail:'The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values. <p> The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a cfif tag.'

I have the feeling it's due to the equals symbol (and maybe the comma at the end too). I've tried putting cfoutput tags around it, using ToString method... running out of ideas.

Both #objDetails.Name#  and  #objDetails.Value[1]# evaluate correctly.

Thanks.

    This topic has been closed for replies.

    4 replies

    M
    m_____1Author
    Participating Frequently
    July 17, 2009

    Hi guys thanks for the replies.

    Yes I'd figured out it was ArrayLen which was the problem in the error message.

    I still have problems, I think it's because Coldfusion doesn't like that equals sign... so I tried

    <cfquery name="setObjDetail" datasource="test">
     UPDATE ObjTable
     SET 
     <cfloop index="i" from="1" to="#ArrayLen(objDetails)#">
          #objDetails.Name# & " = '" & #objDetails.Value[1]# & "',"
     </cfloop>
WHERE OBJID = <cfqueryparam value="#objID#" cfsqltype="cf_sql_integer">
</cfquery>

    However this is giving me the following error

    faultCode:Server.Processing faultString:'Unable to invoke CFC - You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.' faultDetail:''

    Which also comes up when I just have

    <cfquery name="setObjDetail" datasource="test">
     UPDATE ObjTable
     SET 
     <cfloop index="i" from="1" to="#ArrayLen(objDetails)#">
          #objDetails.Name# = '#objDetails.Value[1]#' ,
     </cfloop>
WHERE OBJID = <cfqueryparam value="#objID#" cfsqltype="cf_sql_integer">
</cfquery>

    So maybe it's nothing to do with the equals sign.

    Any suggestions?

    Dileep_NR
    Dileep_NR
    Inspiring
    July 17, 2009
    try this

    <cfloop index=    "i" from="1" to="#ArrayLen(objDetails)#">
              #objDetails.Name#   = '#objDetails.Value[1]#',
         </cfloop>
    Dileep_NR
    Dileep_NR
    Inspiring
    July 17, 2009

    Please make sure all fields should be string

    Dileep_NR
    Dileep_NR
    Inspiring
    July 17, 2009

    plz try the following

    if "objDetails" is an array then use  "arraylen"

    objDetails
    <cfquery name="setObjDetail" datasource="test">
         UPDATE ObjTable
         SET 
         <cfloop index=    "i" from="1" to="#arraylen(objDetails)#">
              #objDetails.Name# = '#objDetails.Value[1]#' ,
         </cfloop>
    WHERE OBJID = <cfqueryparam value=    "#objID#" cfsqltype="cf_sql_integer">
    </cfquery>
    Dileep_NR
    Dileep_NR
    Inspiring
    July 17, 2009

    plz try the following

    if "objDetails" is an array then use  "arraylen"

    objDetails
    <cfquery name="setObjDetail" datasource="test">
         UPDATE ObjTable
         SET 
         <cfloop index=    "i" from="1" to="#arraylen(objDetails)#">
              #objDetails.Name# = '#objDetails.Value[1]#' ,
         </cfloop>
    WHERE OBJID = <cfqueryparam value=    "#objID#" cfsqltype="cf_sql_integer">
    </cfquery>
    D
    Dan_Bracuk
    Inspiring
    July 17, 2009

    To escape the comma, put field_x = field_x after your loop.

    For the error you are getting, you say you have an array of objects.  That being the case, use arraylen(), not len() to in your cfloop tag.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices