Solved

Select 3 random rows from dataset in ASP

Posted on 2007-11-20
5
1,076 Views
Last Modified: 2008-03-08
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.
<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 &amp; 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 &amp; Hypnotherapist Listing in the "

	If countryCode = "" Then

		title = title & "World"

	Else

		title = title & "United States"

	End If

ElseIf listingType = "school" Then

	title = "Hypnosis Schools &amp; 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) %>&nbsp;</font></a>

																																					 

										<BR>

										

										<%

									Next

								End If

							%>

Open in new window

0
Comment
Question by:superlative
  • 2
  • 2
5 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20319168
If you just want a random row you can use the Random number function to generate the questionID value and then specify the value in your SELECT statement criteria:

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

0
 
LVL 33

Accepted Solution

by:
raterus earned 500 total points
ID: 20319180
Replace these two lines with this,

sql = "SELECT top 3 listingStyle, EntryID, FullName, CompanySchoolGuild, City, State, Country FROM WebData WHERE Active = 1 AND EntryType = 'therapist'"


sql = sql & " ORDER BY newid(), 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"
 
0
 

Author Comment

by:superlative
ID: 20319197
hi raterus, what two lines am i replaceing? am i using both of your sql lines?
0
 

Author Comment

by:superlative
ID: 20319206
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?
0
 
LVL 33

Expert Comment

by:raterus
ID: 20319528
The " ORDER BY newid() ", I posted in the second sql query is going to take care of this for you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Asp.net mvc foreach 3 35
How to get the web content with different encodings 2 29
Vb.net dynamic formulas in runtime 11 63
Connection String 16 43
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now