We help IT Professionals succeed at work.

Write / update multiple values to a table from a cfselect

earwig75
earwig75 asked
on
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.
Comment
Watch Question

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 Engineer
Top Expert 2005

Commented:
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#">  
    <cfoutput>#ListElement#</cfoutput><br>  
</cfloop>
</cfif>
<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>
</cfselect>
<cfinput type="submit" name="sbtn1" value="Submit" >       
</cfform>
Pravin AsarPrincipal Systems Engineer
Top Expert 2005

Commented:
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.

Author

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>
   </cfselect>
-------------------------------------------------------------
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">
  </cfquery>
  <cfreturn selectedServiceTypes>

 </cffunction>
Most Valuable Expert 2015

Commented:
Passing the list of values to the "selected" attribute should work.  What's it doing wrong?

Author

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.
Most Valuable Expert 2015

Commented:
The function is returning the ID column ie ServiceTypeID, but you're using the name column instead. Change it to:

       valueList(getSelectedServiceTypes.ServiceTypeID)
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.

Author

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.

Author

Commented:
Okay, that worked.
Most Valuable Expert 2015

Commented:
>> 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#" ...>
               </cfquery>
                #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.
Most Valuable Expert 2015

Commented:
>> <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
Most Valuable Expert 2015

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