locked
How to get a datagrid to display data associated with a foreign key RRS feed

  • 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

    Monday, December 8, 2008 12:18 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 8, 2008 3:13 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 10, 2008 1:07 PM

All replies

  • User-1005219520 posted

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

    Monday, December 8, 2008 2:37 PM
  • 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..

     

    Monday, December 8, 2008 2:44 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 8, 2008 3:13 PM
  • User-1005219520 posted

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

    Monday, December 8, 2008 3:22 PM
  • User770171000 posted

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

    Monday, December 8, 2008 4:33 PM
  • 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 
    Wednesday, December 10, 2008 10:50 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 10, 2008 1:07 PM
  • 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.
    Thursday, December 11, 2008 2:34 PM