• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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>  
0
Docjayman
Asked:
Docjayman
  • 4
  • 2
2 Solutions
 
eclipse2kCommented:
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
0
 
eclipse2kCommented:
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
0
 
eclipse2kCommented:
Another hint:

why dont you insert at first the address, then the highschool, and the athlete in the end?

------------------------
<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="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="AthleteInfo" datasource="CareersSQL">
INSERT INTO CRCathlete (firstname, lastname, parent1, parent2, birthday, graddate, gradyear,
          addressID, highschoolID, 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#, #HighschoolIDSearch.LatestHighschoolID#, '#Trim(form.email)#', '#Trim(form.password)#', '#Trim(form.gender)#',  '#Trim(form.sport)#')
</cfquery>
 
------------------------


eclipse2k
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Tacobell777Commented:
no no, you don't use IDENT_CURRENT that takes the last id from ANY session, use SCOPE_IDENTITY() which takes the last id generated from the current session..

<cfquery name="qInsert" datasource="CareersSQL">
DECLARE @lastID INT

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)#')

SET @lastID = SCOPE_IDENTITY()

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)#',
     @lastID, '#Trim(form.email)#', '#Trim(form.password)#', '#Trim(form.gender)#',  '#Trim(form.sport)#')

 SELECT AthleteID
      FROM CRCathlete
        WHERE addressID = @lastID

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)#')

SET @lastID = SCOPE_IDENTITY()

Update CRCathlete
Set highschoolID = @lastID
WHERE    athleteID = #IDSearch.AthleteID#
</cfquery>  
0
 
eclipse2kCommented:
@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
0
 
Tacobell777Commented:
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..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now