DataGrid And Joins

[HeavenCore]

Hi.

I have a DataGrid which displays data from a table adapter. The table adapter uses a JOIN statement (see query and example datagrid at end of post) to join 3 tables on foreign keys, the data grid view also has two columns which are data bound allowing users to select different foreign keys etc.

Obviously though this has one big issue, because the datagrid is bound to a join table it does not allow updates.

I used a join so i could look at the foreign keys and populate the combo boxes with User friendly values from the related tables. How do i get this working?

Regards

Jordon

Join Query:

SELECT     tblJobs.JobDatabaseID, tblJobs.JobNumber, tblPlants.PlantID, tblPlants.PlantGroup_fk, tblPlants.PlantCode, tblPlants.PlantSerialNumber, 
                      tblPlants.PlantValue, tblPlants.PlantSold, tblPlants.PlantOnJob, tblPlants.PlantOnJob_fk, tblPlants.PlantMonths, tblPlants.PlantServiceDueDay, 
                      tblPlants.PlantDefaultCost, tblPlantGroups.PlantGroupID, tblPlantGroups.PlantGroupName
FROM         tblPlants INNER JOIN
                      tblPlantGroups ON tblPlants.PlantGroup_fk = tblPlantGroups.PlantGroupID LEFT OUTER JOIN
                      tblJobs ON tblPlants.PlantOnJob_fk = tblJobs.JobDatabaseID

 

[cjard]

Allow me to present an example.
Suppose I have a data grid view showing product info. 3 of the columns are ID values and i want them to display as nice text values

Example, a screw has a thread depth (how high the screw spiral stands from the core rod), density (how many spirals per inch) and rate (angle of spirals determining speed of screwing)
Suppose these are decoded:

depth: S=shallow, M=medium, D=deep
density: H=high, M=medium, L=low
rate: F=fast, S=slow

Suppose I have this in 3 tables named Depth/Density/Rate
Suppose these columns have colums called Val,Disp val being the letter and Disp being the text described

I have a table that has e.g. columns:

Name, Dep, Den, Rat
Chipboard Screw, D, M, F


I want to decode this in my datagridview, I will need:

1 dataset with 4 tables:
Screw (contains above bold data)
Depth (decodes depth)
Density (decodes density)
Rate (decodes rate)

I will:

Ensure all 4 tables are filled upon form load
Have a data grid view
For Depth, Density and Rate column I will:
Edit Columns..
Set type = DataGridViewComboBoxColumn
DataPropertyName = Rat/Dep/Den appropriately from Screw table
DataSource = Rate/Depth/Density table appropriately
Display Member = Disp
Value Member = Val


Now, when I run my app, the data in use is:

Chipboard Screw, D, M, F

But I see in the grid:

|Chipboard Screw|Deep [V]|Medium [V]| Fast [V]|

That what you want?