I'm using Products, Categories, and Suppliers tables from NorthWind database.
I want to retrieve the data of one product at a time.
I'm not using Stored Procedures.
I using two comboboxes cboSupplier & cboCategory to populate related data.

When selecting a product, I run the following code
DataSet1.clear
DataAdapter1.Fill(DataSet1, "Categories")
DataAdapter1.Fill(DataSet1, "Suppliers")
DataAdapter1.Fill(DataSet1, "Products")

If what I'm doing is correct, then everytime I select a different product I have to re-fill the suppliers & categories tables.

How can avoid re-fill the two reference tables?


>How are you pulling down the data? Are you using Stored Procedures or sending the query directly to the back end? You don't *have* to pull the data from all the tables. We're using SP's for all our data access ... the SP's will fill the appropriate tables. If a table has no data in it, then nothing actually comes back over the wire for that table. This way, we can use one DataSet for mulitiple purposes.
>
>There are no hard-and-fast rules for this stuff and there are always exceptions to the rules anyway ... I don't know exactly what you are trying to do, or how you are setting up your app ... but perhaps my suggestions are over-kill for your purposes.
>
>~~Bonnie
>
>>I tried the typed dataset.
>>
>>1) I created the proper relations between all three tables and I came to a conclusion that that every time I want to retrieve data of a product I have to retrieve all the data of the reference tables.
>>
>>2)Without creating the relations between tables, also I have to retrieve all all the data of the reference tables.
>>
>>I don't think that this is the ultimate approach.
>>
>>
>>>Nabil,
>>>
>>>Yes, that is correct. In most cases, you don't want to pull down every category and supplier, only those that pertain to the product that was chosen. In most cases, it's best to minimize both the amount of data that you pull across the wire and also minimize the round-trips back to the server ... sometimes you have to walk a fine line as to what will give you the best performance.
>>>
>>>~~Bonnie
>>>
>>>
>>>>>>What is the best practice in retrieving the data of a table and all related reference tables. For example the table [Products] in northwind database and the related reference tables [Categories] & [Suppliers].
>>>>>>Create one dataset with all three tables?
>>>>>>Create three datasets?
>>>>>
>>>>>Well, I think that´s create one dataset with everything... we can think of a dataset as a DataEnvironment in VFP...
>>>>
>>>>What about if I want to populate the Categories & Suppliers data in a comboboxes, so every time I retrieve data of a different product I have CLEAR and FILL (REQUERY) all the data of Categories & Suppliers tables again. Is that correct or am I missing something?