?
Solved

Selecting Random Records via ASP and Access

Posted on 2009-05-08
5
Medium Priority
?
288 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month15 days, 11 hours left to enroll

850 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