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

ideas?



(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

%>



<tr>

<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")

%></a>&nbsp;&nbsp;</font></td>

<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>

</tr>



<%

PoemSet.MoveNext

Counter = Counter + 1

Loop

   

PoemSet.Close

Set PoemSet = Nothing

%>





Thanks a load for your help!

David




  Return to Index