need help defining logic for multiple table lookup/update

[kateryn]

I have three tables
first table: vendors consists of the following columns:
vendor id (unique), address_id , specialty_id (address_id and specialty_id columns compose a unique key)

Second table: Addresses consists of the following columns:
address id (unique/key) - relationship to vendor table,
Addr_Name, addr1, addr1, city, state, zip

Third table: Specialty consists of the following columns:
Specialty id (unique/key) - relationship to vendor table
Specialty_desc

Concept is that I can have an address_id listed in the vendor table more than once with a different specialty_id.
IE: a vendor can have more than one specialty.

I want to create a lookup/edit form that displays the address name, addr1, addr2, city, state, zip and specialty description.

I want to build a dataset table that is limited to certain specialty type and display them in the lookup/edit form.

I would like the form to contain a combo box that displays all the vendor names from the dataset table.

The problem I am running into is the one dealing with the specialty description - I seem to be creating a list that holds the vendor name multiple times - once for each specialty. I would like the vendor name to appear only once. And I would like a datagrid/list of all the specialty descriptions associated with that vendor to display.

And I want to be able to add more specialties to an existing vendor.

I know that this is complicated. But, because I don't know vb .net and its ado interface well enough - I am spinning my wheels trying to move to the next step. An outline of the steps needed to set it up would be appreciated.

My current outline is:
initialization:
in dataset vendor_addresses
fill vendors
fill addresses
fill specialty
build vendor_specialty table in dataset vendor_addresses
'filter vendor for specified specialties and build table
'of addresses, specialties, and vendor_id
end initialization

Load form:
select all address names from dataset vendor_addresses
using a datareader
load into vendor name combobox.
end datareader process
bind the textboxes to the vendor_addresses table
???
end Load

The ??? is where I am stuck. How do I handle the specialty_description? Currently I have an entry in the vendor_address table for each vendor/specialty combination so a vendor name appears several times - but this won't be helpful to the end-user. I want the vendor name to appear only once in the combo-box. So, should I be adding an additional filter to build the combo-box to display the vendor's name? And how do I show the specialties associated with the vendor name? A list box? a datagrid? and how/where does one fill it in the logic? Do I need to add more adapters/olecommands/sqlcommands and connections? or views? None of my books (of which I have six) is giving me much help with this real world situation.

 

[younguser]

I just finished the DB like what you want. Here are some idea:
1) you need a function to fill specialty combobox when loading the main form
2) you need a function to get specialty_ID if pass name, this ID is store in vendor, because in combobox, user only see the name. If user change the name, ID should be changed in order to save in vendors table
3)need sql statement to inner join three tables and create dataview to bind to datagrid.

 

[kateryn]

So, Younguser,

I'm not understanding - as in you're just a little too vague. How does what you gave me display all the specialities that the vendor may have associated with it? And allow my user to add new specialities (thus creating a new vendor ID) for this vendor?

Thanks

 

[younguser]

I only give you idea how to create main data entry form in which there may be one or more combobox.
For specialty table and another table which use for combobox, you need create another data entry form. If you need sample, you can download from bellow and study "data entry form" sample.

http://www.microsoft.com/downloads/...b9-5aeb-4f46-9bf0-2b3e3664be77&DisplayLang=en

 

[RiverGuy]

This is what I would do:

1. Create a dataset
2. Fill 3 tables in the dataset, one each for all of your data in your three tables.
3. Create DataRelations among your three tables.
4. Bind your combo box to the vendor table.
5. Bind your other controls you are using (datagrid, textboxes, whatever), to your child records based off of your DataRelations.

 

[kateryn]

Okay, I've stripped the code and components. This was just too much work for somehting that can be done so easily in vb 6 or access.

 

[RiverGuy]

Too much work? Its not much work at all. Once you have it done once, it will be easy for you after that. I wouldn't change which language you are coding something in just because one part of it is unfamiliar.