asked on

Best Method?

Okay, a brief summary 1st. I have one form inserting into 3 tables(athlete, highschooladdress, athleteaddress), the prefix on those tables is CRC. The athlete table is the parent table and the other two tables contain address information. What is the best way to get the primary keys of the 2 address tables into the athlete table as foriegn keys? Currently I am using the '@@identity' coding the best I can, but there has to be a better way to do it.

Also, all the code below works except the last update statement. I can't get the athleteid from the athlete table, which Selected in the 'IDSearch' query, to be recognized. If I can get that to work my coding may have a shot at working correctly. Any ideas?? Thanks...

<cfquery name="AthleteEnterAddress" datasource="CareersSQL">
INSERT INTO CRCathleteAddress (address, address2, city, State, Zip, HomePhone, OtherPhone )
            Values('#Trim(form.address)#', '#Trim(form.address2)#', '#Trim(form.city)#',
            '#Trim(form.state)#', '#Trim(form.zip)#', '#Trim(form.phone)#', '#Trim(form.otherphone)#')
</cfquery>

<cfquery name="AthleteInfo" datasource="CareersSQL">
INSERT INTO CRCathlete (firstname, lastname, parent1, parent2, birthday, graddate, gradyear,
            addressID, email, password, gender, sport)
      Values('#Trim(form.first)#', '#Trim(form.last)#', '#Trim(form.parent1)#',
      '#Trim(form.parent2)#', '#Trim(form.birthday)#', '#Trim(form.graduation)#', '#Trim(form.gradyear)#',
      @@identity, '#Trim(form.email)#', '#Trim(form.password)#', '#Trim(form.gender)#', '#Trim(form.sport)#')
</cfquery>

<cfquery name="IDSearch" datasource="CareersSQL">
SELECT AthleteID
FROM CRCathlete
WHERE addressID = @@identity
</cfquery>

<cfquery name="AthleteEnterAddress" datasource="CareersSQL">
INSERT INTO CRCHighSchool (name, phone, address, address2, city, State, Zip)
            Values('#Trim(form.highschool)#', '#Trim(form.hsphone)#', '#Trim(form.hsaddress)#',
            '#Trim(form.hsaddress2)#', '#Trim(form.hscity)#', '#Trim(form.hsstate)#', '#Trim(form.hszip)#')
</cfquery>

<cfquery name="AthleteEnterSchoolID" datasource="CareersSQL">
Update CRCathlete
Set highschoolID = @@identity
WHERE athleteID = #IDSearch.AthleteID#
</cfquery>

eclipse2k

Hi there!

To get the latest ID, you should use: IDENT_CURRENT instead of @@IDENTITY:

For Example:

------------------------
<cfquery name="IDSearch" datasource="CareersSQL">
SELECT IDENT_CURRENT('CRCathlete') AS 'LatestAthleteID'
</cfquery>
------------------------

Don't use @@IDENTITY, but replace all occurances to the proper IDENT_CURRENT queries

eclipse2k

eclipse2k

this might work:

------------------------
<cfquery name="AthleteEnterAddress" datasource="CareersSQL">
INSERT INTO CRCathleteAddress (address, address2, city, State, Zip, HomePhone, OtherPhone )
Values('#Trim(form.address)#', '#Trim(form.address2)#', '#Trim(form.city)#',
'#Trim(form.state)#', '#Trim(form.zip)#', '#Trim(form.phone)#', '#Trim(form.otherphone)#')
</cfquery>

<cfquery name="AddressIDSearch" datasource="CareersSQL">
SELECT IDENT_CURRENT('CRCathleteAddress') AS 'LatestAddressID'
</cfquery>

<cfquery name="AthleteInfo" datasource="CareersSQL">
INSERT INTO CRCathlete (firstname, lastname, parent1, parent2, birthday, graddate, gradyear,
addressID, email, password, gender, sport)
Values('#Trim(form.first)#', '#Trim(form.last)#', '#Trim(form.parent1)#',
'#Trim(form.parent2)#', '#Trim(form.birthday)#', '#Trim(form.graduation)#', '#Trim(form.gradyear)#',
#AddressIDSearch.LatestAddressID#, '#Trim(form.email)#', '#Trim(form.password)#', '#Trim(form.gender)#', '#Trim(form.sport)#')
</cfquery>

<cfquery name="AthleteIDSearch" datasource="CareersSQL">
SELECT IDENT_CURRENT('CRCathlete') AS 'LatestAthleteID'
</cfquery>

<cfquery name="AthleteEnterAddress" datasource="CareersSQL">
INSERT INTO CRCHighSchool (name, phone, address, address2, city, State, Zip)
Values('#Trim(form.highschool)#', '#Trim(form.hsphone)#', '#Trim(form.hsaddress)#',
'#Trim(form.hsaddress2)#', '#Trim(form.hscity)#', '#Trim(form.hsstate)#', '#Trim(form.hszip)#')
</cfquery>

<cfquery name="HighschoolIDSearch" datasource="CareersSQL">
SELECT IDENT_CURRENT('CRCHighSchool') AS 'LatestHighschoolID'
</cfquery>

<cfquery name="AthleteEnterSchoolID" datasource="CareersSQL">
Update CRCathlete
Set highschoolID = #HighschoolIDSearch.LatestHighschoolID#
WHERE athleteID = #AthleteIDSearch.LatestAthleteID#
</cfquery>
------------------------

eclipse2k

SOLUTION

eclipse2k

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Tacobell777

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eclipse2k

@Tacobell

Can SCOPE_IDENTITY() be used in a different CFQuery?

But you might be right, although i read some articles (only one?) about IDENT_CURRENT in ColdFusion
with the conclusion that you can get the last ID when using CFTransaction AND CFLock around
the CFQuery Block to ensure that you get the real ID. So i guess this might work, too...

CFLock: http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-p71.htm#wp1100787
CFTransaction: http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-c15.htm#wp1104164

eclipse2k

Tacobell777

I am not one 100% sure, but I would be inclined to say no, I know that with IDENT_CURRENT you could get the wrong ID if you do not use locking etc.. IDENT_CURRENT returns the last id for that table, but in between the request another record could have been inserted..