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**********@p lanet.nl> wrote in message
news:uW******** ******@TK2MSFTN GP11.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**********@p lanet.nl> wrote in message
news:OP******** ******@TK2MSFTN GP12.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=localhos t;Integrated Security=SSPI;I nitial Catalog=northwi nd")

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 orderDetailsAda pter 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 .MissingSchemaA ction = MissingSchemaAc tion.AddWithKey
orderAdapter.Mi ssingSchemaActi on = MissingSchemaAc tion.AddWithKey
orderDetailsAda pter.MissingSch emaAction =
MissingSchemaAc tion.AddWithKey

customerAdapter .Fill(customerD ataSet, "Customers" )
orderAdapter.Fi ll(customerData Set, "Orders")
orderDetailsAda pter.Fill(custo merDataSet, "OrderDetai ls")

' Close the Connection.
connection.Clos e()

' Create relationships.
customerDataSet .Relations.Add( "CustomerOrders ", _
customerDataSet .Tables("Custom ers").Columns(" CustomerID"), _
customerDataSet .Tables("Orders ").Columns("Cus tomerID"))

customerDataSet .Relations.Add( "OrderDetai ls", _
customerDataSet .Tables("Orders ").Columns("Ord erID"), _
customerDataSet .Tables("OrderD etails").Column s("OrderID"))

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

For Each viewSetting As DataViewSetting In
viewManager.Dat aViewSettings
viewSetting.App lyDefaultSort = True
Next

' Set sort order for each table.
viewManager.Dat aViewSettings(" Customers").Sor t = "CompanyNam e"
viewManager.Dat aViewSettings(" Orders").Sort = "OrderID"
viewManager.Dat aViewSettings(" OrderDetails"). Sort = "ProductID"

' Bind to three DataGrids.
Me.DataGrid1.Se tDataBinding(vi ewManager, "Customers" )
Me.DataGrid2.Se tDataBinding(vi ewManager, "Customers.Cust omerOrders")
Me.DataGrid3.Se tDataBinding(vi ewManager,
"Customers.Cust omerOrders.Orde rDetails")

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

Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSP AM.com> wrote in message
news:40******** *************** @news.dial.pipe x.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******** ******@TK2MSFTN GP11.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=localhos t;Integrated Security=SSPI;I nitial Catalog=northwi nd")

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 orderDetailsAda pter 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 .MissingSchemaA ction = MissingSchemaAc tion.AddWithKey orderAdapter.Mi ssingSchemaActi on = MissingSchemaAc tion.AddWithKey
orderDetailsAda pter.MissingSch emaAction =
MissingSchemaAc tion.AddWithKey

customerAdapter .Fill(customerD ataSet, "Customers" )
orderAdapter.Fi ll(customerData Set, "Orders")
orderDetailsAda pter.Fill(custo merDataSet, "OrderDetai ls")

' Close the Connection.
connection.Clos e()

' Create relationships.
customerDataSet .Relations.Add( "CustomerOrders ", _
customerDataSet .Tables("Custom ers").Columns(" CustomerID"), _
customerDataSet .Tables("Orders ").Columns("Cus tomerID"))

customerDataSet .Relations.Add( "OrderDetai ls", _
customerDataSet .Tables("Orders ").Columns("Ord erID"), _
customerDataSet .Tables("OrderD etails").Column s("OrderID"))

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

For Each viewSetting As DataViewSetting In
viewManager.Dat aViewSettings
viewSetting.App lyDefaultSort = True
Next

' Set sort order for each table.
viewManager.Dat aViewSettings(" Customers").Sor t = "CompanyNam e"
viewManager.Dat aViewSettings(" Orders").Sort = "OrderID"
viewManager.Dat aViewSettings(" OrderDetails"). Sort = "ProductID"

' Bind to three DataGrids.
Me.DataGrid1.Se tDataBinding(vi ewManager, "Customers" )
Me.DataGrid2.Se tDataBinding(vi ewManager, "Customers.Cust omerOrders") Me.DataGrid3.Se tDataBinding(vi ewManager,
"Customers.Cust omerOrders.Orde rDetails")

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

Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSP AM.com> wrote in message
news:40******** *************** @news.dial.pipe x.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