I am attempting to set up a drop down menu to help me with a search on my site. It goes like this... I have a site where the users can search for videos specific to a particular country. So I have small database like this:

<b>TBL_Country</b>
CountryID
CountryName

(This table has a listing of several countries, even tough there may not always be a video listed for every country.)

<b>TBL_Videos</b>
VideoID
VideoTitle
CountryID

Now, I want to populate the dropdown list with all the countries that have videos listed. So I create a "select DISTINCT CountryID" recordset for TBL_Videos. This is fine, but it only gives me the Country ID for the country and not the country name.

How can I compare the values in the dropdown list (populated with countryID from TBL_Videos) and have the label be the corresponding CountryName from TBL_Country?

Referral Link <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

www.dmxzone.com/forum/topic.asp?TOPIC_ID=23411&FORUM_ID=7&CAT_ID=5&Topic_Title=Post+Back%3F%3F+List%2FMenus&Forum_Title=ASP

L8R,
Vitoman, M CIW D, MMCP
----------------------------------
He conquers who endures. - Persius

Thanks for the reply vitoman, but I don't think that is what I am searching for... I don't need two drop down menus, but rather 1 menu populated from two tables. I need the value to be populated with the countryID from TBL_Videos where I have selected all of the DISTINCT countryID's. I need the label of the menu to be populated with the corresponding name for the countryID and populated from TBL_Country.

Any thoughts??? Thanks.

Hmmm...This is what I'm thinking (thought you smelled smoke, right?)

If you have two combo boxes, one labeled Country and the other labeled Videos, you can select the Country and it would automatically update the Videos combox box.

In regards to the values of the countries, they would be included in the combobox, like below:

&lt;option value="1" selected="selected"&gt;USA&lt;/option&gt;

The value would be your country code from the table, of course.

Hope this helps...



L8R,
Vitoman, M CIW D, MMCP
----------------------------------
He conquers who endures. - Persius

I <b>REALLY</b> need the results to show up in one dropdown menu. So the value is populated from TBL_Video and the label is populated with the corresponding country name from TBL_Country.

The following should work then.

1). SQL statement
SELECT
CountryID, VideoID, CountryName, VideoTitle
FROM
TBL_Country as Country, TBL_Videos as Videos
WHERE
Country.CountryID = Videos.CountryID
ORDER BY
VideoID

2). Cycle through the list of records and populate the combobox (Not the right syntax, but this is for demonstration porpoises) <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

&lt;option value="&lt;CountryID or Video ID&gt;"&gt;&lt;%=CountryID%&gt; - &lt;%=CountryName%&gt; (&lt;%=VideoTitle%&gt<img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>&lt;/option&gt;

Hope this helps...


L8R,
Vitoman, M CIW D, MMCP
----------------------------------
He conquers who endures. - Persius

Whoops! The last Parenthese should have been a Emoticon winking.

L8R,
Vitoman, M CIW D, MMCP
----------------------------------
He conquers who endures. - Persius

What exactly does the "AS" clause do?

It gives a table an alias so it can be referred to without confusion.

(Community? Surveeeeyyy SAYS...) <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>


L8R,
Vitoman, M CIW D, MMCP
----------------------------------
He conquers who endures. - Persius

Thanks for your help vitoman... that seemed to work perfectly.