Large Datasets and Comboboxes

[lgbjr]

Hi All,

I need some advice. I have a table in MSDE2000 with about 3 million records.
It's a city database. What I want to do is have 3 comboboxes on a form. The
first would be to select a country, second, to select a region (state,
province, etc) in that country, and the third would be a list of cities in
the selected region.

I don't want 3 million records in my dataset, but I can't figure out how to
set things up so that I'm only getting the records I need. The table has
several columns: Index, CountryCode, Country, RegionCountry, Region, City
(example: 1, US, United States, PA, US, PA, Philadelphia)
I'm using VB 2005 (for now) and I can't figure out how to setup a set or
queries that will give me the data I want without loading all of the records
into the record set first. If I setup queries in the DB, they need to be
parameterized, and I also can't figure out how to do that, but I think
that's the only way that I can keep from having all the records in the table
loaded into my dataset.

Can someone provide me with a bit of guidance?

thanks!
Lee

[Andrew D. Newbould]

In message <#U**************@TK2MSFTNGP12.phx.gbl>, lgbjr
<lg***@nospam.com> writes

Hi All,

I need some advice. I have a table in MSDE2000 with about 3 million records.
It's a city database. What I want to do is have 3 comboboxes on a form. The
first would be to select a country, second, to select a region (state,
province, etc) in that country, and the third would be a list of cities in
the selected region.

I don't want 3 million records in my dataset, but I can't figure out how to
set things up so that I'm only getting the records I need. The table has
several columns: Index, CountryCode, Country, RegionCountry, Region, City
(example: 1, US, United States, PA, US, PA, Philadelphia)

The common approach for this type of scenario is to re-post the form
when a Combo box changes its value to get the information required for
the next Combo box. While this creates round-trips to the server, you
are only fetch the small amount of data required.

For example:

1. Page loads for first time and the Country Combo box is filled using
"SELECT DISTINCT CountryCode, Country FROM MyTable". When the user
selects the Country required the page is reposted with that selection
and we go to step 2.

2. Page loads as step 1 however as well as filling Country Combo we
make sure users previous selection is automatically selected. We also
now fill the Region Combo using "SELECT DISTINCT RegionCountry, Region
FROM MyTable WHERE CountryCode='[UsersChoice]'". When the user selects
the Region required the page is reposted with that selection (and 1) and
we go to step 3.

3. Page loads as step 2 however as well as filling Country and Region
Combos we make sure users previous selections are automatically
selected. We also now fill the City Combo using "SELECT DISTINCT City
FROM MyTable WHERE CountryCode='[UsersChoice]' AND
RegionCountry='[UsersChoice]' ".

4. Finially we post all the criteria back to perform whatever needs
doing.

While this creates work for you as a developer, the experience form the
users perspective is slick (ie: they do not have to wait around) as you
are only fetching small amounts of data with each visit to the server
(they are not waiting for 3 million records to download).

Kind Regards,

--
Andrew D. Newbould E-Mail: ne********@NOSPAMzadsoft.com

ZAD Software Systems Web : www.zadsoft.com