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

Posted on 2006-05-23
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
    LVL 3

    Expert Comment

    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

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Superior storage. Superior surveillance.

    WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

    A web service ( is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
    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…
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now