[thelist] SQL problem
Steve Cook
steve.cook at evitbe.com
Fri Aug 16 09:01:02 CDT 2002
Hi Kev!
I can think of at least a couple of ways of doing this, though here's a
straightforward method you can use for lots of similar structures.
Preferably you want to avoid the "Recordset in a loop" method - if you had
100 categories you would have to do 100 separate calls to the database -
processor intensive -especially against Access.
Instead, you should match up the info in the database, taking full advantage
of the relational nature of your database.
SQL = "SELECT PKey, Catagory, PrimaryID, Description from Descs JOIN
FaveList ON PKey = pkmatch ORDER BY PKey, PrimaryID"
I open my DB slightly differenlty to you, but you can use any method that
returns a recordSet.
set dbConnection = Server.CreateObject("ADODB.Connection")
dbConnection.Open "DSN=DSNName;uid=login;pwd=password"
set RS = Server.CreateObject("ADODB.Recordset")
RS.Open SQL, dbConnection
This will return a recordset with info formatted like this:
PKey ¦ Catagory Primary_ID ¦ Description
----------------------------------------------------------------------
1 ¦ Music 1 ¦ Cardiacs Homepage
1 ¦ Music 2 ¦ Offspring Homepage
1 ¦ Music 3 ¦
http://antennaradio.com/
2 ¦ Humour 3 ¦ Popbitch
3 ¦ Computer stuff 4 ¦ Evolt
You then can step through the recordset in your page doing something like
this:
oldKey = 0
do while not RS.EOF
if RS("PKey") <> oldKey then ' Check whether it's a new category
Response.Write RS("Catagory")
Response.Write "<select size=""1"" name=""dropdown_menu"">"
& vbCrlf
end if
Response.Write "<option>"
Response.Write RS("Description")
Response.Write "</option>" & vbCrlf
RS.MoveNext
if RS("PKey") <> oldKey then ' Check whether it's a new category
Response.Write "</select><br>" & vbCrlf
end if
loop
(This code isn't tested). Basically you are looping through the recordset.
When you get a new category you close any previous SELECT, print out the
category name and start a new SELECT. This way you make 1 db query and
process it in 1 loop. Nifty huh?
BTW - I've suggested a fun music link for you in the database info above.
Check out their cool and very eclectic stations - should be right up your
street mate ;-)
.steve
-------------------------------------
Cookstour - http://www.cookstour.org
-------------------------------------
> -----Original Message-----
> From: Kevin Stevens [mailto:kjs at ratking.co.uk]
> Sent: den 16 augusti 2002 15:23
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL problem
>
>
<SNIP>
> OK, sorry, I think I've been getting my jargon wrong. I have
> 2 tables, 1
> called Descs which contains just two columns...
> <SNIP>
>
> Hope I've explained myself properly this time :)
>
More information about the thelist
mailing list