p2p.wrox.com Forums

Need to download code?

View our list of code downloads.

  Return to Index  

asp_databases thread: Fetches a record set but won't display it! (ASP/VBScript+MySQL)

Message #1 by "David Lundin" <lists@s...> on Mon, 3 Dec 2001 23:31:00 -0000
Dear all,

I have another problem whilst converting my ASP/VBScripts from using an

Access database to using a MySQL one.

In the old application I had a script that would display a top 100 list

of the poems that have the most user votes. It worked fine before

(Access db) but now (MySQL db) there's just nothing.

I think the SQL works, I have queried the database and got the correct

output in WinSQL. But the record set below has RecordSet.BOF = True

right after having fetched the data from the database. Why is this? Any


(The database query even seems to take time, why I suspect that the

engine rustles up the correct record set but there is something wrong in

my script.)


Set PoemSet = Server.CreateObject("ADODB.Recordset")

Visa = "SELECT poems.ID, poems.name, COUNT(*) AS votes " &_

"FROM (users INNER JOIN poems ON users.vote = poems.ID) " &_

"WHERE poems.authorized = 1 " &_

"GROUP BY poems.ID " &_

"ORDER BY votes DESC, poems.name " &_

"LIMIT 100"

PoemSet.Open Visa, Connect, adOpenForwardOnly, adLockOptimistic


Dim Counter, LastNoOfVotes, LastCounter

Counter = 1

LastNoOfVotes = -1


Do Until PoemSet.EOF

If Not LastNoOfVotes = PoemSet("votes") Then

 LastNoOfVotes = PoemSet("votes")

 LastCounter = Counter

End If



<td<% If Counter Mod 2 = 0 Then %> bgcolor="#e9f0f2"<% End If %>><font

class="text"><img src="pic/poem_sml.gif" align="middle">&nbsp;<a

href="showpoem.asp?ID=<% =PoemSet("ID") %>"><% =PoemSet("name")


<td<% If Counter Mod 2 = 0 Then %> bgcolor="#e9f0f2"<% End If %>><font

class="text"><% =PoemSet("votes") %></font></td>

<td<% If Counter Mod 2 = 0 Then %> bgcolor="#e9f0f2"<% End If %>><font

class="text"><% =LastCounter %></font></td>




Counter = Counter + 1




Set PoemSet = Nothing


Thanks a load for your help!


  Return to Index