strange. it's the second insert query it's failing on, right, not the first? check the other columns. or show us all the code again.
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: RE: [ cf-dev ] Adding to two tables and inserting a transferring a value 05/02/2004 15:26 Please respond to dev No it's saying "Yes (Duplicates OK)" -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 05 February 2004 15:19 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Adding to two tables and inserting a transferring a value did you set up the FK field to not allow duplicates? check what it's properties are in Access; it's ok for it to be an Index, but it has to allow for duplicate values. 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: RE: [ cf-dev ] Adding to two tables and inserting a transferring a value 05/02/2004 13:15 Please respond to dev Hi Duncan, Still trying to get my head round when to use the single quotes and not but it's slowly sinking in :). Did what you said, and sorry to be a pain, but it's now giving me the following error: Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. It seems to be the FK in the second table that is causing the problem, but why I'm not sure. The TOUR_ID field is setup to take in numbers if that helps. Any ideas what else I should try? Thanks again for all your help. Cheers, James -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 04 February 2004 18:49 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Adding to two tables and inserting a transferring a value I'm assuming your Tour_ID FK in the Tour_dates table is a numeric field. If so, take off the single quotes around '#get_new_TourTable_ID.MaxID#' 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: RE: [ cf-dev ] Adding to two tables and inserting a transferring a value 04/02/2004 16:34 Please respond to dev 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] -- ** 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] -- ** 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]
