Solved

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

Posted on 2004-08-18
2
226 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
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

762 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

21 Experts available now in Live!

Get 1:1 Help Now