3 Replies Latest reply on Jun 30, 2006 1:41 AM by murpg

    Counting Keys in a Structure

    murpg Level 1
      I have some code where I am trying to do a dynamic update of any form without knowing the column names by looping over a form structure. How can I find out the total number of keys in the form structure minus the ones I don't want to update so I can not drop a comma on the last key value for the Update Query. Here is my code.

      <cffunction name="MyFunc" returntype="string" access="remote">
      <cfargument name="FormStruct" type="struct" required="yes" />
      <cfset Arg = Arguments.FormStruct />
      <cfset FieldNames = "" />
      <cfset Counter = 0 />
      <cfquery>
      UPDATE MyTable SET
      <cfloop collection="#Arguments.FormStruct#" item="field">
      <cfset Counter = Counter + 1 />
      <cfif field NEQ "FieldNames" AND field NEQ "MyID">
      #field# = '#Arg[field]#'<cfif Counter xxx>, </cfif>
      </cfif>
      </cfloop>
      WHERE MyID = #Arg.MyID#
      </cfquery>
      <cfreturn FieldNames />
      </cffunction>
        • 1. Re: Counting Keys in a Structure
          jedale Level 1
          I would put the comma before the and statement that way you don't have to worry about the last comma at the end of the list. That should take care of your problem except for the first argument that doesn't need a comma. I would hard code a bogus column in order to get the ball rolling so that you could use a comma at the start of your structure loop. Hope this helps.

          Jeff
          • 2. Re: Counting Keys in a Structure
            BKBK Adobe Community Professional & MVP
            an idea

            • 3. Re: Counting Keys in a Structure
              murpg Level 1
              BKBK, thanks that was a petty good insight for dealing with the comma. I am going to throw out another thought. I am also going to have to deal with the proper syntax depending on the data type of the columns I am updating. Any thoughts on how I get those. I was thinking that perhaps I could find this out by using a query like this and appending the data type to the form structure and then start the first function.

              <cfquery name="Recordset2" datasource="#application.ds#">
              Select COLUMN_NAME, DATA_TYPE
              From #application.ds#.INFORMATION_SCHEMA.Columns
              Where TABLE_NAME = '#form.tablename#'
              </cfquery>