Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-08-18
2
Medium Priority
?
255 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 200 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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 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 …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

581 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