VB .NET Question. Problem in looking up a value in child table using PK-FK relationship. Plz suggest solution?

Question

I have 2 tables:

tblCountries(country_id int(4) Identity PK, country_name varchar(30))

tblCities(city_id int(4) Identity PK, city_name varchar(30), country_id int(4) FK)

I am loading all the country names from tblCountries in a combo box.

I want when the user selects a particular country from the combo, the cities associated with that country should appear in another combo box (from tblCities through PK-FK relationship)

Now, If the country id's were loaded in the countries combo, then on SelectedIndexChanged of countries combo, I can easily look up the selected item in tblCities to find the city name against that country_id. The problem is, when I load the country names in the combo, then, on SelectedIndexChanged event of countries combo, how should i load the city names in the cities combo ?

One solution is to first look up the id against a country name in countries table, then use that id as a lookup in cities table
which i think is not a good logic. PLZ HELP!

Answer 1

Load the country name and country ID into an array. Then when the country name is selected, check the array for the country ID to query for cities in the cities tbl

Hope that helps

Answer 2

Dude use AJAX.
Dont try to submit the Entire Form.you will end up in a Mess much faster than your Manger.Ok. Lets loook at the problem.
Create Query String on your SelectedIndexEvent with a query parameter say query_cities.aspx?country_id=value
In this just go to the database retrieeve teh results and resend it back. In the Java Script you can handle the code for Displaying in the Combo-box.
In this way your form wont have to submit to the server also ..Only a query will occur to database in the background.

Answer 3

Get the name of the country you are looking up based off of the cmb.text field. Then, you can pass that name in to the query for the cities. The query should simply be:

select c.city_name from tblCities c, tblCountries co where co.country_name = "yourcountryname" and c.country_id = co.country_id)

So, yes, you are correct in your logic assumption. You just dont need to do it in two separate lookups.