Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Best Method?

Posted on 2004-10-27
6
Medium Priority
?
156 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
Comment
Question by:Docjayman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 4

Expert Comment

by:eclipse2k
ID: 12419977
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
ID: 12420018
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
eclipse2k earned 400 total points
ID: 12420038
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
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

 
LVL 17

Accepted Solution

by:
Tacobell777 earned 600 total points
ID: 12427025
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
ID: 12427329
@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
ID: 12428424
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

Featured Post

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

636 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