Re: Random sort via SQL in ASP

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.Name@xxxxxxxxx

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.spam.tom7744@xxxxxxx> wrote in message
news:db1v13lq2sn06ic344tbjrf9nakp1elf6m@xxxxxxxxxx
On 12 Apr 2007 21:49:26 -0700, "muskie" <google@xxxxxxxxxxxxxxxx>
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...@xxxxxxxxxxxx wrote:
On Apr 12, 9:13 pm, eng...@xxxxxxxxxxxx wrote:



On Apr 12, 7:33 pm, "muskie" <goo...@xxxxxxxxxxxxxxxx> wrote:

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 (autonumberField).
-- 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?



.

Relevant Pages

  • Re: To change negative to positive in MS Access
    ... If you want to use the UPDATE query method and you will be importing new data into the table, ... UPDATE query that will only change the numbers that haven't been changed. ... changed data or if it is new, unchanged data and then build your UPDATE ...
    (microsoft.public.access.queries)
  • Re: webbased search through catalog
    ... I've got the asp script working, but it seems to spit out results ... Modify q.catalog to specify your catalog name. ... > ' your logo is not a GIF file, or you don't want to copy it, change the ... > ' You can set the number of query results returned on a single page ...
    (microsoft.public.inetserver.indexserver)
  • Re: Append import data to existing record
    ... I'm glad you've got the key query working. ... With the update query open in Design View: ... Split the filespec apart and reassemble it into a ... Function BuildJetTextSource(ByVal FileSpec As String, ...
    (microsoft.public.access.externaldata)
  • Re: To change negative to positive in MS Access
    ... Rick's method is just a calculated field in a SELECT query. ... Klatuu's method is an UPDATE query and it WILL change your underlying data. ... If you are importing, this can be done using the update method, but it can be a little tricky. ... you are importing data from an external source and this field is negative in these source files, ...
    (microsoft.public.access.queries)
  • Re: Update query after an append query?+
    ... computer so that I could tell when and if I get the update query to work. ... that if you created your query under a different ANSI SQL query mode than ... the current mode of your Microsoft Access database then your update query ...
    (microsoft.public.access.formscoding)