Random sort via SQL in ASP

[muskie]

I've looked through as many posts about this as possible, but all end
with no resolution. I simply need records from a table in random
order, and I will be calling this recordset in a SQL statement from
ASP. I've tried the following but it does not produce random order:

SELECT * FROM Table1 ORDER BY Rnd(TableID) ASC;

where TableID is an autonumber field.

I've seen references to Randomize, but how do you use Access's
randomize from ASP?

Does anyone know of a simple solution for this?

Thanks,
Matt

[engles]

On Apr 12, 7:33 pm, "muskie" <goo...@sniders tudio.comwrote:

I've looked through as many posts about this as possible, but all end
with no resolution. I simply need records from a table in random
order, and I will be calling this recordset in a SQL statement from
ASP. I've tried the following but it does not produce random order:

SELECT * FROM Table1 ORDER BY Rnd(TableID) ASC;

where TableID is an autonumber field.

I've seen references to Randomize, but how do you use Access's
randomize from ASP?

Does anyone know of a simple solution for this?

Thanks,
Matt

I'd be surprised if you can use this in SQL using the method you are
trying. I'd write some code that you can trigger that puts a random
number in a field (e.g. sortfield as long) in the table. Then you can
select and order by that random field (e.g. ORDER BY sortfield). This
will certainly get the job done. Whenever you want a different
randomizing, then trigger the code.

-- Larry Engles

[engles]

On Apr 12, 9:13 pm, eng...@ridesoft .com wrote:

On Apr 12, 7:33 pm, "muskie" <goo...@sniders tudio.comwrote:

I've looked through as many posts about this as possible, but all end
with no resolution. I simply need records from a table in random
order, and I will be calling this recordset in a SQL statement from
ASP. I've tried the following but it does not produce random order:

SELECT * FROM Table1 ORDER BY Rnd(TableID) ASC;

where TableID is an autonumber field.

I've seen references to Randomize, but how do you use Access's
randomize from ASP?

Does anyone know of a simple solution for this?

Thanks,
Matt

I'd be surprised if you can use this in SQL using the method you are
trying. I'd write some code that you can trigger that puts a random
number in a field (e.g. sortfield as long) in the table. Then you can
select and order by that random field (e.g. ORDER BY sortfield). This
will certainly get the job done. Whenever you want a different
randomizing, then trigger the code.

-- Larry Engles- Hide quoted text -

- Show quoted text -

If you have an autonumber feild, you could use that in the query (e.g.
ORDER BY (autonumberFiel d).
-- Larry Engles

[muskie]

On Apr 12, 10:40 pm, eng...@ridesoft .com wrote:

On Apr 12, 9:13 pm, eng...@ridesoft .com wrote:

On Apr 12, 7:33 pm, "muskie" <goo...@sniders tudio.comwrote:

I've looked through as many posts about this as possible, but all end
with no resolution. I simply need records from a table in random
order, and I will be calling this recordset in a SQL statement from
ASP. I've tried the following but it does not produce random order:

SELECT * FROM Table1 ORDER BY Rnd(TableID) ASC;

where TableID is an autonumber field.

I've seen references to Randomize, but how do you use Access's
randomize from ASP?

Does anyone know of a simple solution for this?

Thanks,
Matt

I'd be surprised if you can use this in SQL using the method you are
trying. I'd write some code that you can trigger that puts a random
number in a field (e.g. sortfield as long) in the table. Then you can
select and order by that random field (e.g. ORDER BY sortfield). This
will certainly get the job done. Whenever you want a different
randomizing, then trigger the code.

-- Larry Engles- Hide quoted text -

- Show quoted text -

If you have an autonumber feild, you could use that in the query (e.g.
ORDER BY (autonumberFiel d).
-- Larry Engles

Thanks Larry, I appreciate the input...
If I run my query in Access it works to produce a randomly sorted
recordset. If I run the exact same query from my ASP it does not.
Any idea why this happens?

[Tom van Stiphout]

On 12 Apr 2007 21:49:26 -0700, "muskie" <go****@sniders tudio.com>
wrote:

Because when running from Access you have the entire Access VBA code
available (including the Rnd function), whereas when running from ASP
you only have the Jet database engine.

If you only want to select one or a few random records, perhaps you
can open a recordset, count the records, and then rs.Move a random
number between 1 and RecCount from the top.

In SQL Server you can use NewID to sort with.

-Tom.

>On Apr 12, 10:40 pm, eng...@ridesoft .com wrote:

>On Apr 12, 9:13 pm, eng...@ridesoft .com wrote:

On Apr 12, 7:33 pm, "muskie" <goo...@sniders tudio.comwrote:

I've looked through as many posts about this as possible, but all end
with no resolution. I simply need records from a table in random
order, and I will be calling this recordset in a SQL statement from
ASP. I've tried the following but it does not produce random order:

SELECT * FROM Table1 ORDER BY Rnd(TableID) ASC;

where TableID is an autonumber field.

I've seen references to Randomize, but how do you use Access's
randomize from ASP?

Does anyone know of a simple solution for this?

Thanks,
Matt

I'd be surprised if you can use this in SQL using the method you are
trying. I'd write some code that you can trigger that puts a random
number in a field (e.g. sortfield as long) in the table. Then you can
select and order by that random field (e.g. ORDER BY sortfield). This
will certainly get the job done. Whenever you want a different
randomizing, then trigger the code.

-- Larry Engles- Hide quoted text -

- Show quoted text -

If you have an autonumber feild, you could use that in the query (e.g.
ORDER BY (autonumberFiel d).
-- Larry Engles

Thanks Larry, I appreciate the input...
If I run my query in Access it works to produce a randomly sorted
recordset. If I run the exact same query from my ASP it does not.
Any idea why this happens?

[Don Leverton]

How about adding a Long Integer field to the table itself called "NewID",
and then run an update query to fill this field with random ID's on a
somewhat "permanent basis" ?
Sort your data by NewID for use in your ASP.
To re-randomize the values at any time ... run the update query again.
--
--
HTH,
Don
=============== ==============
E-Mail (if you must) My*****@Telus.n et

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

=============== =============== =============== =============== ==============

"Tom van Stiphout" <no************ *@cox.netwrote in message
news:db******** *************** *********@4ax.c om...

On 12 Apr 2007 21:49:26 -0700, "muskie" <go****@sniders tudio.com>
wrote:

Because when running from Access you have the entire Access VBA code
available (including the Rnd function), whereas when running from ASP
you only have the Jet database engine.

If you only want to select one or a few random records, perhaps you
can open a recordset, count the records, and then rs.Move a random
number between 1 and RecCount from the top.

In SQL Server you can use NewID to sort with.

-Tom.

>>On Apr 12, 10:40 pm, eng...@ridesoft .com wrote:

>>On Apr 12, 9:13 pm, eng...@ridesoft .com wrote:

On Apr 12, 7:33 pm, "muskie" <goo...@sniders tudio.comwrote:

I've looked through as many posts about this as possible, but all
end
with no resolution. I simply need records from a table in random
order, and I will be calling this recordset in a SQL statement from
ASP. I've tried the following but it does not produce random order:

SELECT * FROM Table1 ORDER BY Rnd(TableID) ASC;

where TableID is an autonumber field.

I've seen references to Randomize, but how do you use Access's
randomize from ASP?

Does anyone know of a simple solution for this?

Thanks,
Matt

I'd be surprised if you can use this in SQL using the method you are
trying. I'd write some code that you can trigger that puts a random
number in a field (e.g. sortfield as long) in the table. Then you can
select and order by that random field (e.g. ORDER BY sortfield). This
will certainly get the job done. Whenever you want a different
randomizing, then trigger the code.

-- Larry Engles- Hide quoted text -

- Show quoted text -

If you have an autonumber feild, you could use that in the query (e.g.
ORDER BY (autonumberFiel d).
-- Larry Engles

Thanks Larry, I appreciate the input...
If I run my query in Access it works to produce a randomly sorted
recordset. If I run the exact same query from my ASP it does not.
Any idea why this happens?