+ Start a Discussion
Joanne ButterfieldJoanne Butterfield 

List has no rows for assignment to SObject (VF Page & controller extension)

Hi All,
I am trying to display opportunity products on the standard case page layout (The case is related to an opportunity using a look-up)
I am getting the error "Content cannot be displayed: List has no rows for assignment to SObject" I am not a developer and any help would be much appreciated in completing this. I'll owe you some beers at Dreamforce :)

Controller Extension
Public With Sharing Class crossObjectOpportunityInfo {
    Public Opportunity o                {get; private set;}
    Public Case              c              {get; private set;}

    Public List<Schema.FieldSetMember> getFields() {
        return SObjectType.OpportunityLineItem.FieldSets.Display_On_Case.getFields();
    }

    Public crossObjectOpportunityInfo(ApexPages.StandardController sc) {
        this.o = [SELECT Id, AccountId FROM Opportunity WHERE RELATED_OPPORTUNITY__C = :sc.getId() LIMIT 1];

        String queryString = 'SELECT Id';
        List<Schema.FieldSetMember> querySet = SObjectType.OpportunityLineItem.FieldSets.Display_On_Case.getFields();
        for(Schema.FieldSetMember f : querySet) {
            queryString += ', '+ f.getFieldPath();
        }

       
            Id cid = [SELECT Id FROM Contact WHERE AccountId = :o.AccountId LIMIT 1].Id;
            queryString += ' FROM Opportunity WHERE id = \''+ cid +'\' LIMIT 1';
    }

}

VF Page
<apex:page standardController="Case" extensions="crossObjectOpportunityInfo">
<apex:pageBlock title="Opportunity Products">
<apex:pageBlockSection columns="1">
<apex:pageBlockTable value="{!Case.Related_Opportunity__c}" var="line">
<apex:column headerValue="Product">

  </apex:column>
  </apex:pageBlockTable>
  </apex:pageBlockSection>
</apex:pageBlock>
</apex:page>
Best Answer chosen by Joanne Butterfield
kaustav goswamikaustav goswami
I have put together a sample code that should help you. If you have any questions please let me know.

You have mentioned that you want to display this page as related list. How are you planning to do so? Are you planning to include this page as an  inline visualforce page?

I would suggest the best way will be to give a button on the detail page and when the user clicks the button take the user to this page.

Apex Class
public class OppProductsOnCaseController{
    
    // declatre variables to hold the current case record
    public Case inContextCase {get; set;}
    // decalre variable to hold the list of oli fields for the related opportunity
    public List<OpportunityLineItem> oppLineItem {get; set;}
    
    // constructor for the class
    public OppProductsOnCaseController(ApexPages.StandardController stdCont){
        // instantiate the variables decalred
        inContextCase = new Case();
        oppLineItem = new List<OpportunityLineItem>();
        
        // get the in context record from the passed in argument in the constructor
        inContextCase = (Case) stdCont.getRecord();
        // query the necessary fields from the case object like the related opportunity field value
        inContextCase = [SELECT Id, Related_Opportunity__c FROM Case WHERE Id = :inContextCase.Id];
        
        // if the case is related to an opportunity then using the opportunity id query and fetch the opportunity line items
        if(inContextCase.Related_Opportunity__c != null){
            // add or delete fields from the query as required
            oppLineItem = [SELECT Id, ListPrice, OpportunityId, PriceBookEntryId, PriceBookEntry.Product2.Name, Quantity, UnitPrice, TotalPrice FROM OpportunityLineItem
                            WHERE OpportunityId = :inContextCase.Related_Opportunity__c];
            if(oppLineItem != null && oppLineItem.size() > 0){
                // do further operations with opp line items if required.
                // if not then delete the if condition
            }
        }
        
    }
}

VF Page
<apex:page standardController="Case" extensions="OppProductsOnCaseController" title="Related Opportunity Products">
  <apex:form id="oppProdForm">
      <apex:pageBlock id="prodPB" title="Opportunity Product Details">
          <apex:pageBlockTable id="pbTable" value="{!oppLineItem}" var="oli">
              <apex:column headerValue="Product Name" value="{!oli.PriceBookEntry.Product2.Name}" />
              <apex:column headerValue="Quantity" value="{!oli.Quantity}"/>              
          </apex:pageBlockTable>      
      </apex:pageBlock>
  </apex:form>
</apex:page>

Let me know if this helps.

Thanks,
Kaustav

All Answers

Phillip SouthernPhillip Southern
The problem you might be running into is for a case where related_opportunity__c is not populated.  You should have to code run only if that field has a value on the case.  Similar to your VF page, only show data if the field has a value, using the rendered variable for the pageblock.  something like rendered="{!NOT(ISNULL(Case.Related_Opportunity__c))}"
Joanne ButterfieldJoanne Butterfield
Thank you for your response Phillip, however all the cases have a related opportunity and have added the rendered variable and still receiving the error.
Phillip SouthernPhillip Southern
Oh wait, just seeing something.  So if related_opportunity__c is the field on the Case...thats what you'll need to filter by on your query.

this.c = (Case)sc.getRecord();
this.o = [SELECT Id, AccountId FROM Opportunity WHERE Id = :c.related_opportunity__c LIMIT 1];
Joanne ButterfieldJoanne Butterfield
Thank you Phillip, I have added that to the VF page also and still getting the same error.
kaustav goswamikaustav goswami
One thing I noticed in your code is that you have queried from the opportunity object based on a contact id.

The variable "cId" contains the Id of a contact. If you query based on this Id from the Opportunity object the result set will always be null.

Thanks,
Kaustav
Joanne ButterfieldJoanne Butterfield
Thank you Kaustav, Do the last two lines need to be replaced or should I just be able to delet? I deleted them and I'm still getting the same eror.
Thank you!
Phillip SouthernPhillip Southern
Joanne, noticed you said you added to the VF page......the code I posted was actually meant for the apex class and the constructor.
kaustav goswamikaustav goswami
You will have figure out what the actual query should be. Deleting the lines will not solve the problem.

I would suggest you post your requirement and you entire apex class and VF page.

We can then provide you with a working piece.

Ideally if you are trying to display opportunities then you should iterate over a list of opportunities.

Thanks,
Kaustav
Joanne ButterfieldJoanne Butterfield
Thank you Kaustav,
The requirement is to be able to view opportunity product line items related list on a standard case page layout, the case is related to an opportunity via a look-up field (RELATED_OPPORTUNITY__C).

This is the entire apex class and VF page I have completed, as mentioned I am not a developer and haven’t done any coding before.
Any help will be greatly appreciated.
Thank you!

Visualforce Page
<apex:page standardController="Case" extensions="crossObjectOpportunityInfo">
rendered="{!NOT(ISNULL(Case.Related_Opportunity__c))}"
<apex:pageBlock title="Opportunity Products">
<apex:pageBlockSection columns="1">
<apex:pageBlockTable value="{!Case.Related_Opportunity__c}" var="line">
<apex:column headerValue="Product">

  </apex:column>
  </apex:pageBlockTable>
  </apex:pageBlockSection>
</apex:pageBlock>
</apex:page>

Apex Class
Public With Sharing Class crossObjectOpportunityInfo {
    Public Opportunity o                {get; private set;}
    Public Case              c              {get; private set;}

    Public List<Schema.FieldSetMember> getFields() {
        return SObjectType.OpportunityLineItem.FieldSets.Display_On_Case.getFields();
    }

    Public crossObjectOpportunityInfo(ApexPages.StandardController sc) {
        this.c = (Case)sc.getRecord();
        this.o = [SELECT Id, AccountId FROM Opportunity WHERE RELATED_OPPORTUNITY__C = :sc.getId() LIMIT 1];
       


        String queryString = 'SELECT Id';
        List<Schema.FieldSetMember> querySet = SObjectType.OpportunityLineItem.FieldSets.Display_On_Case.getFields();
        for(Schema.FieldSetMember f : querySet) {
            queryString += ', '+ f.getFieldPath();
        }
    }
}
kaustav goswamikaustav goswami
I have put together a sample code that should help you. If you have any questions please let me know.

You have mentioned that you want to display this page as related list. How are you planning to do so? Are you planning to include this page as an  inline visualforce page?

I would suggest the best way will be to give a button on the detail page and when the user clicks the button take the user to this page.

Apex Class
public class OppProductsOnCaseController{
    
    // declatre variables to hold the current case record
    public Case inContextCase {get; set;}
    // decalre variable to hold the list of oli fields for the related opportunity
    public List<OpportunityLineItem> oppLineItem {get; set;}
    
    // constructor for the class
    public OppProductsOnCaseController(ApexPages.StandardController stdCont){
        // instantiate the variables decalred
        inContextCase = new Case();
        oppLineItem = new List<OpportunityLineItem>();
        
        // get the in context record from the passed in argument in the constructor
        inContextCase = (Case) stdCont.getRecord();
        // query the necessary fields from the case object like the related opportunity field value
        inContextCase = [SELECT Id, Related_Opportunity__c FROM Case WHERE Id = :inContextCase.Id];
        
        // if the case is related to an opportunity then using the opportunity id query and fetch the opportunity line items
        if(inContextCase.Related_Opportunity__c != null){
            // add or delete fields from the query as required
            oppLineItem = [SELECT Id, ListPrice, OpportunityId, PriceBookEntryId, PriceBookEntry.Product2.Name, Quantity, UnitPrice, TotalPrice FROM OpportunityLineItem
                            WHERE OpportunityId = :inContextCase.Related_Opportunity__c];
            if(oppLineItem != null && oppLineItem.size() > 0){
                // do further operations with opp line items if required.
                // if not then delete the if condition
            }
        }
        
    }
}

VF Page
<apex:page standardController="Case" extensions="OppProductsOnCaseController" title="Related Opportunity Products">
  <apex:form id="oppProdForm">
      <apex:pageBlock id="prodPB" title="Opportunity Product Details">
          <apex:pageBlockTable id="pbTable" value="{!oppLineItem}" var="oli">
              <apex:column headerValue="Product Name" value="{!oli.PriceBookEntry.Product2.Name}" />
              <apex:column headerValue="Quantity" value="{!oli.Quantity}"/>              
          </apex:pageBlockTable>      
      </apex:pageBlock>
  </apex:form>
</apex:page>

Let me know if this helps.

Thanks,
Kaustav
This was selected as the best answer
Joanne ButterfieldJoanne Butterfield
Kaustav, Thank you so much for your help, and all the explanations in the code.
This is great, Thank you!
Joanne ButterfieldJoanne Butterfield
Hi Kaustav,
For the test class I thought I would just have to create a new case, however the code coverage is still at 0%.
This is the test class I created, as always any help would be appreciated.

@IsTest
Public Class TestOppProductsOnCaseController {

    Static testmethod void insertCase () {
   
    Case c = new Case();
   
    c.AccountId = '001J000001RmXg8';
    c.ContactId = '003J0000010M0vN';
    c.Origin = 'Sales';
    c.OwnerId =  '005U00000011NVp';
    c.RecordTypeId = '012U0000000URMk';
    c.Status = 'Unassigned';
    c.Type = 'Installation';
    c.Related_Opportunity__c = '006J000000GTZTR' ;
   
    insert c;
   
    }
   
    }
kaustav goswamikaustav goswami
Hi Joanne, Just creating an instance will not cover the class. You will have to create appropriate data in the test method and then instantiate the controller. In the test method you will have to carry out the following steps: // create an account and insert it // create an opportunity associate it with the account and insert the opportunity // create a product and insert it // associate it with the standard price book // create a price book entry // create an opportunity line item // create a case record and associate it with the opportunity created above // create an instance of the standard controller ApexPages.StandardController std = new ApexPages.StandardController(pass the instance of the case record); // craete an instance of your actual class and pass the standard controller instance DisplayOppProductsController prodDisp = new DisplayOppProductsController(std); Once you complete the above steps the code should be covered. If you need more help let me know. Thanks, Kaustav
Joanne ButterfieldJoanne Butterfield
Hi Kaustav,
Thank you again, I can’t seem to figure out how to write the test script, I keep getting errors.
How to I associate everything?
Thank you