Solved

Selecting Random Records via ASP and Access

Posted on 2009-05-08
5
278 Views
Last Modified: 2012-06-21
I am trying to pull 50 random records out of a database that contains over 250k lines. I have a saved query in access to do this. It works great and pulls 50 different records everytime it is run. I need to call this query from ASP and then build the data into a report from there. I can pull the data fine and run the query in ASP but it always displays the same recordset. The 50 records it pulls are never different. Am I missing something here? Any advice is great. I will attached two different versions of code I have used and both are doing the same thing. Again, the saved query asks for the variables and works flawlessly from within Access, just cant get it there with ASP using the query.

Version 1
Saved Query in Access:

SELECT TOP 50 m_IN_INVENTORY.SKU, m_IN_INVENTORY.Description, m_IN_INVENTORY.Location, m_IN_INVENTORY.PriceRetail, m_IN_INVENTORY.QtyOnHand_Numeric, Rnd((-1*[@intRandNumber])*m_IN_INVENTORY.SKU) AS rnum
FROM m_IN_INVENTORY
WHERE (((m_IN_INVENTORY.SKU)<"1") AND ((m_IN_INVENTORY.Location)>"") AND ((m_IN_INVENTORY.QtyOnHand_Numeric)>0) AND ((m_IN_INVENTORY.Store)=[@Store]) AND ((IsNumeric([SKU]))=True))
ORDER BY Rnd((-1*[@intRandNumber])*m_IN_INVENTORY.SKU) DESC;

ASP:

Store = 1
intRandNumber = (CInt(1000*Rnd) + 1)*-1

Set rs = Server.CreateObject("ADODB.Recordset")
adoCon.qRand50 intRandNumber, Store, rs

Do While NOT rs.EOF
      response.write(rs("sku") & "<br>")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
adoCon.Close
Set adoCon = Nothing


Version 2:
Saved Query in Access:

SELECT TOP 50 m_IN_INVENTORY.SKU, m_IN_INVENTORY.Description, m_IN_INVENTORY.Location, m_IN_INVENTORY.PriceRetail, m_IN_INVENTORY.QtyOnHand_Numeric, Rnd(m_IN_INVENTORY.SKU) AS rnum
FROM m_IN_INVENTORY
WHERE (((m_IN_INVENTORY.SKU)<"1") AND ((m_IN_INVENTORY.Location)>"") AND ((m_IN_INVENTORY.QtyOnHand_Numeric)>0) AND ((m_IN_INVENTORY.Store)=[@Store]) AND ((IsNumeric([SKU]))=True))
ORDER BY Rnd(m_IN_INVENTORY.SKU) DESC;

ASP:

Store = 1

Set rs = Server.CreateObject("ADODB.Recordset")
adoCon.qRand50 Store, rs

Do While NOT rs.EOF
      response.write(rs("sku") & "<br>")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
adoCon.Close
Set adoCon = Nothing
0
Comment
Question by:asb987
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24340109
Just taking a stab in the dark here, since I've not done this kind of thing before and I didn't realize there was a Rnd() function in Access SQL... however, it looks to me like you're seeding the random number generator the exact same way each time.
In version 1:  Your @intRandNumber is going to be the exact same number throughout the life of the query execution, so even if you multiply it against m_IN_INVENTORY.SKU and order by the product, the result will be exactly the same as ordering the entire database by m_IN_INVENTORY... in other words, despite your math, the random number cancels out.
In version 2:  Same thing, but a different way of getting there.  In this version, you're seeding the random number generator with a different value for each record; however, I'm guessing that each record will end up showing the same "random" number every time you execute the query.
I'm just guessing here, of course, by looking at your code.  I may be totally wrong.  Regardless, I suggest you run these queries and have your ASP page print out the entire result set, including your "random" number field... do this several times, and see if you're getting truly random numbers.
As for what to do as an alternative... give me a few minutes to think on it... :-)
0
 
LVL 8

Accepted Solution

by:
Bobaran98 earned 500 total points
ID: 24340232
Hmm... despite my eloquent arguments in that last post, I've done some searching around and it looks like your code actually should work.  And then there's the fact that it seems to work ok for you in Access proper.  Oh well, mental gymnastics never hurt anyone. :-)
Here's an alternative you might try.  Remove the rnum field from the SELECT statement itself and add something like this to the ORDER BY clause:
ORDER BY Rnd(-10000000*TimeValue(Now())*[id])
... where "id" is a numerical field in your table, such as the ID.  Because it's seeded by the current time, you should get a fairly random number-- seeding with current time is a pretty standard practice.
As for your multiplier, just a thought... you might consider using your QtyOnHand_Numeric field, or maybe adding it as another multiplier.  Your whole goal is to change up which random results you get each visit, and that field would-- I'm guessing-- be the most likely to change frequently (or at least for enough records that it would majorly affect the sorting).
I sincerely hope all this helps. :-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24341691
Here is what you will have to do:
1. Create a table Temp on the fly with two columns your primary key and a randomly generated value (RandomColumn).
2. Join your table to Temp and order by RandomColumn
3. Drop the Temp table.
0
 

Author Comment

by:asb987
ID: 24345670
The problem is the query works flawlessly as is, either version, in Access itself. Something on the calling it from ASP is causing it to not refresh the data. The Rnd Function in Access assigns a different random value to any positive number it is called through and then it gets sorted fine with the existing query. This needs to happen fast and I am worried about performance with creating a scratch / temporary table just for this query. Are there any other thoughts out there as to why it would be breaking when its called from an ASP page? Any more thoughts are appreciated.
0
 

Author Comment

by:asb987
ID: 24345883
Changing the Order by over to the suggested ORDER BY Rnd(-10000000*TimeValue(Now())*[id]) did the trick! Thanks for the help!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Smart quotes being changed on insert 9 48
ASP Classic - Load test 2 34
Error viewing ASP page 12 98
ASP Button to clear text 4 20
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

13 Experts available now in Live!

Get 1:1 Help Now