Solved

Best Method?

Posted on 2004-10-27
141 Views
Last Modified: 2013-12-24
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
Question by:Docjayman
    6 Comments
     
    LVL 4

    Expert Comment

    by: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
    0
     
    LVL 4

    Expert Comment

    by: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
    0
     
    LVL 4

    Assisted Solution

    by:eclipse2k
    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
     
    LVL 17

    Accepted Solution

    by:
    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
     
    LVL 4

    Expert Comment

    by: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
    0
     
    LVL 17

    Expert Comment

    by: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..
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Prepare to Pass the CompTIA A+ 900 Series Exam

    CompTIA aims to adapt its A+ Certification to reflect the most current knowledge and skills needed by today's IT professionals--and this year's 2016 exam is harder than ever. This certification is one of the most highly-respected and sought after in IT.

    Article by: kevp75
    Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
    Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now