How To Select a Random Item From a Recordset?

[KellyHero]

Hi,

I am building a real estate rentals website and would like to have a "Featured Property" on the homepage which would be a single record pulled at random from the properties table in the database so that each time someone visits the page, a different property is displayed.

Any assistance is much appreciated.

 

[Tarwn]

This one has been covered before: thread333-452982

I would suggest going with JamesLean's suggestion (newId) as I later found out after more benchmarking that sometimes it is faster than the other method and sometimes it is slower, but either way it takes less code so it's easier on the programmer

-Tarwn [sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[KellyHero]

Thanks.

 

[KellyHero]

OK, I checked out Tarwn's solution and I think it's a bit much for what I need. I only want to select one record at random at a time.

I found this code that I think will do what I am trying to do:

dim intRandomNumber,intTotalRecords,i
intTotalRecords = recordset.RecordCount
Randomize()
intRandomNumber = Int(intTotalRecords * Rnd)
recordset.Move intRandomNumber
Response.write(&quot;<table border='1'><tr>&quot
For i = 0 to recordset.Fields.Count - 1
Response.write(&quot;<td>&quot; & recordset(i) & &quot;</td>&quot
Next
response.write &quot;</tr></table>&quot;

The problem that I'm now having is that this code calls for connection to the database using the Open method:

dim connection, recordset, sConnString, sql
sql = &quot;SELECT * FROM myTableName&quot;
Set connection = Server.CreateObject(&quot;ADODB.Connection&quot
Set recordset = Server.CreateObject(&quot;ADODB.Recordset&quot
sConnString = &quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot; & _
&quot;DBQ=&quot; & Server.MapPath(&quot;mydatabase.mdb&quot & &quot;;&quot;
connection.Open(sConnString)
recordset.OPEN sql, connection, 3, 1

Each of my ASP pages contain a header which has a search box. This include file calls another include file which contains the code that creates my connection to the database:

Set Conn = server.CreateObject(&quot;adodb.connection&quot
Dim DSNtemp
DSNtemp=&quot;Provider=MSDASQL;&quot;
DSNtemp=DSNtemp & &quot;DRIVER={Microsoft Access Driver (*.mdb)}; &quot;
DSNtemp=DSNtemp & &quot;DBQ=&quot; & Server.Mappath(&quot;./xxxxxxx/xxxxxx.mdb&quot & &quot;;&quot;
Conn.open DSNtemp,&quot;admin&quot;,&quot;&quot;

If I delete their connection code because I already have one, I get the error &quot;Rowset does not support fetching backward.&quot; because my connection doesn't specify a CursorType. How do I change my CursorType using the connection code I already have so that I can move through the recordset as I need to (CursorType = 3)?

Also, because my Admin pages use the same include, do I need to make another include for those pages with CursorType = 1?

Please help me. I'm not very good with ASP yet.

 

[Tarwn]

Actually the method I suggested can be summarized like so:

Dim conn, rs
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Your Connection String Here"

Set rs = conn.Execute("SELECT Top 1 * FROM YourTable ORDER BY NewId()")

'do your display here

rs.Close
conn.Cose
Set rs = Nothing
Set conn = Nothing

The code that I posted in that thread was the code I used to benchmark, not an example of how to get a random record, though there were 3 different pieces of code used in that that do get random records. If you noticed the benchmark results, .Opening a whole table than grabbing a record is extremely slow especially compared to the other two methods.


Concerning your question, the Execute method of the connection object does not accept a parameter for cursor type, therefore to do what your looking to do you would need to use the RecordSet object and .Open method. Since you are looking to open a recordset and scroll through to a random record, neither way will really be faster than the other so you may as well stick with using the recordset object like in the original piece of code (if you don't want to use the NewId method)

-Tarwn [sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[KellyHero]

Ahhhhhhh...now I see what you were doing! Thanks for clearing that up. And thanks for clearing up the issue with the CursorType.

I will give the NewID method a try and get back to you.

 

[baddos]

Is there a way do duplicate the NewId() function from SQL Server to MS Access? Anyone have an idea on how to do this?

I am currently retreiving a full recordset and randomly picking items from that rs. I would like Access to do this though, hoping to get better performance.

-Bad Dos

 

[xutopia]

actually I was wondering if there was a way to do it so that you didn't have to get a huge recordset. Gary

http://www.xutopia.com

Haran
==========================

 

[Tarwn]

Concerning MS Access: No, this is a built in SQL server function

Concerning 1 record: Um...the query above will only return 1 record in the reordset, though I would specify the fieldnames rather than using a * because it cuts down on an extra query that the (db) server needs to make.


-Tarwn [sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048