superlative
asked on
Select 3 random rows from dataset in ASP
Hi,
I have the following asp script that reads from a ms sql database.
It currently returns the data for all rows.
Can somebody please modify this to return 3 different RANDOM rows instead of all data in the loop attached.
I've tried to modify and can't get it to work. Any help apreciated.
I have the following asp script that reads from a ms sql database.
It currently returns the data for all rows.
Can somebody please modify this to return 3 different RANDOM rows instead of all data in the loop attached.
I've tried to modify and can't get it to work. Any help apreciated.
<html>
<!--#INCLUDE Virtual="/asp/user/dbHelpers.asp" -->
<%
sql = "SELECT listingStyle, EntryID, FullName, CompanySchoolGuild, City, State, Country FROM WebData WHERE Active = 1 AND EntryType = 'therapist'"
If listingType <> "" Then
sql = sql & " AND EntryType = '" & listingType & "'"
End If
if countryCode <> "" Then
sql = sql & " AND country = '" & countryCode & "'"
End If
If state <> "" Then
sql = sql & " AND state = '" & state & "'"
end if
sql = sql & " ORDER BY isnull(payedmembersince, '10/10/2100') ASC, CASE Country WHEN '' THEN 'ZZZZ' ELSE Country END ASC, CASE State WHEN '' THEN 'ZZZZ' ELSE State END ASC, CASE City WHEN '' THEN 'ZZZZ' ELSE City END ASC, FullName ASC, CompanySchoolGuild ASC"
' default is guild
title = "Hypnosis Guild & Associations Listing"
fields = Array(Array("FullName", "Full Name"), Array("CompanySchoolGuild", "Company Name"), Array("City", "City"), Array("State", "State"), Array("Country", "Country"))
If listingType = "therapist" Then
title = "Hypnotist & Hypnotherapist Listing in the "
If countryCode = "" Then
title = title & "World"
Else
title = title & "United States"
End If
ElseIf listingType = "school" Then
title = "Hypnosis Schools & Training Listing"
ElseIf listingType = "stage" Then
title = "Stage Hypnotists Listing"
End If
Conn.Open(Application("HypnosisDB_ConnectionString"))
ObjRS.Open sql, conn, adOpenStatic, adLockReadOnly, adCmdText
Dim rows
hasResults = false
If ObjRS.EOF Then
Redim rows(0,0)
Else
rows = ObjRS.getRows()
hasResults = true
End If
ObjRs.close()
%>
<%
for i=0 To ubound(fields)
%>
<%
next
%>
<BR>
<%
If hasResults Then
for i=0 To ubound(rows, 2)
%>
<a href="detail.asp?EntryID=<%= rows(1, i) %>"><font face="Verdana, Arial, Helvetica" size="2"><%= rows(2, i) %> </font></a>
<BR>
<%
Next
End If
%>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi raterus, what two lines am i replaceing? am i using both of your sql lines?
ASKER
hi raterus,
this line here
sql = "SELECT top 3 listingStyle, EntryID, FullName, CompanySchoolGuild, City, State, Country FROM WebData WHERE Active = 1 AND EntryType = 'therapist'"
works great.
now how do i get the top 3 results that show up to just randomize each time so its 3 different random results?
this line here
sql = "SELECT top 3 listingStyle, EntryID, FullName, CompanySchoolGuild, City, State, Country FROM WebData WHERE Active = 1 AND EntryType = 'therapist'"
works great.
now how do i get the top 3 results that show up to just randomize each time so its 3 different random results?
The " ORDER BY newid() ", I posted in the second sql query is going to take care of this for you.
"SELECT * FROM Questions WHERE questionID = " + RandomNumber();
You just need to determine the maximum questionID value (using the SQL MAX keyword) so that you can pass it to the RandomNumber function.