Inserting into 2 tables with a relationship
I have a database with 2 tables one has a relationship with the other and when I do an insert I need to insert into table 1 and insert into table 2 with an var_id field that has not been generated until table 1 has been inserted into.. I am not sure what to do or how to do it.. I am using CF 5 and Access.. Steven Lancaster Barrios Technology NASA/JSC 281-244-2444 (voice) [EMAIL PROTECTED] __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Inserting into 2 tables with a relationship
Use CFTRANSACTION so if the action fails you can rollback. HTH Donnie Bachan Phone: (718) 217-2883 ICQ#: 28006783 Nitendo Vinces - By Striving You Shall Conquer == The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Inserting into 2 tables with a relationship
The problem I am having is that it adds the variable to the field, but it won't update the var_id that has the relationship with the main table it adds a 0 in the field.. Steven Lancaster Barrios Technology NASA/JSC 281-244-2444 (voice) [EMAIL PROTECTED] -Original Message- From: Donnie Bachan [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:53 AM To: CF-Talk Subject: Re: Inserting into 2 tables with a relationship Use CFTRANSACTION so if the action fails you can rollback. HTH Donnie Bachan Phone: (718) 217-2883 ICQ#: 28006783 Nitendo Vinces - By Striving You Shall Conquer == The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Inserting into 2 tables with a relationship
Okay let's say that you are incrementing the primary key in the first table upon an insert, and then you want to insert the value of that key into the second table as var_id, what you have to do is after you did the first insert, you need to select max(variable) as new_var_id from table1 This is done to get the value of the primary key that was created on the insert, there is no other way to get that value. Then you insert new_var_id as var_id into table2 I hope this is what you are looking for. I assume the reason it is inserting 0 is because you have the field set to default to zero when the value is not passed. HTH Donnie Bachan Phone: (718) 217-2883 ICQ#: 28006783 Nitendo Vinces - By Striving You Shall Conquer == The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Inserting into 2 tables with a relationship
If you are using SQL server and are within a transaction you can use SELECT @@identity AS newid FROM table It holds the value of the last id inserted in the table for that transaction Kevin -Original Message- From: Donnie Bachan [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 1:59 PM To: CF-Talk Subject: RE: Inserting into 2 tables with a relationship Okay let's say that you are incrementing the primary key in the first table upon an insert, and then you want to insert the value of that key into the second table as var_id, what you have to do is after you did the first insert, you need to select max(variable) as new_var_id from table1 This is done to get the value of the primary key that was created on the insert, there is no other way to get that value. Then you insert new_var_id as var_id into table2 I hope this is what you are looking for. I assume the reason it is inserting 0 is because you have the field set to default to zero when the value is not passed. HTH Donnie Bachan Phone: (718) 217-2883 ICQ#: 28006783 Nitendo Vinces - By Striving You Shall Conquer == The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Inserting into 2 tables with a relationship
I suggest using the custom tag... Max_ID from the Macromedia Custom Tag library. It is well documented. Then search the archives for discussions on use of the tag. I use this exclusively now and avoid AutoIncrementing primary key fields like the plague. hth Brian At 10:49 AM 5/16/02 -0500, you wrote: I have a database with 2 tables one has a relationship with the other and when I do an insert I need to insert into table 1 and insert into table 2 with an var_id field that has not been generated until table 1 has been inserted into.. I am not sure what to do or how to do it.. I am using CF 5 and Access.. Steven Lancaster Barrios Technology NASA/JSC 281-244-2444 (voice) [EMAIL PROTECTED] __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Inserting into 2 tables with a relationship
Why would you avoid that out of curiosity? Auto incrementing new fields pretty well guarantees that you won't accidentally cause a duplicate. It's quite the standard practice out there from what I understand... Honestly, you should really be using GUID anyways for most new databases anyways, just in case you need to add replication into the mix. Microsoft themselves are really pusing guids now instead of auto-incrementing fields... Only thing is that that guids are REALLY Long... -Original Message- From: Brian Scandale [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 12:16 PM To: CF-Talk Subject: Re: Inserting into 2 tables with a relationship I suggest using the custom tag... Max_ID from the Macromedia Custom Tag library. It is well documented. Then search the archives for discussions on use of the tag. I use this exclusively now and avoid AutoIncrementing primary key fields like the plague. hth Brian At 10:49 AM 5/16/02 -0500, you wrote: I have a database with 2 tables one has a relationship with the other and when I do an insert I need to insert into table 1 and insert into table 2 with an var_id field that has not been generated until table 1 has been inserted into.. I am not sure what to do or how to do it.. I am using CF 5 and Access.. Steven Lancaster Barrios Technology NASA/JSC 281-244-2444 (voice) [EMAIL PROTECTED] __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Inserting into 2 tables with a relationship
Steven,
You can use the below query. This only works if TableA has an IDENTITY
field.
cfquery
Insert into Table1 values('some','some')
SELECT @@Identity as NewID
/cfquery
JOe
-Original Message-
From: Brian Scandale [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 16, 2002 3:16 PM
To: CF-Talk
Subject: Re: Inserting into 2 tables with a relationship
I suggest using the custom tag... Max_ID from the Macromedia Custom Tag
library. It is well documented. Then search the archives for discussions on
use of the tag.
I use this exclusively now and avoid AutoIncrementing primary key fields
like the plague.
hth
Brian
At 10:49 AM 5/16/02 -0500, you wrote:
I have a database with 2 tables one has a relationship with the other and
when I do an insert I need to insert into table 1 and insert into table 2
with an var_id field that has not been generated until table 1 has been
inserted into.. I am not sure what to do or how to do it.. I am using CF 5
and Access..
Steven Lancaster
Barrios Technology
NASA/JSC
281-244-2444 (voice)
[EMAIL PROTECTED]
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
