RE: Question: Creating a primary key field

[Yager, Brian T Contractor/NCCIM]

Will this work with all databases?

Brian
-----Original Message-----
From: Stuart Miller [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 01, 2001 9:03 AM
To: CF-Talk
Subject: RE: Question: Creating a primary key field


This is how I deal with it:

<cftransaction action="BEGIN">


        <cfquery name="qry_addcontact" datasource="#DSN#">
                INSERT INTO Contact( the fields... )
                        VALUES ( the values... )
        </cfquery>
                                        
        <cfquery name="qry_getnewid" datasource="#DSN#">
                SELECT  MAX(ID) AS TheID
                FROM    ContactInfo
        </cfquery>
        
        <cfquery name="qry_addcontactinfo" datasource="#DSN#">
                INSERT INTO ContactInfo ( ContactID, the fields... )
                        VALUES (#qry_getnewid.TheID#, the values..)
        </cfquery>
        

                <cftransaction action="COMMIT"/>
</cftransaction>

The cftransaction tag will ensure that no other records will be inserted
into these tables while processing the queries between the start and end of
the transaction.

-s

Stuart Miller           
Rocom New Media
t: 01937 487492
e: [EMAIL PROTECTED]    
w: http://www.rocom.co.uk       



-----Original Message-----
From: Shannon Rhodes [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 01, 2001 2:41 PM
To: CF-Talk
Subject: Question: Creating a primary key field


Is it possible to query which was the last ID number created (autonumber
field in Access) for the purpose of adding 1 to explicitly state the new ID
number for a newly created record?

The reason why I ask is that I'm having trouble inserting my new info into
two separate tables because I need the newly created ID number for table A
to be inserted into table B.  If I can't "guess" what the number will be,
I'm stuck trying to take the user through a second page where I insert into
table A then turn around and query the new record to insert the new field
into table B.  That leaves me with the problem of what to do if the user
abandons the application on the second page, leaving no new record created
in table B.

This may sound like screwy application design (and maybe it is, I'm still
pretty new at this), but the problem is that table A contains contact
information, while table B contains all other information (class
registration, payment methods, etc), and they have a one-to-one relationship
because I may need to change which person the info is associated with (it's
possible that a person will register and pay for a class, but a co-worker
will attend in their place).  I can't make the classID a primary key in
table B either (the person could decide to switch classes) so I just have an
autonumber as a primary key.  Argh!  All suggestions welcomed.

TIA,
Shannon
[EMAIL PROTECTED]



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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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