Reputation: 1894
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
:
ProjectsOverall
adapter to find all Projects
that references it's ID.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 its
Projects`.
<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