Re: [ cf-dev ] Adding to two tables and inserting a transferring a value

[EWS]

Wicked....!!

Did not know that. I knew being on these lists would teach me something 
new!!

On Wed, 4 Feb 2004 23:27:49 -0000, Snake Hollywood 
<[EMAIL PROTECTED]> wrote:

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]