after the first query, have another query that selects the highest ID, i.e.
the most recently added record.
<cfquery name="getMax">
SELECT MAX(ID) AS MaxID
FROM Tour_data
</cfquery>
Then use this as your value in the 2nd update query for the FK.
#getMax.MaxID#
Wrap all the queries up in a CFTransaction.
Duncan Cumming
IT Manager
http://www.alienationdesign.co.uk
mailto:[EMAIL PROTECTED]
Tel: 0141 575 9700
Fax: 0141 575 9600
Creative solutions in a technical world
----------------------------------------------------------------------
Get your domain names online from:
http://www.alienationdomains.co.uk
Reseller options available!
----------------------------------------------------------------------
----------------------------------------------------------------------
James
Buckingham To: "'[EMAIL PROTECTED]'" <[EMAIL
PROTECTED]>
<[EMAIL PROTECTED] cc:
a.com> Subject: [ cf-dev ] Adding to two tables
and inserting a transferring
a value
04/02/2004
13:58
Please
respond to
dev
Afternoon guys,
I'm currently doing some work on a new CMS for one of our clients and I've
hit a bit of hiccup with some SQL I'm trying to put together. The database
is setup as a one-2-many relationship. Table 1 has the names and details of
the client's tours while table 2 holds all the dates and fares related to
each of the tours. Both tables are connected via the Tour table primary key
Tour_ID which is an autonumber field.
When I tested the new form it inserts all the information into the DB fine,
apart from one thing. What I need to do when adding the new information is
get the newly generated primary key of Table 1 and insert this along with
all the new information in Table 2 to ensure the relationship is setup.
Am I thinking along the right lines here?
Just in case here's my code so far:
<cfquery datasource="#application.datasource#"
name="add_TourTable_record">
INSERT INTO Tour_data
( title
,subtitle
,resorttitle
,resort
,description
,bulletlist
,numberofdays
,departsfrom
,returnsto
,farefrom
,supplements
,region
,type_of_tour
,miscellaneous
,pic )
VALUES('#form.TitleTxt#'
,'#form.SubtitleTxt#'
,'#form.ResortTitleTxt#'
,'#form.ResortTxt#'
,'#form.descriptionTxt#'
,'#form.BulletlistTxt#'
,'#form.NoOfDaysTxt#'
,'#form.DepartsFromTxt#'
,'#form.ReturnsToTxt#'
,'#form.FareFromTxt#'
,'#form.SupplementTxt#'
,'#form.Region_List#'
,'#url.Type#'
,'#form.MiscTxt#'
, 'no pic')
</cfquery>
<cfquery datasource="#application.datasource#" name="add_DateTable_record">
<cfset Departure_Date = "##" & form.DepartureDateTxt & "##">
<cfset Return_Date = "##" & form.ReturnDateTxt & "##">
INSERT INTO Tour_dates
( departuredates
,returndates
,fares
,Tour_ID
)
VALUES ('#Departure_Date#'
,'#Return_Date#'
,'#form.FaresTxt#' )
</cfquery>
Hope that makes sense!!
Cheers,
James
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
