Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. 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 Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 10th, 2004, 05:52 AM
Authorized User
 
Join Date: Sep 2003
Location: Wonderboom South, Pretoria, Gauteng, South Africa.
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Greywacke
Default selecting multiple records as one record array

okay - here are my two tables:

Code:
TABLE: mmorpg
lng_id    str_name    str_currency
1    The Sims Online    Simoleans
2    Ultima Online    Gold
3    Shadow Bane    Gold
4    Ever Quest    Platinum
5    Eve Online    Isk
6    Earth & Beyond    Credits
7    Dark Age Of...    Gold
8    Anarchy Online    Credits
Code:
TABLE: servers
lng_id    lng_rpgid    str_name
1    1    Alphaville
2    1    Blazing Falls
3    1    Calvin's Creek
4    1    Dan's Grove
5    1    East Jerome
6    1    Fancey Fields
7    1    Interhogan
8    1    Jolly Pines
9    1    Mount Fuji
10    1    Test Center
11    2    AOL Legends
12    2    Arirang
13    2    Asuka
14    2    Atlantic
15    2    Balhae
16    2    Baja
17    2    Catskills
18    2    Chesapeake
19    2    Drachenfels
20    2    Europa
21    2    Formosa
22    2    Great Lakes
23    2    Hokuto
24    2    Izumo
25    2    Lake Superior
26    2    Napa Valley
27    2    Oceania
28    2    Pacific
29    2    Sonoma
30    2    Wakoku
31    2    Yamato
32    3    Carnage
33    3    Chaos
34    3    Corruption
35    3    Death
36    3    Deception
37    3    Dread
38    3    Fear
39    3    Mourning
40    3    Scorn
41    3    Test
42    3    Treachery
43    3    Vengeance
44    3    War
45    4    Rallos Zek
46    4    Xegony
47    4    The Rathe
48    4    Fennin Ro
49    4    Tarew Marr
50    4    Povar
51    4    E'ci
52    4    Veeshan
53    4    Solusek Ro
54    4    Cazic-Thule
55    4    Mithaniel Marr
56    4    Karana
57    4    Innoruuk
58    4    Tunare
59    4    Bertoxxulous
60    4    Rodcet Nife
61    4    Erollisi Marr
62    4    Prexus
63    4    Quellious
64    4    Brell Serilis
65    4    Tallon Zek
66    4    Vallon Zek
67    4    Bristlebane
68    4    The Tribunal
69    4    The Nameless
70    4    Lanys T'Vyl
71    4    Luclin
72    4    Druzzil Ro
73    4    Torvonnilous
74    4    Morell-Thule
75    4    Saryrn
76    4    The Seventh Hammer
77    4    Terris-Thule
78    4    Drinal
79    4    Tholuxe Paells
80    4    Xev
81    4    Ayonae Ro
82    4    Vazaelle
83    4    Zebuxoruk
84    4    Sullon Zek
85    4    Firiona Vie
86    4    Antonius Bayle
87    4    Stormhammer
88    4    Kane Bayle
89    5    Official Server
90    6    Official Server
91    7    Pendragon
92    7    Bedevere
93    7    Nimue
94    7    Palomides
95    7    Igraine
96    7    Iseult
97    7    Pellinor
98    7    Bors
99    7    Gawaine
100    7    Gaheris
101    7    Tristan
102    7    Kay
103    7    Galahad
104    7    Mordred
105    7    Percival
106    7    Lancelot
107    7    Guinevere
108    7    Merlin
109    7    Morgan
110    8    Atlantean
111    8    Rimor
112    8    Die Neue Welt
114    2    Baekdu
115    2    Mizuho
116    2    Siege Perilous
117    2    Lake Austin
118    2    Mugen
119    2    Sakura
120    4    Al'Kabor
121    4    Maelin Starpyre
122    4    Stromm
123    4    Test Server
i need to return a recordset which looks about like the following:

Code:
lng_id    str_name    str_currency    lng_srvid    str_name
1    The Sims Online    Simoleans    1, 2, 3, 4, 5, 6, 7, 8, 9, 10    Alphaville, Blazing Falls, Calvin's Creek, Dan's Grove, East Jerome, Fancey Fields, Interhogan, Jolly Pines, Mount Fuji, Test Center
2    Ultima Online    Gold    11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 114, 115, 116, 117, 118, 119    AOL Legends, Arirang, Asuka, Atlantic, Balhae, Baja, Catskills, Chesapeake, Drachenfels, Europa, Formosa, Great Lakes, Hokuto, Izumo, Lake Superior, Napa Valley, Oceania, Pacific, Sonoma, Wakoku, Yamato, Baekdu, Mizuho, Siege Perilous, Lake Austin, Mugen, Sakura
3    Shadow Bane    Gold    32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44    Carnage, Chaos, Corruption, Death, Deception, Dread, Fear, Mourning, Scorn, Test, Treachery, Vengeance, War
4    Ever Quest    Platinum    45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 120, 121, 122, 123    Rallos Zek, Xegony, The Rathe, Fennin Ro, Tarew Marr, Povar, E'ci, Veeshan, Solusek Ro, Cazic-Thule, Mithaniel Marr, Karana, Innoruuk, Tunare, Bertoxxulous, Rodcet Nife, Erollisi Marr, Prexus, Quellious, Brell Serilis, Tallon Zek, Vallon Zek, Bristlebane, The Tribunal, The Nameless, Lanys T'Vyl, Luclin, Druzzil Ro, Torvonnilous, Morell-Thule, Saryrn, The Seventh Hammer, Terris-Thule, Drinal, Tholuxe Paells, Xev, Ayonae Ro, Vazaelle, Zebuxoruk, Sullon Zek, Firiona Vie, Antonius Bayle, Stormhammer, Kane Bayl, Al'Kabor, Maelin Starpyre, Stromm, Test Server
5    Eve Online    Isk    89    Official Server
6    Earth & Beyond    Credits    90    Official Server
7    Dark Age Of...    Gold    91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109    Pendragon, Bedevere, Nimue, Palomides, Igraine, Iseult, Pellinor, Bors, Gawaine, Gaheris, Tristan, Kay, Galahad, Mordred, Percival, Lancelot, Guinevere, Merlin, Morgan
8    Anarchy Online    Credits    110, 111, 112    Atlantean, Rimor, Die Neue Welt
i would like to know can it be done, and if it can how the hell would i do it?

i think the sql str for adCmdText with ADODB.Recordset.Open would look somewhat like the following if it is possible:

Code:
SELECT DISTINCT mmorpg.str_name, STRJOIN(servers.str_name,', ') AS str_srvname, mmorpg.str_currency, STRJOIN(credits.lng_usercredits,', ') FROM mmorpg, servers, credits WHERE servers.lng_rpgid = mmorpg.lng_id AND servers.lng_id = credits.lng_lng_rpgid AND servers.lng_id = credits.lng_serverid ORDER BY servers.str_name ASC, mmorpg.str_name ASC
thing is, i have no idea how to select a column as an array, by distinct id's in a recordset...

any swift help asap would be greatly appreciated.

sincerely,
Pierre
__________________
Sincerely,
Pierre "Greywacke" du Toit
pierre@greywacke.co.za
don't worry about my 0 thankyou's either way, i would say thank you should you help, and i will gladly help others when this work rush is over.
Reply With Quote
  #2 (permalink)  
Old February 10th, 2004, 10:33 AM
Authorized User
 
Join Date: Sep 2003
Location: Wonderboom South, Pretoria, Gauteng, South Africa.
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Greywacke
Default

oki i have worked in access 2002 (XP) with the query editor to return sortof what i want...

Code:
SELECT DISTINCT mmorpg.str_currency, mmorpg.str_name, servers.str_name, credits.lng_usercredits
FROM credits INNER JOIN (servers INNER JOIN mmorpg ON servers.lng_rpgid=mmorpg.lng_id) ON (credits.lng_rpgid=mmorpg.lng_id) AND (credits.lng_serverid=servers.lng_id)
WHERE credits.lng_userid=23 // this will be replaced later in asp with a session variable
ORDER BY mmorpg.str_name, servers.str_name
now the problem is that i need to return the field servers as one field, depicted as an array delimited by ", ".

i need to know asap is this possible and if so how do i go about joining it sothat i get only one record per MMORPG record, but with all the servers listed in that field?

if not, i will just have to come up with another plan...

Pierre
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting records in a query firefighter2045 Access VBA 5 August 28th, 2008 08:14 AM
selecting named ranges from an array TheBFJ Excel VBA 1 November 13th, 2006 07:35 AM
selecting records from excel androoo Pro VB 6 1 January 12th, 2005 12:18 PM
selecting records from multiple tables cygnusx04 Classic ASP Databases 1 October 28th, 2004 05:36 AM
ASP - Selecting Multiple Records Ei0nN Classic ASP Databases 2 May 28th, 2004 03:38 PM



All times are GMT -4. The time now is 07:12 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.