Q: Sorting Relations

[Geoff Jones]

Hi

I have two tables to which I have created a one-to-many relationship.

How do I sort the result of the relationship? For example, if one column
contains dates, how do I display in the dates in order in a DataGrid?

Thanks in advance

Geoff

[Cor Ligthert]

Hi Geoff,

Why not select them with as the sort order the dates?

Cor

I have two tables to which I have created a one-to-many relationship.

How do I sort the result of the relationship? For example, if one column
contains dates, how do I display in the dates in order in a DataGrid?

Thanks in advance

Geoff

[Geoff Jones]

Hi Cor

Could you give some example code?

Goeff

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:uW**************@TK2MSFTNGP11.phx.gbl...

Hi Geoff,

Why not select them with as the sort order the dates?

Cor

I have two tables to which I have created a one-to-many relationship.

How do I sort the result of the relationship? For example, if one column
contains dates, how do I display in the dates in order in a DataGrid?

Thanks in advance

Geoff

[Cor Ligthert]

Hi Geoff

Dim myselectstring as string = "Select * in MyDetailTable order by mydates"

Than your table is sorted not depending the relation, it looks so simple,
where do I understand you wrong?

Maybe you can as well use the dataview I see now, for which I gave you a
sample in that other message, when you are using two datagrids (one master
and one detail)

Cor

Hi Geoff,

Why not select them with as the sort order the dates?

Cor

I have two tables to which I have created a one-to-many relationship.

How do I sort the result of the relationship? For example, if one column contains dates, how do I display in the dates in order in a DataGrid?

Thanks in advance

Geoff

[Geoff Jones]

Hi Cor

Yes, I can see what you mean. However, I am dealing with events after the
DataAdaptor. I was trying to do it via a DataTable or DataRow. My
understanding what you could only do a Select etc. during the
DataAdaptor/Connection stage and not once you have a DataTable or DataRow?

Geoff

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:OP**************@TK2MSFTNGP12.phx.gbl...

Hi Geoff

Dim myselectstring as string = "Select * in MyDetailTable order by mydates"
Than your table is sorted not depending the relation, it looks so simple,
where do I understand you wrong?

Maybe you can as well use the dataview I see now, for which I gave you a
sample in that other message, when you are using two datagrids (one master
and one detail)

Cor

Hi Geoff,

Why not select them with as the sort order the dates?

Cor

> I have two tables to which I have created a one-to-many relationship. >
> How do I sort the result of the relationship? For example, if one column > contains dates, how do I display in the dates in order in a DataGrid? >
> Thanks in advance
>
> Geoff
>
>

[Jay B. Harlow [MVP - Outlook]]

Geoff,
Have you tried using a DataViewManager?

Something like (modified sample from MSDN sample):

' Create a Connection, DataAdapters, and a DataSet.
Dim connection As SqlConnection = New SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim customerAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT
CustomerID, CompanyName FROM Customers", connection)
Dim orderAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT
OrderID, CustomerID FROM Orders", connection)
Dim orderDetailsAdapter As SqlDataAdapter = New
SqlDataAdapter("SELECT OrderID, ProductID, Quantity FROM [Order Details]",
connection)

Dim customerDataSet As DataSet = New DataSet

' Open the Connection.
connection.Open()

' Fill the DataSet with schema information and data.
customerAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
orderAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
orderDetailsAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey

customerAdapter.Fill(customerDataSet, "Customers")
orderAdapter.Fill(customerDataSet, "Orders")
orderDetailsAdapter.Fill(customerDataSet, "OrderDetails")

' Close the Connection.
connection.Close()

' Create relationships.
customerDataSet.Relations.Add("CustomerOrders", _
customerDataSet.Tables("Customers").Columns("Custo merID"), _
customerDataSet.Tables("Orders").Columns("Customer ID"))

customerDataSet.Relations.Add("OrderDetails", _
customerDataSet.Tables("Orders").Columns("OrderID" ), _
customerDataSet.Tables("OrderDetails").Columns("Or derID"))

' Create default DataView settings.
Dim viewManager As DataViewManager = New
DataViewManager(customerDataSet)

For Each viewSetting As DataViewSetting In
viewManager.DataViewSettings
viewSetting.ApplyDefaultSort = True
Next

' Set sort order for each table.
viewManager.DataViewSettings("Customers").Sort = "CompanyName"
viewManager.DataViewSettings("Orders").Sort = "OrderID"
viewManager.DataViewSettings("OrderDetails").Sort = "ProductID"

' Bind to three DataGrids.
Me.DataGrid1.SetDataBinding(viewManager, "Customers")
Me.DataGrid2.SetDataBinding(viewManager, "Customers.CustomerOrders")
Me.DataGrid3.SetDataBinding(viewManager,
"Customers.CustomerOrders.OrderDetails")

Note we are binding to a ViewManager instead of the DataSet directly.

Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40***********************@news.dial.pipex.com ...

Hi

I have two tables to which I have created a one-to-many relationship.

How do I sort the result of the relationship? For example, if one column
contains dates, how do I display in the dates in order in a DataGrid?

Thanks in advance

Geoff

[Geoff Jones]

Interesting!

Thanks Jay

Geoff

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:eX**************@TK2MSFTNGP11.phx.gbl...

Geoff,
Have you tried using a DataViewManager?

Something like (modified sample from MSDN sample):

' Create a Connection, DataAdapters, and a DataSet.
Dim connection As SqlConnection = New SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim customerAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT
CustomerID, CompanyName FROM Customers", connection)
Dim orderAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT
OrderID, CustomerID FROM Orders", connection)
Dim orderDetailsAdapter As SqlDataAdapter = New
SqlDataAdapter("SELECT OrderID, ProductID, Quantity FROM [Order Details]",
connection)

Dim customerDataSet As DataSet = New DataSet

' Open the Connection.
connection.Open()

' Fill the DataSet with schema information and data.
customerAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey orderAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
orderDetailsAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey

customerAdapter.Fill(customerDataSet, "Customers")
orderAdapter.Fill(customerDataSet, "Orders")
orderDetailsAdapter.Fill(customerDataSet, "OrderDetails")

' Close the Connection.
connection.Close()

' Create relationships.
customerDataSet.Relations.Add("CustomerOrders", _
customerDataSet.Tables("Customers").Columns("Custo merID"), _
customerDataSet.Tables("Orders").Columns("Customer ID"))

customerDataSet.Relations.Add("OrderDetails", _
customerDataSet.Tables("Orders").Columns("OrderID" ), _
customerDataSet.Tables("OrderDetails").Columns("Or derID"))

' Create default DataView settings.
Dim viewManager As DataViewManager = New
DataViewManager(customerDataSet)

For Each viewSetting As DataViewSetting In
viewManager.DataViewSettings
viewSetting.ApplyDefaultSort = True
Next

' Set sort order for each table.
viewManager.DataViewSettings("Customers").Sort = "CompanyName"
viewManager.DataViewSettings("Orders").Sort = "OrderID"
viewManager.DataViewSettings("OrderDetails").Sort = "ProductID"

' Bind to three DataGrids.
Me.DataGrid1.SetDataBinding(viewManager, "Customers")
Me.DataGrid2.SetDataBinding(viewManager, "Customers.CustomerOrders") Me.DataGrid3.SetDataBinding(viewManager,
"Customers.CustomerOrders.OrderDetails")

Note we are binding to a ViewManager instead of the DataSet directly.

Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40***********************@news.dial.pipex.com ...

Hi

I have two tables to which I have created a one-to-many relationship.

How do I sort the result of the relationship? For example, if one column
contains dates, how do I display in the dates in order in a DataGrid?

Thanks in advance

Geoff