Q: Sorting Relations

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

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
 
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 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
 
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("CustomerID"), _
customerDataSet.Tables("Orders").Columns("CustomerID"))

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

' 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
 
Interesting!

Thanks Jay

Geoff

Jay B. Harlow said:
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("CustomerID"), _
customerDataSet.Tables("Orders").Columns("CustomerID"))

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

' 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 said:
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
Click to expand...
Click to expand...
 
Back
Top