Getting Wrong ID

[BobbaBuoy]

I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:

If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.

For instance, this code:

sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g "
sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName"
Set rs = conn.Execute(sql)
Do While Not rs.EOF
        lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")"
        rs.MoveNext
    Loop
    Set rs = Nothing

could show the following:
2441-John Doe (10)
2441-John Doe (11)
if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.

I appreciate any help you can give me!

Thanks~

Bobba Buoy
Just trying to make sense of it all ...

 

[Microbe]

Since they both have the same field name, use an AS statement to differentiate them and then it should work.

sql = "SELECT r.RosterID AS theRosterID [/cdode]

Steve Davis
[URL unfurl="true"]http://www.HaHaHa.com.au/weblog[/URL]

NOTE: This sig does not include any reference to voting, stars, or marking posts as helpful as doing so is cause for membership termination.

 

[Microbe]

oh if only:

a) I wasn't so lazy that I previewed my post
b) you could edit your own posts

Since they both have the same field name, use an AS statement to differentiate them and then it should work.

sql = "SELECT r.RosterID AS theRosterID

Steve Davis

http://www.HaHaHa.com.au/weblog

NOTE: This sig does not include any reference to voting, stars, or marking posts as helpful as doing so is cause for membership termination.

 

[BobbaBuoy]

I am confused with the "theRosterID" value. Are you suggesting that I use the roster id in question? I want both of the roster values in the list box but with their own roster id? Am I missing something? (probably )

Bobba Buoy
Just trying to make sense of it all ...

 

[Microbe]

if you use

sql = "SELECT [b]r.RosterID AS rRosterID, g.RosterID AS gRosterID[/b], r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g "
sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName"

You could then say

response.write(" r.rosterID = " &  rs("rRosterID") & " g.rosterID = " & rs("gRosterID"))

to display the value of r.rosterID.

But I just noticed the problem in your original code. Are you including g.rosterID in the recordset?

Steve Davis

http://www.HaHaHa.com.au/weblog

NOTE: This sig does not include any reference to voting, stars, or marking posts as helpful as doing so is cause for membership termination.

 

[johpje]

BobbaBuoy,

There isn't anything wrong with your query. The fact is that there actually exist 2 grades for the given rosterid.

Please verify this by running following query:

SELECT * FROM Grades WHERE RosterID = 2441

I'm quiet sure this will return two records with grade 10 and 11.

cheers,
Johpje

 

[BobbaBuoy]

Yeah, that helped. I got it figured out now. Thanks to all!

Bobba Buoy
Just trying to make sense of it all ...

 

[r937]

bobba, mind telling what it was?

r937.com | rudy.ca

 

[BobbaBuoy]

It was inserting the participant just fine but when it then needed to add a record to the "grades" table to assign a current grade for that participant it was only looking for the record by name (first and last) and once it found it, it inserted another grade for the existing participant rather than inserting a grade for the one I am trying to add. What I had to do was look through the existing roster records and find one that matched based on first name, last name, and gender AND that did NOT have a corresponding record in the "grades" talbe. Once I did that (using a boolean function that returned false if a corresponding record in the "grades" table could not be found) then it entered a new record in the "grades" table for the new participant.

I hope this makes sense...

Bobba Buoy
Just trying to make sense of it all ...