Loop Update? Is this possible

[radiance]

Hello.

I have an admin form, which allows the user to add calendar items. The calendar items can have multiple "views". These views are pulled from a separate table in my sql db. I am having success with my insert statement. Data is insterted into the main table as well as the parts table. I am able to get one insert for each iteration of the loop. I am using a list loop:

<cfquery name="getCalendarID" datasource="#request.dsn#">
SELECT MAX(Calendar_ID) as LastPost
FROM calendar
</cfquery>

<cfloop list="#form.showID#" index="FormshowID">
<cfquery name="insertParts" datasource="#request.dsn#">
INSERT INTO calendarParts
(Calendar_ID, showID, dateAdded)
VALUES
('#getCalendarID.LastPost#', '#FormshowID#', GETDATE())
</cfquery>
</cfloop>

The problem is updating. How do I update a list loop? The user sees a form with a select box (so they can choose multiple options) Is it even a list loop for an update?

I tried the following, and it updated the records (let's say there were 3 records for example), but those records in question now have the same value! So, I gather I should not use a loop?????

I tried the following:

<cfquery name="getcalendar_id" datasource="#request.dsn#">
SELECT MAX(calendar_id) as newID
FROM calendar
</cfquery>

<cfloop list="#form.showID#" index="FormshowID">
<cfquery name="updateParts" datasource="#request.dsn#">
UPDATE calendarParts
SET
calendar_id='#getcalendar_id.newID#',
showID='#FormshowID#',
dateModified=GETDATE()
WHERE calendar_id = #calendar_id#
</cfquery>
</cfloop>

THANKS in ADVANCE!!!!

 

[webmigit]

I get what you were doing.. you were a bit off though.. Your query returns one value as MAX() tells it to..

<cfquery name="getcalendar_id" datasource="#request.dsn#">
     SELECT MAX(calendar_id) as newID
     FROM calendar
</cfquery> 

<cfset calA = ListToArray(valuelist(getcalender_id.newid))>
<cfset calB = ListToArray(form.showID)>
<cfloop list="#ArrayLen(calb)#" index="xID">
<cfquery name="updateParts" datasource="#request.dsn#">
UPDATE calendarParts 
SET
calendar_id='#calA[xID]#', 
showID='#calB[xid]#', 
dateModified=GETDATE()
WHERE calendar_id = #calendar_id#
</cfquery>
</cfloop>

That's basically what I came up with but it won't work... but it might get you started... You need to find a way to get the values you want from the first query.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[radiance]

Thank You.

I am not sure if I understand. Should I create an array??
The insert page is successful. Inserts data in calendar table as well as separate records for the parts table-
ie. calendarParts tbl:

record imageID showID
81 16 4

record imageID showID
81 16 10

record imageID showID
81 16 10

IF I want to update record 81 with different showIDs, then how do I do this from a select box?

=========================
The Current Insert Page
=========================
<cfquery name="insertit" datasource="#request.dsn#">
INSERT INTO calendar
(eventname, calendardate, eventdescription, eventtype, location, starttime, stampm, endtime, ETAMPM, price, contact, phone, email, website, dateAdded)
VALUES
('#form.eventname#','#DateFormat(form.calendardate, "mm/dd/yy")#','#form.eventdescription#','#form.eventtype#','#form.location#','#form.starttime#','#form.STAMPM#','#form.endtime#','#form.ETAMPM#','#form.price#','#form.contact#','#form.phone#','#form.email#','#form.website#',GETDATE())
</cfquery>

<!--- Pull ID from record that was just inserted --->

<cfquery name="getCalendarID" datasource="#request.dsn#">
SELECT MAX(Calendar_ID) as LastPost
FROM calendar
</cfquery>

<cfloop list="#form.showID#" index="FormshowID">
<cfquery name="insertParts" datasource="#request.dsn#">
INSERT INTO calendarParts
(Calendar_ID, showID, dateAdded)
VALUES
('#getCalendarID.LastPost#', '#FormshowID#', GETDATE())
</cfquery>
</cfloop>
=========================
The Current Update Page:
=========================

<cfquery name="updateit" datasource="#request.dsn#">
UPDATE calendar
SET eventname = '#form.eventname#',
eventdescription = '#form.eventdescription#',
eventtype = '#form.eventtype#',
location = '#form.location#',
price = '#form.price#',
contact = '#form.contact#',
phone = '#form.phone#',
email = '#form.email#',
website = '#form.website#',
dateModified=GETDATE()
WHERE rid = #form.rid#
</cfquery>

<!--- now get the ID of the posting that was just inserted --->
<cfquery name="getcalendar_id" datasource="#request.dsn#">
SELECT MAX(calendar_id) as newID
FROM calendar
</cfquery>

<cfloop list="#form.showID#" index="FormshowID">
<cfquery name="updateParts" datasource="#request.dsn#">
UPDATE calendarParts
SET
calendar_id='#getcalendar_id.newID#',
showID='#FormshowID#',
dateModified=GETDATE()
WHERE calendar_id = #calendar_id#
</cfquery>
</cfloop>

Thanks In Advance.