SQL query to populate dropdown

Sorry for cross-posting this to microsoft.public.inetserver.asp.db - I
didn't know if that group saw much action.

Hi All,

I'm very new to ASP and am now working on an existing site that is
written in ASP. I'm trying to populate a dropdown box with the results of
a query. There is current code that does this but it populates with team
name whereas I want division name.

The database is set up as:
League - connected to - Division - connected to Team

I want to be able to have someone choose a league and then on the next
page populate a dropdown with all the divisions in that league.

Here is the working code.

<%sql = "SELECT division.name, team.id, team.name as team, [team-
division].season
from (([division-league]
inner join division on division.id = [division-league].division)
inner join  [team-division] on [team-division].division = division.id)
inner join team on team.id = [team-division].team
where league=" & thisleague
cmd.CommandText = sql
rs.Open cmd, , 1,1
do while not rs.eof
seasonid = rs("season")%>
<option value="<%=rs("id")%>"><%=rs("team")%> (<%=rs
("name")%> )
<%rs.movenext%>
<%loop
rs.close%>

I have tried many things but I think just changing the query as follows,
should work but have had no success.

<%sql = "SELECT division.name, division.id from [division-league],
division
where [division.league].id = division.id
and league=" & thisleague

Thanks,

Dave

FatBear wrote:
> Sorry for cross-posting this to microsoft.public.inetserver.asp.db -
The problem is: you did not crosspost it: you multiposted it. If you had
crossposted it by sending a single message to both groups, nobody would
have a problem with it.
> I
> didn't know if that group saw much action.

Posts there get as much attention as posts here

----canned response ---------------------------
Please do not multipost. This is definitely a database-related question
so .asp.db was the perfect group in which to post it. Posting it here as
well did not increase your chances of getting an answer (most of us
subscribe to both groups). On the contrary, if somebody had taken his
time
to answer it here, only to find that it was already resolved in the
other
group, that person may have been annoyed enough to ignore any future
posts
from you, thereby decreasing your chances of getting help in the future.

There are times when you will not be sure which group is most
appropriate,
and you will want to post a question to both groups. In that situation,
you
should use the cross-posting technique, rather than posting the same
message
multiple times. To crosspost, put a semicolon-delimited* list of the
newsgroups to which you wish to post in the To: header of your post and
post
it once. It, and any replies to it, will appear in all the newsgroups in
your list. So, if I reply in .asp.db, my reply will also appear here in
.asp.general.

* ... or whatever delimiter is recognized by your news client
------------------------------------------------------------------------
----------

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Could you try this?

<%
sql = "SELECT division.name as divisionName, team.id, team.name as team,
[team-division].season
from (([division-league]
inner join division on division.id = [division-league].division)
inner join  [team-division] on [team-division].division = division.id)
inner join team on team.id = [team-division].team
where league=" & thisleague
cmd.CommandText = sql
rs.Open cmd, , 1,1
do while not rs.eof
seasonid = rs("season")%>
<option value="<%=rs("id")%>"><%=rs("divisionName")%>
<%rs.movenext%>
<%loop
rs.close%>

Jess

"FatBear" wrote:

> Sorry for cross-posting this to microsoft.public.inetserver.asp.db - I
> didn't know if that group saw much action.
>
> Hi All,
>
> I'm very new to ASP and am now working on an existing site that is
> written in ASP. I'm trying to populate a dropdown box with the results of
> a query. There is current code that does this but it populates with team
> name whereas I want division name.
>
> The database is set up as:
> League - connected to - Division - connected to Team
>
> I want to be able to have someone choose a league and then on the next
> page populate a dropdown with all the divisions in that league.
>
> Here is the working code.
>
> <%sql = "SELECT division.name, team.id, team.name as team, [team-
> division].season
> from (([division-league]
> inner join division on division.id = [division-league].division)
> inner join  [team-division] on [team-division].division = division.id)
> inner join team on team.id = [team-division].team
> where league=" & thisleague
> 	cmd.CommandText = sql
> 	rs.Open cmd, , 1,1
> 		do while not rs.eof
> 			seasonid = rs("season")%>
> 			<option value="<%=rs("id")%>"><%=rs("team")%> (<%=rs
> ("name")%> )
> 			<%rs.movenext%>
> 		<%loop
> 	rs.close%>
>
> I have tried many things but I think just changing the query as follows,
> should work but have had no success.
>
> <%sql = "SELECT division.name, division.id from [division-league],
> division
> where [division.league].id = division.id
> and league=" & thisleague
>
> Thanks,
>
> Dave
>