Hi again,
... I've just tried your suggestion Duncan and although I'm getting a result
it's not the right one. I'm also getting an error:
ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
expression.
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (69:5) to (69:79) (THIS IS
Add_DateTable_Record).
Here's the new code I've just added:
<cfquery
datasource="#application.datasource#" name="get_new_TourTable_ID">
SELECT MAX(Tour_ID) AS MaxID
FROM Tour_data
</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#'
,'#get_new_TourTable_ID.MaxID#' )
</cfquery>
I've done a CFOUTPUT of the get_new_TourTable_ID and I'm currently getting
the result 584 (which increments as well if you refresh). The problem with
that is that my P.Keys only goes up to 581 so where is it getting the 584(+)
from? I've looked at the DB as well and nothing is being added to Table 1
(which is before the queries above) but I'm assuming, since I've now put
everything in a CFTRANSACTION, is because the other queries are throwing up
errors.
Any suggestions on this would be great.
Cheers again,
James
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: 04 February 2004 14:57
To: [EMAIL PROTECTED]
Subject: Re: [ cf-dev ] Adding to two tables and inserting a transferring a
value
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]
--
** 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]
