Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 23rd, 2008, 06:40 AM
Authorized User
 
Join Date: Sep 2008
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default 2 table fields into 1 dropdown selectbox

Hi

I am stuck on a problem I'm not sure is possible but hope it is. I currently have a dynamic dropdown select box which simply takes the value from one field of an access database table. Using ASP and Access, The code I use simply selects all unique town/cities from a field called 'towncity'. An output would something like:

SELECT DISTINCT towncity from table order by towncity;

<option>Birmingham</option>
<option>Crewe</option>
<option>Preston</option>
<option>Wigan</option>

and so on. What I want to do is get unique data from 2 fields in the table (district and towncity)and join them into one dropdown. So that the out put would be similar to:

SELECT DISTINCT district, towncity from table order by <alphabetically?>;

<option>Batherton</option>
<option>Birmingham</option>
<option>Crewe</option>
<option>Haslington</option>
<option>Preston</option>
<option>Wigan</option>
<option>Willaston</option>
<option>Wybunbury</option>

Is there a way I can do this? I may want to extend this by combine 3 fields into one unique dropdown. ie, District, TownCity and County. Many thanks for anyone who can help me.

 
Old September 23rd, 2008, 03:35 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

SELECT DISTINCT towncity AS NAME from table
UNION
SELECT DISTINCT district from table
ORDER BY 1

Presto.

NOTE: If you have a towncity and a district with the same name, SQL Server will only give you back one of them (you would use UNION ALL to get both), but I think that Access will give you both. You could fix this, as well, by doing

SELECT DISTINCT name FROM
   ( SELECT towncity AS name from table
     UNION
     SELECT district from table
   )
ORDER BY name
 
Old September 23rd, 2008, 03:42 PM
Authorized User
 
Join Date: Sep 2008
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Many thanks for your help. That worked a treat.

Thank you.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating a table from a dropdown GS ASP.NET 2.0 Basics 2 March 18th, 2007 07:22 AM
get the text and not the value from a selectBox recipiente Classic ASP Basics 7 March 9th, 2007 12:59 PM
Greying out dropdown fields in another form rabbit124 Classic ASP Basics 3 March 12th, 2006 07:35 PM
dropdown save into sql table noor ASP.NET 1.0 and 1.1 Basics 3 January 3rd, 2005 12:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.