foreign keys and dropdowns in DD custom page : The Official Microsoft ASP.NET Forums

Reply

fgeorge

Member

29 Points

foreign keys and dropdowns in DD custom page

Sep 23, 2009 11:41 PM|LINK

I Have followed steves tutorial at http://csharpbits.notaclue.net/2008/07/part-3-standard-aspnet-page-with.html to create a custom page in the root of my site using a details view.

I have two questions;

1) The table i want to update has a foreign key which i want to hide and instead, display a friendly field from the foreign key table in the detailsview . How can i do this?

2) Secondly, when i click on the edit button in the detailsview, instead of a textbox to edit the foreign key ID, i want a dropdown to show friendly fields form the foregn key table. Any ideas how i can do this?

Thanks

Reply

nijhawan.sau...

All-Star

16394 Points

3170 Posts

Re: foreign keys and dropdowns in DD custom page

Sep 24, 2009 12:58 AM|LINK

Hi,

for the first question instead of just binding the table to detailsview, bind it to an template field, write a query, havning a join b/w the two tables and get the corresponding foreign key value and display it.

About the second question, you need to have a dropdownlist in the edit template of details view, have it's datasource as well and get it's value according to a control parameter.

Reply

sjnaughton

All-Star

27308 Points

5458 Posts

MVP

Re: foreign keys and dropdowns in DD custom page

Sep 24, 2009 09:57 AM|LINK

Hi Fgeorge, whe setting up the dynamic field for the field you want to show the drop down list you need to use the navigation property not the FK field i.e. in the Orders table of Northwind you would use the Employee field not the EmployeeId field, does this make sense? [:)]

See my blog C# Bits | Twitter @sjnaughton
Always seeking an elegant solution.

Reply

fgeorge

Member

29 Points

198 Posts

Re: foreign keys and dropdowns in DD custom page

Sep 24, 2009 11:40 AM|LINK

Thanks steve..i did this and it didnt work..pls see the code i used below;

I used AdventureworksLT..I have 2 foreign keys, ProductCategoryID and ProductModelID. What do i change these values to? There is a field in the ProductCategory Table called Name but if i use that, it will clash with a dynamic field i have used earlier in the code.

Please see the code i used below. This works perfectly but without showing dropdowns for the foreign keys when i click on "edit".

Thanks

<form id="form1" runat="server">
        <div>
    <asp:ValidationSummary ID="ValidationSummary1" runat="server" EnableClientScript="true"
    HeaderText="List of validation errors" />
<asp:DynamicDataManager ID="ddmProducts" runat="server" />
<asp:DynamicValidator runat="server"
    ID="ddvProducts"
    ControlToValidate="dvProducts"
    Display="None">
</asp:DynamicValidator>




        <asp:DetailsView ID="dvProducts" runat="server" Height="50px" Width="125px"
            AutoGenerateEditButton="True" DataSourceID="ldsProducts"
            AutoGenerateRows="False" DataKeyNames="ProductID"
            onitemupdated="dvProducts_ItemUpdated">
            <Fields>
                <asp:DynamicField  DataField="ProductID" />
                <asp:DynamicField  DataField="Name"/>
                <asp:DynamicField  DataField="ProductNumber" />
                <asp:DynamicField  DataField="Color" />
                <asp:DynamicField  DataField="StandardCost"/>
                <asp:DynamicField  DataField="ListPrice" />
                <asp:DynamicField  DataField="Size" />
                <asp:DynamicField  DataField="Weight"/>
                <asp:DynamicField  DataField="ProductCategoryID"/>
                <asp:DynamicField  DataField="ProductModelID" />
                <asp:DynamicField  DataField="SellStartDate"/>
                <asp:DynamicField  DataField="SellEndDate"/>
                <asp:DynamicField  DataField="DiscontinuedDate"/>
                <asp:DynamicField  DataField="ThumbnailPhotoFileName"/>
                <asp:DynamicField  DataField="rowguid"/>
                <asp:DynamicField  DataField="ModifiedDate"/>
            </Fields>
        </asp:DetailsView>
        <asp:LinqDataSource ID="ldsProducts" runat="server"
            ContextTypeName="AWDataContext" EnableUpdate="True" TableName="Products"
            Where="ProductID == @ProductID">
            <WhereParameters>
                <asp:Parameter DefaultValue="680" Name="ProductID" Type="Int32" />
            </WhereParameters>
        </asp:LinqDataSource>
    </div>
    </form>

Reply

sjnaughton

All-Star

27308 Points

5458 Posts

MVP

Re: foreign keys and dropdowns in DD custom page

Sep 24, 2009 01:04 PM|LINK

Following the normal Linq to SQL standards they would be:

<asp:DynamicField  DataField="ProductCategory"/>
<asp:DynamicField  DataField="ProductModel" />

You can find out the correct value by opening the designer.cs/vb file below the the dbml file. then search for the class and look at the members for the names (the properties ten to be more difficult to follow and are named the same as the members but witout the preceding '_')

See my blog C# Bits | Twitter @sjnaughton
Always seeking an elegant solution.

Reply

fgeorge

Member

29 Points

198 Posts

Re: foreign keys and dropdowns in DD custom page

Sep 24, 2009 06:00 PM|LINK

thanks steve but i'm realy not sure what i should be looking out for here..i have seen the class but i'm not clear on what value i'm looking out for..

i tried your above suggestion and i got an error;

the table 'products' does not have a column named 'productmodel'.

how come this works in the default DD pages but not in my custom pages?

strange!

Reply

sjnaughton

All-Star

27308 Points

5458 Posts

MVP

Re: foreign keys and dropdowns in DD custom page

Sep 24, 2009 07:06 PM|LINK

Hi fgeorge, I've e-mailed you [:D] just replay with what I've asked for and I'll post the replay back here [:)]

See my blog C# Bits | Twitter @sjnaughton
Always seeking an elegant solution.

Reply

fgeorge

Member

29 Points

198 Posts

Re: foreign keys and dropdowns in DD custom page

Sep 25, 2009 02:27 PM|LINK

thanks steve, i've done as requested..

Reply

sjnaughton

All-Star

27308 Points

5458 Posts

MVP

Re: foreign keys and dropdowns in DD custom page

Sep 25, 2009 03:32 PM|LINK

Hi Femi, have a look at the code from your AW.designer.cs:

these two lines (private member variable used in conjunction with the properties) show the navigation properties the first is an EntitySet a collection of SalesOrderDetails and the second is an EntityRef to ProductCategory these replace the Child relationships and FK columns in the displayed data in normal DD, but when you have to manually create a page then you have to find out what they are and manually reference them (this is all automatic in the normal DD pages) so your page should look like this:

[Table(Name="SalesLT.Product")]
public partial class Product : INotifyPropertyChanging, INotifyPropertyChanged
{
      private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
      private int _ProductID;
      private string _Name;
      private string _ProductNumber;
      private string _Color;
      private decimal _StandardCost;
      private decimal _ListPrice;
      private string _Size;
      private System.Nullable<decimal> _Weight;
      private System.Nullable<int> _ProductCategoryID;
      private System.Nullable<int> _ProductModelID;
      private System.DateTime _SellStartDate;
      private System.Nullable<System.DateTime> _SellEndDate;
      private System.Nullable<System.DateTime> _DiscontinuedDate;
      private System.Data.Linq.Binary _ThumbNailPhoto;
      private string _ThumbnailPhotoFileName;
      private System.Guid _rowguid;
      private System.DateTime _ModifiedDate;


      private EntitySet<SalesOrderDetail> _SalesOrderDetails;
      private EntityRef<ProductCategory> _ProductCategory;

these two lines (private member variable used in conjunction with the properties) show the navigation properties the first is an EntitySet a collection of SalesOrderDetails and the second is an EntityRef to ProductCategory these replace the Child relationships and FK columns in the displayed data in normal DD, but when you have to manually create a page then you have to find out what they are and manually reference them (this is all automatic in the normal DD pages) so your page should look like this:

<asp:DynamicField  DataField="ProductID" />
<asp:DynamicField  DataField="Name"/>
<asp:DynamicField  DataField="ProductNumber" />
<asp:DynamicField  DataField="Color" />
<asp:DynamicField  DataField="StandardCost"/>
<asp:DynamicField  DataField="ListPrice" />
<asp:DynamicField  DataField="Size" />
<asp:DynamicField  DataField="Weight"/>

<asp:DynamicField  DataField="ProductCategory"/>

<asp:DynamicField  DataField="ProductModelID" />

<asp:DynamicField  DataField="SellStartDate"/>
<asp:DynamicField  DataField="SellEndDate"/>
<asp:DynamicField  DataField="DiscontinuedDate"/>
<asp:DynamicField  DataField="ThumbnailPhotoFileName"/>
<asp:DynamicField  DataField="rowguid"/>
<asp:DynamicField  DataField="ModifiedDate"/>

<asp:DynamicField  DataField="SalesOrderDetails"/>

But this line <asp:DynamicField DataField="ProductModelID" /> here does not seem to have an EntityRef which leads me to believe that you don’t have that relationship in the DB or Model so for testing if you drop this line all should work.

Also note that this line <asp:DynamicField DataField="SalesOrderDetails"/> here will create a child ref (if you want it) to the SalesOrderDetails records for this entity.

See my blog C# Bits | Twitter @sjnaughton
Always seeking an elegant solution.

Marked as answer by fgeorge on Sep 25, 2009 04:03 PM

Reply

fgeorge

Member

29 Points

198 Posts

Re: foreign keys and dropdowns in DD custom page

Sep 25, 2009 06:49 PM|LINK

Thanks Steve,

Your answer is spot on.

I have implemented your response and it works perfectly. And you were also right about the ProductModel Table. It was missing from my Model. When i added it things workied perfectly.

Thank you for a speedy and clear answer!

foreign keys and dropdowns in DD custom page RSS

9 replies

fgeorge

foreign keys and dropdowns in DD custom page

nijhawan.sau...

Re: foreign keys and dropdowns in DD custom page

sjnaughton

Re: foreign keys and dropdowns in DD custom page

fgeorge

Re: foreign keys and dropdowns in DD custom page

sjnaughton

Re: foreign keys and dropdowns in DD custom page

fgeorge

Re: foreign keys and dropdowns in DD custom page

sjnaughton

Re: foreign keys and dropdowns in DD custom page

fgeorge

Re: foreign keys and dropdowns in DD custom page

sjnaughton

Re: foreign keys and dropdowns in DD custom page

fgeorge

Re: foreign keys and dropdowns in DD custom page

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft