Selecting Random Records via ASP and Access

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
asb987Asked:
Who is Participating?
 
Bobaran98Connect With a Mentor Commented:
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
 
Bobaran98Commented:
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
 
Anthony PerkinsCommented:
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
 
asb987Author Commented:
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
 
asb987Author Commented:
Changing the Order by over to the suggested ORDER BY Rnd(-10000000*TimeValue(Now())*[id]) did the trick! Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.