parent column in child row

[Frank]

Hello,
I have a dataset with 2 tables and a relation (parent - child). I linked
that ds to a datagrid. Shows everything fine (very little coding for such
functionality!).
But.. in the child row I want to show a column of the parent. Example:
orderColumn shows customerId and I want the customerName which is located in
the parent.
How do I do that?
Thanks in advance
Frank

 

[Frank]

Is this question so difficult or is it impossible what I want? I can't be
the first one to bump into this problem. Plse tell me if it is not possible
then I can look into another direction.
Frank

 

[Cor Ligthert]

Hi Frank,

I have no direct answer, however when it was my problem I would add an extra
column to the childtable.

You can make a column like this
dim datatable.column.add(NameString,Type, expression)

http://msdn.microsoft.com/library/d...de/html/cpconaddingdatacolumnstodatatable.asp

This is how to use an expression, what I did never do was with a parent
table, however it is described I saw.

http://msdn.microsoft.com/library/d...fsystemdatadatacolumnclassexpressiontopic.asp

I never tried it.

However I hope it helps?

Cor

 

[Mike McIntyre [MVP]]

Frank,

You can do what you want by adding a calculated column to the child
DataTable that contains and expression that references a parent row column.

Learn more at the links below:

http://msdn.microsoft.com/library/d...fsystemdatadatacolumnclassexpressiontopic.asp

http://msdn.microsoft.com/library/d...de/html/cpconaddingdatacolumnstodatatable.asp

Below is some code for a button click handler that demonstrates how to
accomplish your goal. If you create a Windows Forms project, add a Button1
and a DataGrid1, add this code below to the form code, and make sure the
SqlConnection is valid for your Sql server you can see this code in action.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

' Open a database connection.

Dim strConnection As String = _

"Data Source=localhost;Initial Catalog=Northwind;" _

& "Integrated Security=True"

Dim cn As SqlConnection = New SqlConnection(strConnection)

cn.Open()

' Set up a data adapter object.

Dim strSql As String = "SELECT * FROM Customers" _

& " WHERE City = 'Buenos Aires' AND Country = 'Argentina'"

Dim da As SqlDataAdapter = New SqlDataAdapter(strSql, cn)

' Load a data set.

Dim ds As DataSet = New DataSet()

da.Fill(ds, "Customers")

' Set up a new data adapter object.

strSql = "SELECT Orders.*" _

& " FROM Customers, Orders" _

& " WHERE (Customers.CustomerID = Orders.CustomerID)" _

& " AND (Customers.City = 'Buenos Aires')" _

& " AND (Customers.Country = 'Argentina')"

da = New SqlDataAdapter(strSql, cn)

' Load the data set.

da.Fill(ds, "Orders")

' Close the database connection.

cn.Close()

' Create a relation.

ds.Relations.Add("CustomerOrders", _

ds.Tables("Customers").Columns("CustomerID"), _

ds.Tables("Orders").Columns("CustomerID"))

' Create a child row calculated column that shows

' a datacolumn from the child row's parent.

Dim companyNameColumn As New DataColumn("CompanyName",
System.Type.GetType("System.String"))

companyNameColumn.Expression = "Parent.CompanyName"

ds.Tables("Orders").Columns.Add(companyNameColumn)

Me.DataGrid1.DataSource = ds

End Sub

 

[Frank]

Mike and Cor,
thanks!!!, especially the last lines of the detailed code from Mike helped.
I looked into the added datacolumn myself but I could not get the expression
do what I wanted. But it turns out to be more simple than I thought. Strange
that I didn't find an example like Mikes, maybe I used the wrong search
keywords.
Frank