linking parent child failing

[mickeyg]

I create the parent record (tblAngler) correctly but am not creating the child records (tblSponsor and tblSponsorLink). Am I refinding my record correctly? Is it not there yet--scoping?

Thank you for your assistance,
Mickey

Code:
<!-- make sure Angler does not already exist -->
<cfif qverifyAngler.RecordCount EQ 0 >
<!--- insert a new record into the Angler table --->
<cfquery name="MemberAdd" datasource="#wifomdsn#"dbtype="ODBC">
INSERT INTO tblAngler (FirstName, LastName,City,State,Division,RookieYear,FavLure,FavWater,CreateIPAddress)
VALUES ('#Form.FirstName#','#Form.LastName#','#Form.Hometown#','#Form.Homestate#','#Form.Division#','#Form.RookieYear#','#Form.LureTechnique#','#Form.BodyofWater#','#myURL#')
</cfquery>

<!--- get recid of created Angler record --->
<cfquery name="qrefindAngler" datasource="#wifomdsn#"dbtype="ODBC">
SELECT AnglerID
FROM tblAngler
WHERE tblAngler.FirstName = '#Form.FirstName#' AND
tblAngler.LastName = '#Form.LastName#' AND
tblAngler.Division = '#Form.Division#'
</cfquery>
<cfif qrefindAngler.RecordCount EQ 1>
<cfset local_AnglerID='#qrefindAngler.AnglerID#'>
<!--- create Sponsor records --->
<cfif #Form.SponsorName1# IS NOT "">
INSERT INTO tblSponsor (Name,WebSite)
VALUES ('#Form.SponsorName1#','#Form.SponsorWebsite1#')
</cfif>

<!--- get recid of created Sponsor record --->
<cfquery name="qrefindSponsor" datasource="#wifomdsn#"dbtype="ODBC">
SELECT SponsorID
FROM tblSponsor
WHERE tblSponsor.Name = '#Form.SponsorName1#' AND
tblSponsor.WebSite = '#Form.SponsorWebsite1#'
</cfquery>
<cfif qrefindSponsor.RecordCount EQ 1>
<cfset local_SponsorID='#qrefindSponsor.SponsorID#'>
<!--- create Angler/Sponsor link records--->
INSERT INTO tblSponsorLink (AnglerID, SponsorID)
VALUES ('#local_AnglerID#','#local_SponsorID#')
</cfif>
</cfif>

</cfif>

 

[r937]

several problems

what happens if the angler already exists? the sponsor and link rows are not inserted

what happens if you happen to insert the same angler (firstname, lastname, division) more than once? then query recordcount will be >1, and again the sponsor and link rows are not inserted

what happens if you insert the same sponsor more than once?

and so on

the method of inserting a row and then querying it back based on its inserted column values in order to get the id number (autonumber) is a solid technique, and you can and should use it, but it requires that you put a unique constraint on the inserted columns (the "real" primary key)

also, break your actions down, don't put everything inside the CFIF for inserting the angler

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)