dynamically building a select statement in coldfusion.

Posted on 2011-04-21
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
    LVL 28

    Expert Comment

    by:Pravin Asar
    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 28

    Accepted Solution

    <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


    @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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    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 …
    PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now