SQL Query - Populate Drop Down

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")%>
"><%=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

Re: SQL Query - Populate Drop Down

FatBear wrote:
> 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")%>
> "><%=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.

What are the symptoms that tell you that you have had no success? Don't
forget, we have no access to your data so we can't run this code
ourselves to see what symptoms you are getting.
>
> <%sql = "SELECT division.name, division.id from [division-league],
> division
> where [division.league].id = division.id
> and league=" & thisleague
>

You should supply a few rows of sample data (in tabular format),
followed by desired results from that sample data (also in tabular
format), followed by the bad results this query is giving you.

--
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.

Re: SQL Query - Populate Drop Down

"Bob Barrows [MVP]" wrote in news:e
$a3H8vnIHA.6064@TK2MSFTNGP03.phx.gbl:

> FatBear wrote:
>> 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")%>
>> "><%=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.
>
> What are the symptoms that tell you that you have had no success? Don't
> forget, we have no access to your data so we can't run this code
> ourselves to see what symptoms you are getting.
>>
>> <%sql = "SELECT division.name, division.id from [division-league],
>> division
>> where [division.league].id = division.id
>> and league=" & thisleague
>>
>
> You should supply a few rows of sample data (in tabular format),
> followed by desired results from that sample data (also in tabular
> format), followed by the bad results this query is giving you.
>

Hi Bob,

Thanks for your response and I promise I won't crosspost anymore. Here
are more details regarding the problem:

1. It is an access database.
2. The problem is that no data appears in the dropdown. The box is there
but there are no values in it.
3. Here are the tables I'm using:

LEAGUE
ID Name
145 Monday night Shinny - JD

DIVISION
ID Name
176 Team A
177 Team B
178 Team C
179 Team D
180 Team E

DIVISION-LEAGUE
ID Division League
176 177 145
177 178 145
178 179 145
179 180 145
180 181 145

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

thisleague is a variable that equates to 145 in this case.

Please let me know if you need anything else.

Thanks,

Dave

Re: SQL Query - Populate Drop Down

FatBear wrote:
> "Bob Barrows [MVP]" wrote in news:e
> $a3H8vnIHA.6064@TK2MSFTNGP03.phx.gbl:
>
>> FatBear wrote:
>>> 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")%>
>>> "><%=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.
>>
>> What are the symptoms that tell you that you have had no success?
>> Don't forget, we have no access to your data so we can't run this
>> code ourselves to see what symptoms you are getting.
>>>
>>> <%sql = "SELECT division.name, division.id from [division-league],
>>> division
>>> where [division.league].id = division.id
>>> and league=" & thisleague
>>>
>>
>> You should supply a few rows of sample data (in tabular format),
>> followed by desired results from that sample data (also in tabular
>> format), followed by the bad results this query is giving you.
>>
>
> Hi Bob,
>
> Thanks for your response and I promise I won't crosspost anymore. Here
> are more details regarding the problem:
>
> 1. It is an access database.
> 2. The problem is that no data appears in the dropdown. The box is
> there but there are no values in it.
> 3. Here are the tables I'm using:
>
> <%sql = "SELECT division.name, division.id from [division-league],
> division
> where [division.league].id = division.id
> and division.league=" & thisleague
>
> thisleague is a variable that equates to 145 in this case.
>

Have you verified that with

Response.write sql

?
--
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.

Re: SQL Query - Populate Drop Down

"Bob Barrows [MVP]" wrote in
news:e8Nq97xnIHA.4036@TK2MSFTNGP05.phx.gbl:

> FatBear wrote:
>> "Bob Barrows [MVP]" wrote in news:e
>> $a3H8vnIHA.6064@TK2MSFTNGP03.phx.gbl:
>>
>>>> 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")%>
>>>> "><%=rs("team")%> (<%=rs
>>>> ("name")%>)
>>>> <%rs.movenext%>
>>>> <%loop
>>>> rs.close%>
>>>>
>>>> <%sql = "SELECT division.name, division.id from [division-league],
>>>> division
>>>> where [division.league].id = division.id
>>>> and league=" & thisleague
>>>
>> Hi Bob,
>>
>> Thanks for your response and I promise I won't crosspost anymore. Here
>> are more details regarding the problem:
>>
>> 1. It is an access database.
>> 2. The problem is that no data appears in the dropdown. The box is
>> there but there are no values in it.
>> 3. Here are the tables I'm using:
>>
>> <%sql = "SELECT division.name, division.id from [division-league],
>> division
>> where [division.league].id = division.id
>> and division.league=" & thisleague
>>
>> thisleague is a variable that equates to 145 in this case.
>>
>
> Have you verified that with
>
> Response.write sql
>
> ?

Yes, it has been verified with response.write sql.

Re: SQL Query - Populate Drop Down

FatBear wrote:
>>> 1. It is an access database.
>>> 2. The problem is that no data appears in the dropdown. The box is
>>> there but there are no values in it.
>>> 3. Here are the tables I'm using:
>>>
>>> <%sql = "SELECT division.name, division.id from [division-league],
>>> division
>>> where [division.league].id = division.id
>>> and division.league=" & thisleague
>>>
>>> thisleague is a variable that equates to 145 in this case.
>>>
>>
>> Have you verified that with
>>
>> Response.write sql
>>
>> ?
>
> Yes, it has been verified with response.write sql.
And when you run that query in Access you get results?
If that's the case, the problem is with the code after this point.

--
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.