dynamically building a select statement in coldfusion.

Working away on a little CRM type tool and I am a little stumped.

here is the issue I have.

Page1.cfm
asks for number of lines to work with (called portnum)

Calls page2.cfm portnum is passed over in a session variable

I loop on the form to show a input field for each line
<cfloop from="1" to="#session.portingfields.portnum#" index="counter">
  <cfoutput>
           	<cfinput type="text" name="number_#counter#" value="" size="8"><br>
  </cfoutput>
</cfloop>

Open in new window

and it calls writelines.cfm on submit

my issue is how to write it out to the sql after the person enters the details.   I am thinking that i build the insert string in writelines.cfm on the fly.  Am I right in this thinking or is their an easier way ?

thanks
PatrickMacCAsked:
Who is Participating?
 
Pravin AsarPrincipal Systems EngineerCommented:
<cfset qryStr= "select ">
<cfloop item="fname" collection="#form#">
<cfoutput>#fname# - #form[fname]#<br></cfoutput>
<cfif FIND("number_",fname,1)>
<cfset qryStr = qryStr & #fname#  & ",">
</cfif>
</cfloop>

<!--- REMOVE Extra , at the end of string --->

<cfset qryStr = mid(qrystr,1,#len(qrystr)#-1)>

<cfset qryStr = qryStr & "   from databaseTableName">

<cfquery name="qry" datasource="#DSN#">
#qryStr#
</cfquery>
0
 
Pravin AsarPrincipal Systems EngineerCommented:
You can access

#form.fieldnames# to get the fieldnames

e.g.

<cfloop item="fname" collection="#form#">
<cfoutput>#fname# - #form[fname]#<br></cfoutput>
</cfloop>


Use this information to build select statement

0
 
gdemariaCommented:

@Patrick,  you are doing it the correct way (although I don't know why you need to use a session variable for the loop counter, I think that could lead to bugs if the user opens a second window)

In your action statement, all you need to do is repeat the loop again and grab the value from the form variables.   In this statement below you can see how I take the form.number_1, 2, 3... and put it into a variable called "theNUmber"   then use "TheNUmber" in your insert statement.

<cfset theNumber = form["number_" & counter]>

Any variable can be called using the structure syntax with the variable scope form["myName"] is the same as form.myName.   Then we are just adding the dynamic counter to that.


<cfloop from="1" to="#session.portingfields.portnum#" index="counter">
  <cfset theNumber = form["number_" & counter]>
  <cfquery name="doInsert" datasource="#request.datasource#">
     insert into table (xxxx) values ('#theNumber#')
  </cfquery>
</cfloop>

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.