DataGridView + Dataset From Mutiple Tables

[johnrowse]

Hi

I can create a DataGridView bound to a datatable, which queries a single table, or even multiple tables via a dataset (xsd). I want to use a query to return data from a foreign table, where a related price is held. I need to display this [readonly] price, so the user can make the right changes to the 'main' data, based on the price it references. However, I cannot get updates to work, as the XSD will not create an update routine for the datatable as a whole, as it queries data outside of the table. I can manually create update commands, but they only except single parameters on a per row basis, rather than taking a datatable, to update changes. Is it possible to achieve what I want?...

Sub Load
adapter.fill(QueriedDataTable)
dgvMain.datasource = QueriedDataTable
End Sub

Sub Update
adapter.update(QueriedDataTable)
End Sub

Thanks in advance

John

 

[MajP]

You should get a away from filling a data table based on a query. Wherever possible add to separate tables that mirror your database tables. You can use expression columns to do what you want.

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=vs.110).aspx

See how to calculate child data.

 

[MajP]

Here is a tutorial

http://msdn.microsoft.com/en-us/library/vstudio/3df0c8ey(v=vs.100).aspx

 

[johnrowse]

Thanks MajP

But the 'price' is not calculated, so can I use an expression (can I use a select statement within an expression)? The main table is essentially an 'order table' and I need to display 'price' within the 'orders', so that the user can reallocate the order, if necessary, on the basis of the price.

Thanks again

John

 

[MajP]

it is calculated in a sense. The calculation says to go to the related data table and grab the value. You need to have a datarelation which you can build in the designer or likely it already exists if your datasource had one defined.

So you have datatableA and datatableB and there is a data relation dataRelationA_B
Assume B is the datatable with a foreign key to table A.
Then in B you can add a calculated field like
Parent(dataRelationA_B).FieldInAName
You can replace Parent with child where appropriate

You can do aggregates. If A is the parent table (primary key)
you can add
Avg(Child.Price)
The above would calculate the average of all child records. If there is only one datarelation for the table you can drop the name of the datarelation as shown above.