Problem with two record sets for a Message Board 1

[SlakeB]

I am building a message board application, using an Access DB. The users can view all the main posts, and by clicking on one, they can view all of the subthreads for that post. On the ASP page displaying the main post, and the sub posts, I want a picture of the person who made the main post.

On this page I have two record sets. One (objRS3) is the table that contains all of the member information (including 'UserName', and the URL of their 'Photo'). The other RS (ObjRS) is the table that contains the data about each of the Main Posts. One of the fields in this table is 'Author', and it corresponds to a 'UserName' from the other table.

I'm trying to write code that goes through objRS3 until it finds a match between the author of the post, and the username of a member. Then I want it to assign that member's photo url to 'sMainAuthorPic'. The code below seems to find the correct UserName, but not the correct photo (the photo URL keeps going to the default value, instead of the value assigned to that user)

What am I doing wrong?

Do While not bFoundMainAuthor
		If objRS("Author") = objRS3("UserName") Then
			bFoundMainAuthor = true
			sMainAuthorPic = objRS3("Photo")
		End If
		
		If not objRS3.EOF and not bFoundMainAuthor then
			objRS3.MoveNext
		End If
	Loop

 

[DotNetGnat]

i would rather use a single recordset object and do a join in the sql query...it would be more efficient.

-DNG

 

[DotNetGnat]

something like this:

SELECT T1.MemId, T1.MemName, T1. MemPic, T2.MemPost FROM Table Member T1 INNER JOIN POSTS T2 ON T1.MemId=T2.MemId

-DNG

 

[SlakeB]

Is it possible to Join more than two tables in a SQL statement?

 

[DotNetGnat]

yes...

SELECT T1.field, T2.field, T3.field
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID=T2.ID
INNER JOIN Table3 T3 ON
T2.ID=T3.ID

Actually it depends on how you want to join...i just showed an example...

-DNG

 

[Sheco]

Select A.Field1, B.Field2, C.Field3
From ThisTable A INNER JOIN ThatTable B
ON A.ID = B.ForiegnKey
LEFT JOIN TheOtherTable C
ON A.ForiegnKey = C.ID

 

[Sheco]

bleh, i was too slow