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
