Write / update multiple values to a table from a cfselect

I have a CFSELECT box that allows multiple choices. I want each choice to be a new record in a table. The CFSELECT separates the choices by commas when you submit. Can someone help with the correct way to write the query for this insert and update?

I understand it's not a good practice to have a query within a loop, so I'm trying to avoid that. Thank you.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

not sure where you got the idea that you should not put a cfquery inside a loop,that's perfectly ok.  

also you cando  an insert ... select where id in  (#FORM.selectOPTIONS#)   if the values from the select are in atable
Pravin AsarPrincipal Systems EngineerCommented:
You should be able to loop over a list. (multiple selections are passed as list).

<cfif isdefined ("form.sbtn1")>
<cfloop index = "ListElement" list = "#form.myselections#">  
<cfform name="f1"  method="post">
<cfselect name="myselections" multiple="true" >
      <option value="value 1">Option 1</option>
      <option value="value 2">Option 2</option>
      <option value="value 3">Option 3</option>
      <option value="value 4">Option 4</option>
<cfinput type="submit" name="sbtn1" value="Submit" >       
Pravin AsarPrincipal Systems EngineerCommented:
replace the cfouptut statement with appropriate query.
Since I do not know the context to table, I have not coded this.

Need more help, let me know.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

earwig75Author Commented:
I was able to take care of the insert. I am having a problem populating the cfselect with the results from a record.

If 2 items are selected in the query, I want both to be selected when the record is viewed. This is what I have."

<cfinvoke component="mycfc" method="getSelectedServiceTypes" ID="#URL.ID#" returnvariable="SelectedServiceTypes">

<cfset ServiceType=#ValueList(getSelectedServiceTypes.SelectedServiceTypes,",")#>

  <cfselect  queryPosition="below" name="ServiceType" query="ServiceTypes" value="ServiceTypeID" display="ServiceType"  multiple="yes"   selected="#ServiceType#" >
                   <OPTION VALUE=" "> </OPTION>
this is the query in the cfc:

<cffunction name="getSelectedServiceTypes" returntype="any" hint="Get selected service types for a record">
  <cfargument name="ReferenceNumber" type="numeric" required="yes" hint="Reference Number">

  <cfquery name="SelectedServiceTypes">
       SELECT ServiceTypeID
        FROM ServiceTypes
        WHERE ServiceTypes.IDr=<cfqueryparam value = "#ARGUMENTS.ID#" CFSQLType = "CF_SQL_INTEGER">
  <cfreturn selectedServiceTypes>

Passing the list of values to the "selected" attribute should work.  What's it doing wrong?
earwig75Author Commented:
I'm receiving this error: The ValueList() function has an invalid parameter: getSelectedServiceTypes.SelectedServiceTypes.
Parameter 1 of function ValueList which is now getSelectedServiceTypes.SelectedServiceTypes must be pointing to a valid query name.
The function is returning the ID column ie ServiceTypeID, but you're using the name column instead. Change it to:

It looks like this method is returning a query
<cfinvoke component="mycfc" method="getSelectedServiceTypes" ID="#URL.ID#" returnvariable="SelectedServiceTypes">

So this format is correct EXCEPT that you are using the wrong variable, the query is returned in the return variable and you are using the method name and then query name instead (this must be throwing an error)
<cfset ServiceType=#ValueList(getSelectedServiceTypes.SelectedServiceTypes,",")#>

So here is the correct format with the returned query name and the column name of the query
<cfset ServiceType=#ValueList(SelectedServiceTypes.ServiceTypeID,",")#>

IMHO, this is not the best way to define the method, why return an entire query?   Create the list of values inside the method and just return the list of the IDs.   That way you don't need to know anything about the query or the column names outside of the cfc, it's the "black box" concept.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
earwig75Author Commented:
I'm now getting this error:
 The ValueList() function has an invalid parameter: getSelectedServiceTypes.ServiceTypeID.
Parameter 1 of function ValueList which is now getSelectedServiceTypes.ServiceTypeID must be pointing to a valid query name.
earwig75Author Commented:
Okay, that worked.
>> it's not a good practice to have a query within a loop

Yes, it should be avoided when possible, because it Is inefficient. It incurs a separate db every time on each iteration.  (For QoQ's it's extra memory).  

For example, say you're looping through a query of customers and querying within the loop to grab some property:

        <cfoutput query="MainQuery">
               <cfquery name="getProps" ....>
                    SELECT SomeProp FROM TableB WHERE ID = <cfqueryparam value="#MainQuery.ID#" ...>
                #MainQuery.CustomerName#  #getProp.SomeProp#
        </cfoutput >

You could do the same thing more efficiently by using a JOIN. The JOIN would return the same information in a single query:

            SELECT  ... columns ... FROM TableA a INNER JOIN TableB b ON b.ID = a.ID

With INSERT's, you can often use an INSERT .. SELECT to insert multiple records into another table. For example, if your form field contains a list of id's, feed that list into a SELECT and you can insert everything in one shot instead of running a separate INSERT for each ID.

        INSERT INTO OtherTable ( ColumnName )
        SELECT ColumnName
        FROM   ServiceTypes
        WHERE  ServiceTypeID IN ( <cfqueryparam value="#listOfSelectedIDs#" cfsqltype="cf_sql_integer" list="true"> )

Keep in mind it's not always possible.  Sometimes you must query within a loop.  Just avoid doing it necessarily.
>> <cfset ServiceType=#ValueList(SelectedServiceTypes.ServiceTypeID,",")#>

Small aside, commas are the default delimiter, so you don't have to specify them.  Also, as you're not outputting anything (or using quotes) no need for the extra # signs.

Yes, it should be avoided when possible, because it Is inefficient

Just to put in context, this was about looping to do multiple inserts.  I completely agree that looping SELECTs should be avoided and the insert...select is an option that should be explored.

You can also do one cfquery and have it contain multiple insert statements if the insert...select is not possible
Yeah, just trying to cover the various scenarios. But I figured it was about the insert. That's why I mentioned there are options for those too. "Optimization isn't just for SELECT's anymore" ;-)

>> You can also do one cfquery and have it contain multiple insert statements

Yep, that's another approach. Just keep in mind there are some slight differences:
- the db must support it (it's not enabled for mySQL jdbc by default) and
- unlike the insert/select a cftransaction is required to ensure data integrity
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.