ColdFusion Insert multiple loop, list values

Hello Experts
I have a rather large Cold Fusion form where a customer can select multiple destionations and have the results added to a database. There can be dozens of destinations submited from the form by way of checkboxes. So far, so good. However, each destination that is selected is inserted into a new database row with the appropriate values inserted for each field. So if someone selects Montreal, the database is be updated to reflect like so... Field = value i.e.

BrandID = 123, Brand = MyClient, Destination = Montreal, City = Montreal, State = Quebec, Country = Canada, Continent = North America.
BrandID = 123, Brand = MyClient, Destination = Niagra Falls, City = Niagra Falls, State = New York, Country = USA, Continent = North America.
BrandID = 123, Brand = MyClient, Destination = Canada, City = , State = , Country = USA, Continent = North America.

I want to get the brand and destination information from 2 separate tables and insert the results into a 3rd table called AC_DestinationList_Test after deleting the previous brand entries.
I tried an "insert" query followed by an "update" query with limited success. The BrandID, Brand and Destination fields are always populated in new rows correctly, but depending on the code I try, I either get nothing else inserted or the entire comma delim list in each field.

Can someone please show me what I'm doing wrong?
<cfquery name="GetRecord" Datasource="DBSource">
    Select *
    FROM AC_Brand
    where BrandID = #Brandid# 
    </cfquery>
		
    <cfquery name="getDestinations" datasource="DBSource">
    Select Destination, City, State, Country, Continent, Region, DestinationArea
    from AC_DestinationList
	</cfquery>	

	
	<CFQUERY Name="DeleteElement" Datasource="DBSource">
 	Delete From AC_DestinationList_Test
	where BrandID=#BrandID#
	</CFQUERY>
	

	<CFLOOP INDEX="ListDestinations" list="#Destination#">
	<CFQUERY Name="List_Destination" DataSource="DBSource">
	INSERT INTO AC_DestinationList_Test (Brand, BrandID, Destination, City, State, Country, Continent, Region, DestinationArea) 
	VALUES ('#GetRecord.BrandName#', '#BrandID#', '#ListDestinations#', '#City#', '#State#', '#Country#', '#Continent#', '#Region#', '#DestinationArea#'
	)
	</CFQUERY></CFLOOP>
	
	
		<cfoutput>
#Destination#
		</cfoutput>

Open in new window

MmoconnorinteractiveAsked:
Who is Participating?
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.

gdemariaCommented:
fetching the other columns should be inside your loop, you also need to scope your variables

I changed the name of your index="" variable because the index is only one item of the list, not the list, that seemed confusing

    <CFLOOP INDEX="oneDestination" list="#Destination#">
        <cfquery name="getDest" datasource="DBSource">
          Select Destination, City, State, Country, Continent, Region, DestinationArea
          from AC_DestinationList
          where deistnationID = #val(oneDestination)#
        </cfquery>      
        <CFQUERY Name="List_Destination" DataSource="DBSource">
          INSERT INTO AC_DestinationList_Test (Brand, BrandID, Destination, City, State, Country, Continent, Region, DestinationArea) 
          VALUES ('#GetRecord.BrandName#', '#BrandID#', '#oneDestination#', '#getDest.City#', '#getDest.State#', '#getDest.Country#', '#getDest.Continent#', '#getDest.getDest.Region#', '#getDest.DestinationArea#'
          )
        </CFQUERY>
    </CFLOOP>

Open in new window

0
_agx_Commented:
If I'm understanding the form correctly ... I would suggest a different approach.  If all of the "destination" checkboxes the same name, the selected ID's will be submitted as a comma delimited list like:

    form.destinationID = 2,15,6,16

You can then use a simple CROSS JOIN to do the insert, which is more efficient than looping.


INSERT INTO AC_DestinationList_Test
(
Brand, BrandID, Destination, City, State, Country, Continent, Region, DestinationArea
 )
 SELECT b.BrandName, b.BrandID,
                        d.DestinationID, d.City, d.State, d.Country, d.Continent, d.Region, d.DestinationArea
 FROM        AC_Brand b CROSS JOIN AC_DestinationList d
  WHERE  b.BrandID = <cfqueryparam value="#form.Brandid#" cfsqltype="cf_sql_integer">
 AND         d.destinationID IN ( <cfqueryparam value="#form.destination#" list="true" cfsqltype="cf_sql_integer"> )

But, the tables really should be normalized. Just store the brand and destination ID's, not the details like BrandName, City, State, etc... That information can be retreived with a simple JOIN.  That way you eliminate unecessary duplication, improve performance etc...
0
_agx_Commented:
Sorry the EE rich text editor messed up the query sample. Here it is again formatted.

=========================
If I'm understanding the form correctly ... I would suggest a different approach.  If all of the "destination" checkboxes the same name, the selected ID's will be submitted as a comma delimited list like:

    form.destinationID = 2,15,6,16

You can then use a simple CROSS JOIN to do the insert, which is more efficient than looping. (See below).   But, the tables really should be normalized. Just store the brand and destination ID's, not the details like BrandName, City, State, etc... That information can be retrieved with a simple JOIN.  That way you eliminate unnecessary duplication, improve performance etc...


<!---
    Assuming AC_DestinationList has a unique Record ID
    Note: Make sure to use CROSS JOIN correctly to prevent unexpected results
--->
INSERT INTO AC_DestinationList_Test
(
        Brand, BrandID, Destination, City, State, Country, Continent, Region, DestinationArea
)
SELECT   b.BrandName, b.BrandID,
         d.DestinationID, d.City, d.State, d.Country, d.Continent, d.Region, d.DestinationArea
FROM     AC_Brand b CROSS JOIN AC_DestinationList d
WHERE    b.BrandID = <cfqueryparam value="#form.BrandID#" cfsqltype="cf_sql_integer">
AND      d.destinationID IN 
         ( 
         <cfqueryparam value="#form.destinationID#" list="true" cfsqltype="cf_sql_integer"> 
         )

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

gdemariaCommented:

I agree with agx, I was too focused on the code as it was written.

His approach is much better, I suggest using it..
0
MmoconnorinteractiveAuthor Commented:
Thanks guys for the quick response. Sage, I was wondering... in the first code that wasn't formatted correctly, there are several more  parameter values at the end of the query. The final formatted code only has one parameter. The reson I ask s because I am getting a syntax error

<cfqueryparam value="#form.DestID#" list="true" cfsqltype="cf_sql_integer">

any Ideas?
0
_agx_Commented:
Yes, the EE editor messed up the first one. Try the reformatted example.  Adjust the cfsqltype to match your column types.
0
gdemariaCommented:
Check the spelling of the destination variable.
In the above posts, it is written three different ways...

form.destID, form.destination and form.destinationID

In your code, change them so they all match the form field's name

0
_agx_Commented:
> The final formatted code only has one parameter.

.... I missed that comment.  No, the final example has _two_ parameters: #form.BrandID# and #form.destinationID#.  

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_25590876.html?cid=1573#a29006467

Assuming you correct the variable names to match your form (as mentioned), it should work fine.
....

WHERE    b.BrandID = <cfqueryparam value="#form.BrandID#" cfsqltype="cf_sql_integer">
AND      d.destinationID IN
         (
         <cfqueryparam value="#form.destinationID#" list="true" cfsqltype="cf_sql_integer">
         )
0
MmoconnorinteractiveAuthor Commented:
Thanks!  I'll check it out and will let you know.
0
MmoconnorinteractiveAuthor Commented:
YIKERS! I'm still getting a syntax error. no matter what I try. Do I need to have the "form." in the cfqueryparams? When it is ine there I get a "Element BRANDID is undefined in FORM." error. When I take it out I get a "Syntax error in FROM clause."   The id fields are numeric but changing them to cf_sql_numeric didn't help. Here's the revised code:

INSERT INTO AC_DestinationList_Test
(
        Brand, BrandID, DestID, Destination, City, State, Country, Continent, Region, DestinationArea
)
SELECT   b.BrandName, b.BrandID,
         d.DestID, d.Destination, d.City, d.State, d.Country, d.Continent, d.Region, d.DestinationArea
FROM     AC_Brand b CROSS JOIN AC_DestinationList d
WHERE    b.BrandID = <cfqueryparam value="#form.BrandID#" cfsqltype="cf_sql_integer">
AND      d.DestID IN  
         (  
         <cfqueryparam value="#form.DestID#" list="true" cfsqltype="cf_sql_integer">  
         )
                         

Thanks again
0
_agx_Commented:

> Do I need to have the "form." in the cfqueryparams?

   No.  It's recommended to scope variables, but not strictly required in most cases.  Since I can't see your
   form code I "guessed" at your form type. But you're obviously using something different. Just omit the
   scope for now.  (You can add the proper scope once you get it working)

> When I take it out I get a "Syntax error in FROM clause."  

  I tested it with MySQL and it works perfectly.  What type of database are you using ?  Most enterprise
  db's support CROSS joins. Except maybe MS Access..?
0
_agx_Commented:
> Most enterprise db's support CROSS joins. Except maybe MS Access..?

   ... To clarify that comment, MS Access is a desktop db (not in the enterprise category).
   So it's not generally recommended for web apps. But anyway, is that what you're using?
0
MmoconnorinteractiveAuthor Commented:
yup MS Access. Does That mean I'm back to looping?
0
_agx_Commented:
No. I think you could do it with Access. Though not quite as elegantly.  Add back your query to grab the "BrandName". Then add the BrandName value to the SELECT list like the example below.  NOTE: You didn't mention the data type of BrandID, so I'm assuming it's numeric.  Once you get it working, add in proper variable scopes.


<!--- NOT tested with MS Access --->
<cfquery name="GetRecord" Datasource="DBSource">
    SELECT BrandName
    FROM   AC_Brand
    WHERE  BrandID = #BrandID# 
</cfquery>

<cfquery name="AddRecords" Datasource="DBSource">
INSERT INTO AC_DestinationList_Test
(
   Brand, BrandID, DestID, Destination, City, State, Country, Continent, Region, DestinationArea
)
SELECT   
         <cfqueryparam value="#GetRecord.BrandName#" cfsqltype="cf_sql_varchar">  
         , <cfqueryparam value="#BrandID#" cfsqltype="cf_sql_integer">  
         , d.DestID, d.Destination, d.City, d.State, d.Country, d.Continent, d.Region, d.DestinationArea
FROM     AC_DestinationList d
WHERE    d.DestID IN  
         (  
         <cfqueryparam value="#DestID#" list="true" cfsqltype="cf_sql_integer">  
         )
</cfquery>

Open in new window

0

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
MmoconnorinteractiveAuthor Commented:
AGX... YOU ARE THE MAN!
Worked like a charm! Thanks sooooooooo much!
0
_agx_Commented:
You're welcome :)  For PAQ purposes, it could also be done in a single cfquery. But .. the sql's
a little more convoluted. Here's an example for reference ..
<cfquery name="AddRecords" Datasource="DBSource">
INSERT INTO AC_DestinationList_Test
(
   Brand, BrandID, DestID, Destination, City, State, Country, Continent, Region, DestinationArea
)
SELECT   
       ( SELECT BrandName  FROM AC_Brand WHERE BrandID = <cfqueryparam value="#BrandID#" cfsqltype="cf_sql_integer"> )
         , <cfqueryparam value="#BrandID#" cfsqltype="cf_sql_integer">
         , d.DestID, d.Destination, d.City, d.State, d.Country, d.Continent
         , d.Region, d.DestinationArea
FROM     AC_DestinationList d
WHERE    d.DestID IN  
         (  
         <cfqueryparam value="#DestID#" list="true" cfsqltype="cf_sql_integer">  
         )
</cfquery>

Open in new window

0
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
Web Servers

From novice to tech pro — start learning today.