Inserting multiple data with cfloop?

[kingzulu]

I’m trying to insert movie data into my database using a cfloop.

I have a movie which must be link to a number if venues i.e. 20. On my input form I have a list of all the venues together with a yes/no dropdown box. The purpose is to say yes this movie is showing at this venue or visa versa.

When the form is activated it sends a comer separated list of venue IDs and yes/no’s to the insert statement. As can been seen from my code below I am using a cfloop to add the data which works.

BUT,

it loops though the data and instead of adding 20 records it adds 800. If I remove one of the cfloop’s i.e. the yes/no loop then it adds the correct number of records (20). This doesn’t help when I need to recall the data because I need the yes/no field.

What am I doing wrong? Can someone please help?????

CODE:

[<cfloop index="i" list="#FORM.theatersBrID#" >
<cfloop index="b" list="#FORM.ven_status#">
<cfset a = #i# & #b#>

<cfquery datasource="#REQUEST.DataSource#" name="#a#" >
INSERT INTO tbl_movies_venue (moviesID,
theatersBrID,
ven_times,
ven_status)
VALUES (
<cfif IsDefined("FORM.moviesID") AND #FORM.moviesID# NEQ "">
<cfqueryparam value="#FORM.moviesID#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
,
<cfif IsDefined("i") AND #i# NEQ "">
<cfqueryparam value="#i#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
,
<cfif IsDefined("FORM.ven_times") AND #FORM.ven_times# NEQ "">
<cfqueryparam value="#FORM.ven_times#" cfsqltype="cf_sql_clob" maxlength="150">
<cfelse>
''
</cfif>
,
<cfif IsDefined("b") AND #b# NEQ "">
<cfqueryparam value="#b#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
)
</cfquery>
<!--- close loop ---></cfloop>
</cfloop>]

Help will be appreciated

[kingzulu]

So, I have been able to resolve this issue... (See my first posting)

I my attempt I have reverted to using an editable cfgrid which seen to work to the point that the I now seen to find the cfgridupdate tag doesn't seem to work in ColdFusion 7.02. Has anybody else had the same problem???

If not have a look at my code, maybe I’m just stupid!!!!

FORM PAGE

<cfquery datasource="#REQUEST.DataSource#" name="getThe">
SELECT tbl_movies_venues.*
FROM tbl_movies_venues
WHERE tbl_movies_venues.moviesID = '#URL.moviesID#'
</cfquery>

<cfform
action="action/updateVenues.cfm"
format="flash"
width="770">

<cfgrid name="myGrid2" query="getThe" height="250" colheaders="yes" rowheaders="no" selectmode="edit">
<cfgridcolumn name="venueID" header="Venue ID" select="no" />
<cfgridcolumn name="moviesID" header="Movie ID" select="no" />
<cfgridcolumn name="theatersBrID" header="Theatres ID" select="no" />
<cfgridcolumn name="ven_status" header="Venue" select="yes" />
<cfgridcolumn name="ven_times" header="Time" select="yes" />
</cfgrid>--->
<cfformgroup type="horizontal">
<cfinput type="submit" name=”addit” value="Add Theaters to Movie">
</cfformgroup>
</cfform>

ACTION PAGE (this is on a different page)

<cfgridupdate grid="myGrid2" dataSource="#REQUEST.DataSource#" tableName="tbl_movies_venues"/>

Any help will be appreciated