dynamically building a select statement in coldfusion.

Posted on 2011-04-21
Medium Priority
Last Modified: 2012-05-11
Working away on a little CRM type tool and I am a little stumped.

here is the issue I have.

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">
           	<cfinput type="text" name="number_#counter#" value="" size="8"><br>

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 ?

Question by:PatrickMacC
  • 2
LVL 29

Expert Comment

by:Pravin Asar
ID: 35446820
You can access

#form.fieldnames# to get the fieldnames


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

Use this information to build select statement

LVL 29

Accepted Solution

Pravin Asar earned 500 total points
ID: 35446841
<cfset qryStr= "select ">
<cfloop item="fname" collection="#form#">
<cfoutput>#fname# - #form[fname]#<br></cfoutput>
<cfif FIND("number_",fname,1)>
<cfset qryStr = qryStr & #fname#  & ",">

<!--- 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#">
LVL 39

Assisted Solution

gdemaria earned 500 total points
ID: 35447200

@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#')

Open in new window


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question