Hi guys, ...and thanks for the quick replies. Sorry I forgot to mention that the DB is in Access. I'll give your tips a go just now and let you know how it goes.
Cheers, James -----Original Message----- From: ellwood [mailto:[EMAIL PROTECTED] Sent: 04 February 2004 15:01 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Adding to two tables and inserting a transferring a value Or getting the MAX(ID) -----Original Message----- From: ellwood [mailto:[EMAIL PROTECTED] Sent: 04 February 2004 14:59 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Adding to two tables and inserting a transferring a value If you are using SQL Server then do it as a stored procedure and then get the @@IDENTITY variable straight after the first insert. otherwise it is a case of selecting the record from the database with the same info you passed into it such as SELECT * FROM TABLE WHERE title = **** AND subtitle = **** etc etc -----Original Message----- From: James Buckingham [mailto:[EMAIL PROTECTED] Sent: 04 February 2004 13:59 To: '[EMAIL PROTECTED]' Subject: [ cf-dev ] Adding to two tables and inserting a transferring a value 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] -- ** 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] -- ** 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]
