Sort a GridView Column related to other Table

Question

Hello,

i have a GridView bound to a DataView. Some columns in the DataView's table are foreignkeys to related tables(f.e. Customer). I want to enable sorting for these columns too, but all i can do is sorting the foreignkey(fiCustomer) and not the CustomerName.

I have tried this without success(" Cannot find column ERP_Customer.CustomerName "):

<asp:TemplateField HeaderText="Customer" SortExpression="ERP_Customer.CustomerName" >

A tried also the DataViewManager, but i've a problem to detect the table to sort:

    Dim daCharge As New ERPModel.dsERPTableAdapters.ERP_ChargeTableAdapter
    daCharge.Fill(dsERP.ERP_Charge)
    Dim viewManager As New DataViewManager(Me.dsERP)
    viewManager.DataViewSettings(dsERP.ERP_Charge).RowFilter = filter
    viewManager.DataViewSettings(dsERP.ERP_Charge).Sort = sort 'sort is the GridView's SortExpression
    Me.GrdCharge.DataSource = viewManager.CreateDataView(dsERP.ERP_Charge)

I have to apply the sort on a distinct table of the DataViewManager, but this table would differ on the related tables. I have bound the TemplateColumns in Codebehind in RowDataBound-Event f.e.:

Dim LblCustomer As Label = DirectCast(e.Row.FindControl("LblCustomer"), Label)
LblCustomer.Text = drCharge.ERP_CustomerRow.CustomerName 'drCharge inherits DataRow

What is the recommended way to sort a GridView on columns related to other tables? I could build a custom datatable with the customername instead of the foreignkey and bind this column to the TemplateField. But then my huge dataset in the model makes no sense anymore.

EDIT: It seems that my question was not clear or too special. Perhaps i can rephrase it in a more general term. I have a model with a Dataset. I'm binding one Datatable(ERP_Charge) from it to my GridView(actually i take a Dataview from that Table). In this Datatable are columns that are related to other Datatables in the Dataset(relations are defined). When i want to make the grid sortable its no problem on the columns that belong to ERP_Charge. But the columns with foreign keys to other table could not be sorted because the Gridview shows f.e. not the CustomerID but the Customername. I get the Customername in RowDataBound. Normally i would join the tables and add a Datacolumn for the Customername. Then i set this "virtual" Datatable as Datasource from the Gridview and i'm able to sort it. But i didnt want to create the datatables in the Page on the fly(it belongs into the model). Do i have to define it in the dataset-Designer? I thought it would be sufficient to define the relationships in the dataset.

UPDATE: i had solved my sorting problems as below.

Regards

Answer 1

UPDATE: i had solved my sorting problems in the following way: I changed my DataSet in the model and joined the related table to the Main-Table(ERP_Charge). I added the columns to the Datatable(f.e. CustomerName) that i want to sort. But i did that without saving it in the configuration wizard of the DataAdapter, because that would delete all Update-,Delete- and InsertCommands. Hence i changed only the SelectCommand with the one that was generated from the Wizard. Therefore i kept the benefit of my Datamodel with the ability to sort for columns of related tables. Maybe queries are a little slower now because of the joins but it works for me.