Small Code Problem

I am inserting the entire query just so all the information is there.  The problem at the very end of the code where I have the two UPDATE statements I can't get the value of the 'athleteId' that was selected in the SELECT statement just above.  Basically what is happening is that it is setting the same highschoolID for every single record in the database because athleteid = athleteid for all of them.  I tried using (Where athleteid = qInsert.AthleteID ) but that didn't work.  What should I change?

<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, ReferredBy)
     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)#',
       '#Trim(form.agent)#')

 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 = AthleteID

Update CRCathlete
Set  agentid = athleteID + 12000
WHERE    athleteID = AthleteID
</cfquery>
DocjaymanAsked:
Who is Participating?
 
pinaldaveCommented:
hi that will not work... but as I can see you are very good with SQL.
what youc an do is that... youc an declare another int like
declare @thevar int

how in the select you can do like this
 SELECT (set @thevar = AthleteID)
      FROM CRCathlete
        WHERE addressID = @lastID

I am not good here with code but I am givving you idea...
now when you have to update... do something like this ..

Update CRCathlete
Set highschoolID = @lastID
WHERE    athleteID =@thevar

This does not work when you select in just above...what you will have to do is run the query just above this query then you wil lhave the value... otherwise... till all the results are executed CFQUERY have no answer...
Regards,
---Pinal
0
 
DocjaymanAuthor Commented:
I hear ya, I was thinking the same thing, but  I am having a little problem getting the var to equal AthleteID though.  
0
 
pinaldaveCommented:
well then just ask this question in the TA of MSSQL and they will be able to quickly answer your question.
Regards,
---Pinal
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.

All Courses

From novice to tech pro — start learning today.