5 Replies Latest reply on Jul 4, 2007 12:57 AM by Newsgroup_User

    Combining Variables

    ready4god2513
      I have a form that is being generated via a query loop. One of the fields is 'ispercentage'&i (which the i is the index for the loop). So the number afterwards could be between 0 and 9. So obviously the variable name is going to change. How do I place that into my sql insert query? I can't do #form.ispercentage# because that comes back undefined (as is would be 'ispercentage(1-9 or even more as more are added)'. The sql insert query is also looping to add each new item.

        • 1. Re: Combining Variables
          Level 7
          The generally preferred method is array notation:
          #form['scheduleuser" & i]#

          • 2. Re: Combining Variables
            ready4god2513 Level 1
            That works great! One more problem...it's syntax. Does anyone see the problem here?

            <cfquery datasource="#application.dsn#">
            INSERT INTO commSchedules(commScheduleName, brokerageID, <cfif IsDefined (#form['ispercentage' & i]#)>1<cfelse>0</cfif>userID, percentage, percofpie)
            VALUES (#schName#, #form.brokerage#, #form['ispercentage' & i]#, #form['scheduleuser' & i]#, #form['totalperc' & i]#, #form['amount' & i]#)
            </cfquery>

            I get this error:
            Error Executing Database Query.
            [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '0'.
            • 3. Re: Combining Variables
              Level 7
              <cfif IsDefined
              (#form['ispercentage' & i]#)>1<cfelse>0</cfif>userID

              When form['ispercentage' & i] returns a false value you are going to
              insert '0userID' into your SQL statement, is this correct?
              • 4. Re: Combining Variables
                ready4god2513 Level 1
                Actually, no! I knew there was something wrong there, but you pointed it out! That line actually shouldn't even be there! It works perfect now! Thanks so much for the help!
                • 5. Re: Combining Variables
                  Level 7
                  > Actually, no! I knew there was something wrong there, but you pointed it out! That line actually shouldn't even be there! It works perfect now! Thanks so much for the help!

                  The best way of troubleshooting this sort of thing is to use your eyes. As
                  part of the error message, it will display the actual SQL statement that
                  was trying to be executed. This is usually the best thing to check for
                  syntax errors like this, rather than looking at the code that generates the
                  SQL, which relies on you executing the code in your head, as well as having
                  to sort out what is CF code and what is SQL and what is... missing.

                  --
                  Adam