Skip to main content
Solved

'Single row was requested' error occured

  • 10 September 2024
  • 1 reply
  • 54 views

Forum|alt.badge.img

I am using PXResult in foreach and it gives single row was requested error occured 

 

 

Best answer by chameera71

The error you're encountering, "Single row was requested but the resultset returned multiple rows", typically occurs when you're trying to retrieve a single row from the database, but the query returns multiple rows, causing an issue with how the result is handled.

In your case, you are using PXSelect<> to fetch multiple rows, but you are attempting to loop through the results using a foreach statement. However, it appears that there may be a mismatch between how the result set is being handled and how many rows are actually being returned by your query.

Fix for the Error:

  1. Use PXResultset<> in the foreach Loop: When using PXSelect<> to fetch multiple rows, you need to loop through the result set using PXResultset<>, which is already defined in your variable materials.

    But you are directly trying to use the type EWPMBOMMat1 in the foreach loop, which causes the error. Instead, the foreach should loop over the PXResult<> type.

    Here's how you can update your code:

    Updated Code:

     

    csharp

     

    private decimal CalculateFormulaMaterialCost() { PXResultset<EWPMBOMMat1> materials; // condition applied on 04-05-2023 #98977 //added && for full FG BOM 08-05-2023 if (EWPMBOMUtils.IsFullFGBOM(Bom)) { materials = PXSelect<EWPMBOMMat1, Where<EWPMBOMMat1.bomID, Equal<Required<EWPMBOMMat1.bomID>>, And<EWPMBOMMat1.revisionNo, Equal<Required<EWPMBOMMat1.revisionNo>>, And<EWPMBOMMat1.bomCD, Equal<Required<EWPMBOMMat1.bomCD>>, And<Where<EWPMBOMMat1.sourceType, Equal<EWPMItemSourceType.IntermediateSourceType>>>>>>> .Select(PXGraph, Bom.BomID, Bom.RevisionNo, Bom.BomCD); } else { materials = PXSelect<EWPMBOMMat1, Where<EWPMBOMMat1.bomID, Equal<Required<EWPMBOMMat1.bomID>>, And<EWPMBOMMat1.revisionNo, Equal<Required<EWPMBOMMat1.revisionNo>>, And<EWPMBOMMat1.bomCD, Equal<Required<EWPMBOMMat1.bomCD>>, And<Where<EWPMBOMMat1.sourceType, Equal<EWPMItemSourceType.FormulaSourceType>, Or<EWPMBOMMat1.sourceType, Equal<EWPMItemSourceType.RoutingSourceType>>>>>>> .Select(PXGraph, Bom.BomID, Bom.RevisionNo, Bom.BomCD); } decimal totalMaterialCost = 0; // Use PXResultset here foreach (PXResult<EWPMBOMMat1> result in materials) { EWPMBOMMat1 material = result; material.PlanCost = material.UnitCost * material.BaseQtyReq; totalMaterialCost += material.PlanCost ?? 0; } return totalMaterialCost; }

Explanation:

  • PXResultset<> vs PXResult<>:

    • PXResultset<> is used for handling a set of rows returned by the PXSelect<> query.
    • PXResult<> is used when iterating over multiple rows in the PXResultset<>.
  • foreach loop: You need to iterate over PXResult<EWPMBOMMat1> instead of EWPMBOMMat1. This ensures that the PXResultset properly handles multiple rows without generating the error.

Additional Considerations:

  • Error Handling: Ensure that your materials variable always contains rows before the foreach loop is executed. You can add a check like:
     

    csharp

     

    if (materials == null || materials.Count == 0) { return 0; // or handle empty material set }

    This prevents unnecessary loops when no data is returned.

With this approach, you should be able to eliminate the "Single row requested" error and successfully iterate through the material records for cost calculation.

 

😀💡

View original
Did this topic help you find an answer to your question?

1 reply

chameera71
Varsity I
Forum|alt.badge.img+2
  • Varsity I
  • 53 replies
  • Answer
  • September 12, 2024

The error you're encountering, "Single row was requested but the resultset returned multiple rows", typically occurs when you're trying to retrieve a single row from the database, but the query returns multiple rows, causing an issue with how the result is handled.

In your case, you are using PXSelect<> to fetch multiple rows, but you are attempting to loop through the results using a foreach statement. However, it appears that there may be a mismatch between how the result set is being handled and how many rows are actually being returned by your query.

Fix for the Error:

  1. Use PXResultset<> in the foreach Loop: When using PXSelect<> to fetch multiple rows, you need to loop through the result set using PXResultset<>, which is already defined in your variable materials.

    But you are directly trying to use the type EWPMBOMMat1 in the foreach loop, which causes the error. Instead, the foreach should loop over the PXResult<> type.

    Here's how you can update your code:

    Updated Code:

     

    csharp

     

    private decimal CalculateFormulaMaterialCost() { PXResultset<EWPMBOMMat1> materials; // condition applied on 04-05-2023 #98977 //added && for full FG BOM 08-05-2023 if (EWPMBOMUtils.IsFullFGBOM(Bom)) { materials = PXSelect<EWPMBOMMat1, Where<EWPMBOMMat1.bomID, Equal<Required<EWPMBOMMat1.bomID>>, And<EWPMBOMMat1.revisionNo, Equal<Required<EWPMBOMMat1.revisionNo>>, And<EWPMBOMMat1.bomCD, Equal<Required<EWPMBOMMat1.bomCD>>, And<Where<EWPMBOMMat1.sourceType, Equal<EWPMItemSourceType.IntermediateSourceType>>>>>>> .Select(PXGraph, Bom.BomID, Bom.RevisionNo, Bom.BomCD); } else { materials = PXSelect<EWPMBOMMat1, Where<EWPMBOMMat1.bomID, Equal<Required<EWPMBOMMat1.bomID>>, And<EWPMBOMMat1.revisionNo, Equal<Required<EWPMBOMMat1.revisionNo>>, And<EWPMBOMMat1.bomCD, Equal<Required<EWPMBOMMat1.bomCD>>, And<Where<EWPMBOMMat1.sourceType, Equal<EWPMItemSourceType.FormulaSourceType>, Or<EWPMBOMMat1.sourceType, Equal<EWPMItemSourceType.RoutingSourceType>>>>>>> .Select(PXGraph, Bom.BomID, Bom.RevisionNo, Bom.BomCD); } decimal totalMaterialCost = 0; // Use PXResultset here foreach (PXResult<EWPMBOMMat1> result in materials) { EWPMBOMMat1 material = result; material.PlanCost = material.UnitCost * material.BaseQtyReq; totalMaterialCost += material.PlanCost ?? 0; } return totalMaterialCost; }

Explanation:

  • PXResultset<> vs PXResult<>:

    • PXResultset<> is used for handling a set of rows returned by the PXSelect<> query.
    • PXResult<> is used when iterating over multiple rows in the PXResultset<>.
  • foreach loop: You need to iterate over PXResult<EWPMBOMMat1> instead of EWPMBOMMat1. This ensures that the PXResultset properly handles multiple rows without generating the error.

Additional Considerations:

  • Error Handling: Ensure that your materials variable always contains rows before the foreach loop is executed. You can add a check like:
     

    csharp

     

    if (materials == null || materials.Count == 0) { return 0; // or handle empty material set }

    This prevents unnecessary loops when no data is returned.

With this approach, you should be able to eliminate the "Single row requested" error and successfully iterate through the material records for cost calculation.

 

😀💡


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings