Exception occuring

[emozley]

I have a SQL statement that returns a recordset. I am trying to display this as a list but I don't want a record to be repeated if value for MeetingID is repeated.

So far I have:

Do While Not TBL.EOF
LastMeetingID=TBL("MeetingID")
Response.Write("<tr>")
Response.Write("<td><font face='verdana' size='2'>") & TBL("Title") & "</font></td>"
Response.Write("<td><font face='verdana' size='2'>") & TBL("O.FirstName") & " " & TBL("Surname") & "</font></td>"
Response.Write("<td><font face='verdana' size='2'>") & TBL("RoomID") & "</font></td>"
Response.Write("<td><font face='verdana' size='2'>") & TBL("Date") & "</font></td>"
Response.Write("<td><font face='verdana' size='2'>") & TBL("StartTime") & "</font></td>"
Response.Write("</tr>")

Do While TBL("MeetingID")=LastMeetingID And Not TBL.EOF
TBL.MoveNext
Loop

counter=counter+1

Loop

I am getting an exception though at the line

Do While TBL("MeetingID")=LastMeetingID And Not TBL.EOF

Is there a better way round it than the way I am attempting?

Thanks

Ed

 

[tsdragon]

Could you not screen out duplicate meeting ids in the SQL statement (using DISTINCT)?

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[emozley]

For some reason the distinct function isn't working (unless I'm not using it properly). It might be because I'm using aliasing or the structure of the join statement...

I've got:

SELECT DISTINCT Meetings.MeetingID, Meetings.Title, Meetings.RoomID, Meetings.Date, Meetings.StartTime, Attendees.UserID, O.FirstName, O.Surname, A.FirstName FROM ((Meetings LEFT JOIN Attendees ON Attendees.MeetingID=Meetings.MeetingID) LEFT JOIN Users AS O ON Meetings.OrganiserID=O.UserID) LEFT JOIN Users AS A ON Attendees.UserID=A.UserID

Any ideas?

Thanks

 

[vongrunt]

Some other column used in SELECT does not repeat...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[emozley]

Here's what the query returns (I have added A.Surname to the original query). Is there any way of making the DISTINCT statement only look at MeetingID? eg I am organising MeetingID 78 and there are 3 people attending but I only want the meeting to be listed once.

MeetingID Title RoomID Date StartTime UserID O.FirstName O.Surname A.FirstName A.Surname

78 Important Meeting 1 02/12/2005 08:00:00 98 Ed Mozley Scott Baggley

78 Important Meeting 1 02/12/2005 08:00:00 112 Ed Mozley Tracey Latteman

78 Important Meeting 1 02/12/2005 08:00:00 124 Ed Mozley Ed Mozley

79 Important Meeting 4 05/12/2005 09:00:00 Ed Mozley

80 Test Meeting 3 12/12/2005 09:00:00 31 Ed Mozley Elizabeth Petch

81 Maeve's Meeting 2 13/12/2005 08:30:00 37 Maeve Laverty Hilary Wortley

 

[DotNetGnat]

change the code at the ASP level to make the display to look whatever you want to...

-DNG

 

[emozley]

This was my original plan - eg:

1. Assign TBL("MeetingID") to LastMeetingID
2. Display row of recordset
3. Move to next record
Is new MeetingID=LastMeeting?
If so go back to start of step 3.
Else go back to step 1.

Problem occurs when I try to make the comparison in step 3:

Is new MeetingID=LastRecord

because I have moved off the end of the recordset it cannot compare. I have tried:

Do While TBL("MeetingID")=LastMeetingID And Not TBL.EOF
TBL.MoveNext
Loop

for my step 3 but the And Not TBL.EOF is not trapping the end of the recordset. I am going to try a a WEND loop and see if that works.

Do While TBL("MeetingID")=LastMeetingID And Not TBL.EOF
TBL.MoveNext
Loop

for

 

[DotNetGnat]

try this instead....

If Not TBL.EOF Then
IF LastMeetingID<>TBL("MeetingID") THEN
Exit Do
End if

-DNG

 

[emozley]

Perfect thanks.

Just out of interest do you know there is a good code editor that will execute the script in a sort of simulation mode with break points and variable watches etc.

At the moment I use Source Edit - a plain text editor with a few extra features eg colour coding and line numbers - which is great but is limited in some respects.

cheers

Ed

 

[DotNetGnat]

no problem glad to help...

Visual Interdev is what you are looking for...BUT its not free...

-DNG