By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,911 Members | 1,171 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,911 IT Pros & Developers. It's quick & easy.

Q: Sorting Relations

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Nov 20 '05 #2

P: n/a
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


Nov 20 '05 #3

P: n/a
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



Nov 20 '05 #4

P: n/a
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
>
>



Nov 20 '05 #5

P: n/a
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

Nov 20 '05 #6

P: n/a
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


Nov 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.