You can get @@ identity without an SP, just do another select right after the insert
Select @@identity from table > -----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]
