Highlighted

How to insert dynamic variable in my table?

Community Beginner ,
Oct 09, 2015

Copy link to clipboard

Copied

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: 

combName_034-54-65
combName_176-87-98
combName_289-88-88

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 --->

  <cftry>

  <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;

  </cfquery>

  <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">

    </cfquery>

   </cfloop>

  </cfif>

  <cfset fncResults.status = "200">

  <cfset fncResults.id = addLockerLock.RecID>

  <cfcatch type="any">

  <cfset fncResults.status = "400">

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

  </cfcatch>

  </cftry>

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

Views

260

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

How to insert dynamic variable in my table?

Community Beginner ,
Oct 09, 2015

Copy link to clipboard

Copied

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: 

combName_034-54-65
combName_176-87-98
combName_289-88-88

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 --->

  <cftry>

  <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;

  </cfquery>

  <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">

    </cfquery>

   </cfloop>

  </cfif>

  <cfset fncResults.status = "200">

  <cfset fncResults.id = addLockerLock.RecID>

  <cfcatch type="any">

  <cfset fncResults.status = "400">

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

  </cfcatch>

  </cftry>

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

Views

261

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 09, 2015 0
ErinVZ LATEST
New Here ,
Oct 09, 2015

Copy link to clipboard

Copied

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/evaluate.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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 09, 2015 0