How to get a datagrid to display data associated with a foreign key

Question

User770171000 posted

Hi all,

 I am using a dynamic data web site with LINQ to SQL.  I have created a custom page for one table and that table has two foreign keys that show up fine with the following inside the datagrid:

 

1    <asp:BoundField DataField="respID" HeaderText="Responsibility"  />
2    <asp:BoundField DataField="approverID" HeaderText="Approver" />

 

Unfortunately, when the page loads, these foreign key IDs are displayed as the acutal ID and I want another field (an associated name for each) to be displayed instead.  Keep in mind, this is a dynamic data web site custom page, so these items are part of a metatable (approverresp in this case)

Here is the .cs:

  

1    using System.Web.UI.WebControls;
2    using System.Web.UI.WebControls.WebParts;
3    using System.Web.UI.HtmlControls;
4    using System.Xml.Linq;
5    using System.Web.DynamicData;
6    
7    namespace AMSv0._2.DynamicData.CustomPages.approverResps
8    {
9        public partial class List : System.Web.UI.Page
10       {
11           protected MetaTable table;
12   
13           protected void Page_Init(object sender, EventArgs e)
14           {
15               DynamicDataManager1.RegisterControl(GridView1, true /*setSelectionFromUrl*/);
16           }
17   
18           protected void Page_Load(object sender, EventArgs e)
19           {
20               table = GridDataSource.GetTable();
21   
22               InsertHyperLink.NavigateUrl = table.GetActionPath(PageAction.Insert);
23               // Disable various options if the table is readonly
24               if (table.IsReadOnly)
25               {
26                   GridView1.Columns[0].Visible = false;
27               }
28           }
29       }
30   }

 

The tables are:

approver (ID as integer, name as varchar)

responsibility (ID as integer, name as varchar)

approverresp (ID as integer, FK approverID, FK respID)

 

Thanks so much!

--Jack Stitt

Answer 1

User-1005219520 posted

I think Steven covers this in Creating a Standard Custom Page  and Cust Page part II

Answer 2

User770171000 posted

I can create a custom page.  The problem lies with information from a foreign table... I have the ID but want a name that is associated with that ID (it's in a table that isn't the "current" metadata table)  ... I'll double check those and make sure.

 

edit -- I looked at page II and found the following:

"Now All we have to do is change the foreign key fields to EntitySet/EntityRefs, i.e. CustomerID to Customers and ShipVia to Shipper. As in the last article you can find this information in the Linq to SQL classes designer.cs file."

 

I am not really sure what that means?  I don't know how I can change the datafield to the correct one..

 

Answer 3

User1641955678 posted

Note that you need to use asp:DynamicField, not BoundField.  Also, please see this post for more info on the proper DataField to use.

thanks,
David

Answer 4

User-1005219520 posted

Look in your designer.cs file - there will be an EntityRef  for the FK. Use the EntityRef.

Answer 5

User770171000 posted

Aha, it was a combination of using the correct tag (dynamicfield) and the correct reference.  Thanks all!

Answer 6

User770171000 posted

 Now it is working, I just referenced the entity (approver in this case),  but now it is pulling only the next field in the table instead of the ID.  The whole table is more like:

ID integer, appoverFirstName varchar, approverLastName varchar, approverEmail varchar, and so-on

 

What if I want to reference the approver table but not use the first thing after the ID?   Ie, I want to display information from the foreign table that isn't the first field after the ID (approverLastName in this case)

 

1    	[Table(Name="dbo.approverResp")]
2    	public partial class approverResp : INotifyPropertyChanging, INotifyPropertyChanged
3    	{
4    		private EntityRef<approver> _approver;
5    
6    </approver>

So, I see "approver" as an entity and if I put it in the code:

 
1    <asp:DynamicField DataField=<asp:dynamicfield datafield="<span" class="st">"approver" HeaderText="Approver"  />
2    
</asp:dynamicfield>

I get approverFirstName instead of approverLastName 

Answer 7

User-1005219520 posted

Use the DisplayColumn attribute. Dynamic Data defaults to the first string column, not the first column. My blog Improving the FK field display: Showing two fields in Foreign Key columns shows an example of this. My blog is very close to your code. I show first and last name -  approverLastName , approverFirstName

Answer 8

User770171000 posted
Thanks Rick, that completely solved a problem that has been a thorn in my side for quite a while now.  Very much appreciated and well-written.