IF you do your ownerid table insert first, then use:
SELECT SCOPE_IDENTITY() as varName
To get the new ownerid, you can use that in the insert for the date
table.
Here is a bit of the insert code I use:
<cfquery datasource="PhysicianAccess" name="InsertPhysicianInfo">
INSERT INTO dbo.PhysicianInfo (FName, LName, MI, PrefFName, Degree,
DictationNo, TeachingNo, ExpirationDate, GroupID, SupervisorID,
SecurityQ, SecurityA) VALUES (
<cfif IsDefined("Session.Sect1F") AND #Session.Sect1F# NEQ ''>
'#Session.Sect1F#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect1L") AND #Session.Sect1L# NEQ ''>
'#Session.Sect1L#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect1M") AND #Session.Sect1M# NEQ '' OR
#Session.Sect1M# NEQ 0>
'#Session.Sect1M#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect1PF") AND #Session.Sect1PF# NEQ ''>
'#Session.Sect1PF#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect1D") AND #Session.Sect1D# NEQ ''>
'#Session.Sect1D#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect1DN") AND #Session.Sect1DN# NEQ ''>
'#Session.Sect1DN#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect1TN") AND #Session.Sect1TN# NEQ ''>
'#Session.Sect1TN#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect1ED") AND #Session.Sect1ED# NEQ ''>
'#Session.Sect1ED#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect4ID") AND #Session.Sect4ID# NEQ ''>
'#Session.Sect4ID#'
<cfelseif IsDefined("InsertGroupInfo.GroupInfoID") AND
#InsertGroupInfo.GroupInfoID# NEQ ''>
'#InsertGroupInfo.GroupInfoID#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect2ID") AND #Session.Sect2ID# NEQ ''>
'#Session.Sect2ID#'
<cfelseif IsDefined("InsertContactInfo.ContactInfoID") AND
#InsertContactInfo.ContactInfoID# NEQ ''>
'#InsertContactInfo.ContactInfoID#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect1Q") AND #Session.Sect1Q# NEQ ''>
'#Session.Sect1Q#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("Session.Sect1A") AND #Session.Sect1A# NEQ ''>
'#Session.Sect1A#'
<cfelse>
NULL
</cfif>
)
SELECT SCOPE_IDENTITY() as PhysicianInfoID
</cfquery>
You would then be able to reference the new id as:
#InsertPhysicianInfo.PhysicianInfoID#
Does that make sense?
-----Original Message-----
From: Robert Makowski [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 01, 2006 10:04 AM
To: CF-Newbie
Subject: inserting into two different tables
I have a fairly basic form that I am submitting. It's a simple add
form: first, last name, address, email, etc., but there is a date field
that I'm putting into another table. The date table has a bunch of date
fields, and the foreign key is the ownerid from the other table. The
problem is that I'm creating a new owner with a new id, so I can't just
to two inserts, because I the owenerid hasn't been created yet. Could
someone help me with this?
Thanks.
Rob Makowski
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2179
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
