How do you create a new record in two tables where the information is linked from a single form

Posted on 2006-05-23
Medium Priority
Last Modified: 2013-12-24
I am new to cold fusion and using cfmx. The data structure is company information in one table and addresses in another. I want to be able to input a new company with addresses from one form.
Question by:davlin2

Expert Comment

ID: 16744010
Do you have some sort of ID field relating the two tables? if so you could write two insert statements the first one would populate the first table Then I would query that table to get the relating id number and insert the rest of the data including the relating id ito table two. THis will make it easy to refrense the related info by filtering by id in your where statement.

Hope that helps

Accepted Solution

EdgeHelpdesk earned 150 total points
ID: 16745886
You will need to run a series of queries to update the database properly (you can either use the cfquery or cfinsert tag) - my recommendation (pseudocode) would be as follows:

- (assumption) the companyInfo table is the "master" table and contains a unique ID, and that ID is an autoincrement integer
- use cflock so that no other process can update the companyInfo table
- insert the data into the companyInfo table
- get the max unique ID from the companyInfo table
- un-cflock the companyInfo table
- (assumption) the companyAddress table may contain more than one address for any entry in the companyInfo table and each address has a unique ID, but also contains a foreign key that links to a record in the companyInfo table
- insert the data and the max unique ID of the previous query into the companyAddress table

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question