random records

Report · Jul 24, 2007

I'm working on a site that is using a number of small Access databases and
classic ASP. The option to move to a different database format is not an
option at present (but I'm working on it).

In the interim, I've been tasked to grab a random text record from a
database; kind of a "tip of the day (hour, minute, whatever)". Within Access
I can generate a query that will do this, no probs. However it seems that
this will not work when run from a web server. (Odd thing is that the SQL
would work when using the "test" button w/in DW8)

For those interested, the SQL was:
SELECT TOP 1 tblReason2Ride.recNum, tblReason2Ride.ReasonNum,
tblReason2Ride.ReasonText, tblReason2Ride.RidingReason,
tblReason2Ride.ReasonCustomer
FROM tblReason2Ride
ORDER BY Rnd([recNum])*[recNum]*Now()

I also tried
SELECT *
FROM tblFactoids
ORDER BY Rnd([ID])

Within Access, both worked fine. Within Dreamweaver, hitting the TEST button
w/in the Recordset dialog box also worked. And if I could move to MS SQL I
could use ORDER BY NEWID()... so, any suggestions on how to achieve this
w/in ASP would be helpful.

Report · Jul 24, 2007

"Hunter Elliott" <nospam@gatewaycity.com> wrote in message
news:f85qiq$pkj$1@forums.macromedia.com...
> In the interim, I've been tasked to grab a random text record from a
> database; kind of a "tip of the day (hour, minute, whatever)". Within
> Access I can generate a query that will do this, no probs. However it
> seems that this will not work when run from a web server. (Odd thing is
> that the SQL would work when using the "test" button w/in DW8)

In order to solve a problem, the problem must first be defined.
Both queries you showed will run ("from a web server" has little to do with
anything) provided that your column names are appropriate for your table
definitions - and you add the final closing parenthesis to the first. If
you're having a problem, the error message and symptoms would be most
helpful for potential troubleshooters.

Report · Jul 25, 2007

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:f85r90$qdn$1@forums.macromedia.com...
> "Hunter Elliott" <nospam@gatewaycity.com> wrote in message
> news:f85qiq$pkj$1@forums.macromedia.com...
>> In the interim, I've been tasked to grab a random text record from a
>> database; kind of a "tip of the day (hour, minute, whatever)". Within
>> Access I can generate a query that will do this, no probs. However it
>> seems that this will not work when run from a web server. (Odd thing is
>> that the SQL would work when using the "test" button w/in DW8)
>
> In order to solve a problem, the problem must first be defined.
> Both queries you showed will run ("from a web server" has little to do
> with anything) provided that your column names are appropriate for your
> table definitions - and you add the final closing parenthesis to the
> first. If you're having a problem, the error message and symptoms would
> be most helpful for potential troubleshooters.

Sorry about that - when I run the query(-ies) from within Access, whether or
not using a TOP 1 declaration, they work great - things randomize fine. When
using the TEST button from the Recordset dialog box in Dreamweaver, it also
works - things randomize as expected. When the page is viewed from the
webserver (IIS6), it "sort of" randomizes. It always returns the same record
every time. It doesn't matter which query is used for the recordset. There
are no error messages.

Report · Jul 25, 2007

"Hunter Elliott" <nospam@gatewaycity.com> wrote in message
news:f87j9q$jq$1@forums.macromedia.com...
> Sorry about that - when I run the query(-ies) from within Access, whether
> or not using a TOP 1 declaration, they work great - things randomize fine.
> When using the TEST button from the Recordset dialog box in Dreamweaver,
> it also works - things randomize as expected. When the page is viewed from
> the webserver (IIS6), it "sort of" randomizes. It always returns the same
> record every time. It doesn't matter which query is used for the
> recordset. There are no error messages.

They look OK. You might want to try using (ID * Now()) in both queries,
though, rather than just the first.
Check to make sure that the page isn't being cached. If you think it might
be cached, hit F5 to double check.

Report · Jul 25, 2007

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:f87tvg$dg8$1@forums.macromedia.com...
> "Hunter Elliott" <nospam@gatewaycity.com> wrote in message
> news:f87j9q$jq$1@forums.macromedia.com...
>> Sorry about that - when I run the query(-ies) from within Access, whether
>> or not using a TOP 1 declaration, they work great - things randomize
>> fine. When using the TEST button from the Recordset dialog box in
>> Dreamweaver, it also works - things randomize as expected. When the page
>> is viewed from the webserver (IIS6), it "sort of" randomizes. It always
>> returns the same record every time. It doesn't matter which query is used
>> for the recordset. There are no error messages.
>
> They look OK. You might want to try using (ID * Now()) in both queries,
> though, rather than just the first.
> Check to make sure that the page isn't being cached. If you think it
> might be cached, hit F5 to double check.

caching was my first thought - I tried it with multiple browser brands on my
machine and a few others before I posted here - the same record was returned
on all machines (heh, and yes, there is more than one record in the table).
I'll try what you suggest and see what happens. Thanks for the suggestion!

Report · Aug 01, 2007

"Hunter Elliott" <nospam@gatewaycity.com> wrote in message
news:f886ov$o2r$1@forums.macromedia.com...
> I'll try what you suggest and see what happens. Thanks for the suggestion!

wound up using a random record SB I found over at DMXzone.com - it's for ASP
only, but does what I need it to do. It's called "Move To Random Record-ASP"
by Steven Jones.