0

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?

Peter
  • 1,844
  • 2
  • 31
  • 55
  • Your exception handling needs some serious reconsideration. First of all, don't do `throw e;` where `e` is a caught exception. You will lose your stack trace, and make it harder to debug your issue. Then, why do you even have a try/catch if the only thing you do is rethrow the exception? If you're not going to take any action in the `catch` block besides rethrow the caught exception, then you shouldn't use a try/catch in the first place. – mason Sep 18 '19 at 16:45
  • I only caught it to be able to set a break point – Peter Sep 18 '19 at 19:16
  • Why? The debugger should break when the application has an exception anyways. I wouldn't risk polluting the code with that. – mason Sep 18 '19 at 19:28
  • First, remove `OldValuesParameterFormatString="{0}" `; it always screws things up. I forget the exact reason now but delete it. It might be needed for transactions or something like that. – wazz Sep 21 '19 at 10:20
  • Start a blank page and use simple queries to get things started. Create a dropdown list of ProjectsOverall; use a bare-bones query of ID and Name. Create another query of related ProjectsByProjectsOverallID. (if it's at all possible, rename 'ProjectsOverall'; it's terrible.) Put the related projects into a gridview. Just do that to make sure everything is working. Make the gridview update when the dropdown list updates. It looks like there might be some data issues but hopefully not. – wazz Sep 21 '19 at 10:25

0 Answers0