Sorting ADO recordset

[Nicholas]

I'm using a DISTINCT query to build a pulldown list that
users can choose from. It works fine and is based on this
page: http://www.frontpagehowto.com/dynamicdrop/Default.asp

Problem is, I want to further sort the pulldown list.
SORT and GROUP don't work with DISTINCT, so the thought is
to use a separate sort command on the recordset. This
causes an error message that leads to setting the cursor
location in the data connection object, but I can't get
that to work.

http://support.microsoft.com/default.aspx?scid=kb;en-
us;Q253248

Can anyone help with this? I looked at the cursor
location statement in the FP global.asa file, but changing
it to adUseClient didn't seem to help. It comes up as 3,
so maybe the right number would work. 0, 1, 2 didn't seem
to.

Or, a different approach would be to put numbers at the
beginning of the pulldown fields to handle the sorting,
and then hide those characters. I can't find anyway to
hide say just the first six characters.

What I am aiming for is to sort a dynamically-created
pulldown by month and year. I could do 200408 August 2004
to solve the sorting, but I want the pulldown to show
August 2004 once, then when a user chooses it he'll see
all the August records.
I have not built the database so it could be any
combination of fields.

 

[Jim Buyens]

How about using an ORDER BY clause, as in:

SELECT distinct
Products.ProductID,
Products.ProductName
FROM Products
ORDER BY Products.ProductName;

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Nicholas]

This works, no erros and the list is ordered by
ProductName, but ProductID is not distinct. It returns
all of the records as if distinct is not there.

Is there any way to strip or hide duplicate records out of
the ADO recordset? Or, again to strip off or hide the
first few characters

 

[Jim Buyens]

Well, you know, DISTINCT only filters duplicates on *all* fields in
the query. Perhaps you need:

SELECT Products.ProductID,
First(Products.ProductName) AS FirstProductName
FROM Products
GROUP BY Products.ProductID
ORDER BY First(Products.ProductName)

which guarantees you'll only get one record per ProductID value.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Nicholas]

Thank You! This worked like a champ. Thank You Again!!!