Solved

Query works in Access and Dreamweaver - only partially works with webpage!

Posted on 2004-08-18
2
241 Views
Last Modified: 2010-04-25
I have spent most of today trying to work out why my webpage is not correctly working!

I have an internal (intranet) website driven by an Access Database, with multiple pages with various queries displaying the information in a multitude of ways. All of my queries have worked so far (around 50) however I wrote a query to randomize the records returned from a simple query displaying the contents of a table yet in a random order each time the query runs. (Using Expr1: RND([ID])

One field of the query contains a simple randomize statement of the ID's within the table and I have selected order by Ascending results of the field with the randomizsing expression. The query works perfectly in Access and also in Dreamweaver once a recordset is set up and 'test' is selected. Even when I write SELECT TOP 1 People FROM QryRndm, the query works perfectly returning one row with a different persons name each time (which is actually what I want)

However, when I set the webpage up with various dynamic text components produced from the recordset, I get the same record each time, rather than a random record returned!

What is even more bizarre, the record returned is not the first one, it is indeed 'random' - the 7th I think in the table. However, no matter how many times I refresh (thinking maybe a cache problem), I always get the same record returned rather than a random one each time!

I don't understand how the query can work in Access, via the test function in the recordset of Dreamweaver, yet not when the actual webpage is displayed!

Is it an IIS problem? Why would all my other 50+ queries work, any not this one?

Any known issues using the RND() command directly in an SQL statement?
0
Comment
Question by:djnsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 8

Expert Comment

by:alexhogan
ID: 11836749
There is a solution that I think will address your issue here on EE;

http://www.experts-exchange.com/Databases/MS_Access/Q_20790395.html

0
 
LVL 19

Accepted Solution

by:
webwoman earned 50 total points
ID: 11842680
To simplify a little...
You need to add a RANDOMIZE command before you create a random number. Otherwise you get the same number every time. Also, you need to specify the range you want the random number to fall within.

There are pretty standard formulas to do this.

You don't want to do the random number inside an Access query, because you have no way to initialize the RANDOMIZE command. The rnd command works fine when it's done directly in Access, but NOT when you connect to an Access database using an ASP page. You'll get the same number every time (been there, tried that...). (Don't ask me, ask MS -- it's not the only dumb thing that happens when you use Access databases on web pages.) You need to generate the random number in your ASP code, then pull that record from the database.

I would open the recordset, generate a random number based on the number of records, then move that many records and display the result. There are other ways, so don't feel you have to do it like that.

But if you use an autonumber for the primary ID, it's very likely you'll have gaps in the sequence, so going to that record ID or using it as the ID for a SQL statement may not work. Moving that number of records in the recordset will always work.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 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