Peter
Peter

Reputation: 1894

Display data from one to many relation using ASP.NET Web Forms data controls

I have a relation consisting of ProjectsOverall to Projects. ProjectOverall contains many Projects and a Project is assigned exactly to one ProjectOverall.
One having the detail view a ProjectOverall I want to display all it's Projects. This leaves 2 options in my WebControlling.xsd:

  1. Configure the ProjectsOverall adapter to find all Projects that references it's ID.
  2. Configure the Project adapter to find all entries with a given ProjectOverallID.

I tried both and stumbled upon one of these problems:
Option 1, Conigure ProjectOverall
When returning the rows the data contains multiple instances of the primary key ID of ProjectOverall. This results in an error:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

References: How to resolve common errors, Force DataSet to not enforce constraints
Both options didn't work.

[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public WebControlling.ProjectsDataTable GetAllProjectsForID(Guid ProjectOverallID)
{
    WebControlling.ProjectsDataTable projects = new WebControlling.ProjectsDataTable();
    try
    {
        projects.Merge(Adapter.GetAllProjectsForID(ProjectOverallID));
    }
    catch (Exception e)
    {
        throw e;
    }
    return projects;
}

The sql (Total overkill, but works when only one entry is returned):

SELECT   wc_Countries.Name AS CountryName, wc_OrganisationUnit.Name AS OEName, wc_Projects.ID AS ProjectID, wc_ProjectsOverall.ID AS ProjectOverallID, wc_Projects.Name AS ProjectName, 
                         wc_ProjectsOverall.ProjectName AS ProjectOverallName, wc_ProjectsOverall.*, wc_Countries.*, wc_OrganisationUnit.*, wc_Projects.*
FROM         wc_ProjectsOverall INNER JOIN
                         wc_Countries ON wc_ProjectsOverall.CountryID = wc_Countries.ID INNER JOIN
                         wc_OrganisationUnit ON wc_ProjectsOverall.OrganisationUnitID = wc_OrganisationUnit.ID INNER JOIN
                         wc_Projects ON wc_ProjectsOverall.ID = wc_Projects.ProjectOverallID AND wc_Countries.ID = wc_Projects.CountryID AND 
                         wc_OrganisationUnit.ID = wc_Projects.OrganisationUnitID
WHERE     (wc_ProjectsOverall.ID = @ProjectOverallID)

In the detail view:

<%-- Sub Project Data Source --%>
<asp:ObjectDataSource ID="ObjectDataSourceProjects" runat="server" 
                          OldValuesParameterFormatString="{0}" 
                          SelectMethod="GetAllProjectsForID" 
                          TypeName="ProjectsOverall" 
                          DataObjectTypeName="System.Guid">
    <SelectParameters>
        <asp:QueryStringParameter QueryStringField="ID" Name="ProjectOverallID" Type="Object" /> 
    </SelectParameters>
</asp:ObjectDataSource>

The second option does not work, because when I switch the type of the DataSource to TypeName="Projects I don't know how to access the ID present in the view.
The view displays all properties of a ProjectOverall and should display all itsProjects`.

<ContentTemplate>                   
                    <%-- Displays all Sub-Projects  --%>
                    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                        <ContentTemplate>
                            <asp:GridView ID="GridViewProjects" runat="server" AllowPaging="True" AllowSorting="True"
                                AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="ObjectDataSourceProjects" PageSize="5">
                                <Columns>
                                    No need to show all    
                                </Columns>
                                <EmptyDataTemplate>
                                    No project matched your filter criteria. <br />
                                    <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="<%# Request.RawUrl %>">Return to the project overview.</asp:HyperLink>
                                </EmptyDataTemplate>
                            </asp:GridView>
                        </ContentTemplate>
                    </asp:UpdatePanel>
                </div>
            </ContentTemplate>

Is there a clean solution to do either of those things?

Upvotes: 0

Views: 128

Answers (0)

Related Questions