1 Reply Latest reply on Oct 9, 2015 11:58 AM by ErinVZ

    How to insert dynamic variable in my table?


      Hello everyone, I'm working on the project where I have to pass few parameters and insert them in two different tables. Arguments that I passed in my first table are fine and I do not have any problems to insert those records but in my second table where I'm passing dynamic parameters I keep getting an error. That argument is created on the previous page from drop down. So If I select 1 in my drop down I will have just one input text box, If I select 2 I will have 2 and so on. So I used <cfdump var="#arguments#"> to pass all the arguments from that page. Dynamic arguments are passed like: 


      So at this point I have three parameters but next time I might have only 1. So I'm getting confused how to Insert these records in my second table, also each of these combinations match value from drop down in my table. So combName_0 should be inserted in the same row where DropDown value = '1', combName_1 should match DropDown value= '2'. I created something on my cfc page to insert all of these parameters but for now that works only for my first table where I'm inserting my SerialNumber and Current Combination but second table that inserts combination numbers still does not work. Here is my code that I use:


      <cffunction name="SaveMyFunction" access="remote" output="no"returnformat='JSON'>

        <cfdump var="#arguments#">



        <cfset fncResults = structNew()>


        <!--- Insert Record, return the generated unique Id --->


        <cfquery name="addElements" datasource="xxxxxx">

           Insert Into Table1(SerialNum, CurrentComb, Area, Building)

           Values (<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="30" value="#arguments.SerialNum#">,

           <cfqueryparam cfsqltype="cf_sql_integer" maxlength="5" value="#arguments.CurrentComb#">,

           <cfqueryparam value="#arguments.Area#" cfsqltype="cf_sql_char">,

           <cfqueryparam value="#arguments.Building#" cfsqltype="cf_sql_integer">);

           Select SCOPE_IDENTITY() As RecID;


        <cfif isDefined("arguments.MaxComb")>

          <cfloop index="i" from="0" to="#arguments.nameComb_#i#">

            <cfquery name="addCombo" datasource="xxx">

             Insert Into Table2(BankComb)

             Values (<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="8" value="arguments.nameComb_#i#">)

             Where BankNum = <cfqueryparam value="#arguments.MaxComb#" cfsqltype="cf_sql_integer">




        <cfset fncResults.status = "200">

        <cfset fncResults.id = addLockerLock.RecID>

        <cfcatch type="any">

        <cfset fncResults.status = "400">

        <cfset fncResults.message = "Error inserting record.">




      If anyone see what is wrong with my code please let me know. Thanks in advance.

        • 1. Re: How to insert dynamic variable in my table?
          ErinVZ Level 1

          It's hard to interpret exactly what you've got going on here because the different fields you're showing don't really match up with what your explanation text has. However, if you are dynamically creating a variable name, and then want to get the value of that variable, there are a few ways to do it.  For example, if your field name is  comb_#i# and there are 3 of them, you would loop through #i#, and your field name would be "comb"&#i# and  values would be #form["comb"&i]# . Alternatively, you could also use evaluate(form["comb"&#i#]), but that tends to take a performance hit, from what I understand. See this reference page in the docs for evaluate:  https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-e-g/evalu ate.html


          A quick review of your code, though -- it appears that you are coming up with a single number for #arguments.nameComb_#i#, based on the idea that you're using it as your loop index.  You are then inserting only that single value into Table2, and doing it multiple times, with the number of iterations dependent on the single value of that field.