Having Trouble Manipulating SQL with VB

[Jokeheads]

So what this sub is supposed to do is read in the top 10 "runners" of a database and display their usernames and amount of runs through a String. The current sub only works for the first runner, the top 1. I'm not sure how to access the other "rows" of the database, the other 9. Any help with that would be much appreciated.

Sub stats_top()
set rs = statsConn.Execute("SELECT TOP 10 `runs`,`username`,`time` FROM `baal` ORDER BY `runs` DESC")

statTopOut="The top ten runners:"

For i = 0 to 9
statUsernames = rs.Fields(1)
statRun = rs.Fields(0)
statTopOut = statTopOut & statUsernames & ": " & statRun & ". "
Next

AddQ statTopOut
End Sub

This sub outputs the first person's stats 10 times.

 

[SkipVought]

Hi,

Use...

Select [b]Distinct[/b] ....

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[gmmastros]

Just before the Next, add RS.MoveNext

Ex:

For i = 0 to 9
statUsernames = rs.Fields(1)
statRun = rs.Fields(0)
statTopOut = statTopOut & statUsernames & ": " & statRun & ". "
[!]RS.MoveNext[/!]
Next

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

duh!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Jokeheads]

That works great. Perfect. Thank you very very much.

 

[gmmastros]

Cool. Now that we've gotten past that, I would like to make a suggestion. The problem I am about to describe may not affect you here (for this query), but could cause you problems later.

Specifically, when you 'select Top 10' from a database, you are not always guaranteed to get 10 records. If there are less than 10 matching records, then you would get less records. For example, if you had a table with 4 records, and you select top 10, you would still only get 4 records (because that is all there is).

What does this really mean? Well, looking at your code, you set up a 'for' loop to process the records. Instead, I would suggest that you use a while loop.

While Not RS.Eof
    statUsernames = rs.Fields(1)
    statRun = rs.Fields(0)
    statTopOut = statTopOut & statUsernames & ": " & statRun & ". "
    RS.MoveNext
Wend

Under normal circumstances, there will be 10 records. There will never be more than 10 records. But there could sometimes be less than 10.

The code I show will accomodate all of these situations. I hope this makes sense.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jokeheads]

That makes perfect sense, you bring up a very good point, but it does not apply in my situation. I appreciate the idea though, for the future.

 

[BobRodes]

George is quite right of course. Now, using the With construct is slightly more efficient (with 10 records, it's not going to make any sort of difference, though):

With rs
   Do Until .Eof
       statUsernames = .Fields(1)
       statRun = .Fields(0)
       statTopOut = statTopOut & statUsernames & ": " & statRun & ". "
       .MoveNext
   Loop
End With

The reason it's more effiicent is because the object pointer is only evaluated once, at the point of the with statement, instead of once each time "rs" occurs in the code.

This would also be the case with a For Next loop.

Bob