[SalesForce] I’m getting “List has no rows for assignment to SObject” error on a Visualforce component

EDIT:
Is there a recommended way for a VF email template that uses a VF component to display in the SFDC UI such that you don't get the error message "Error occurred trying to load the template for preview: List has no rows for assignment to SObject. Please try editing your markup to correct the problem"

when saving the template? As the original problem description states below, I get this message when saving the VF template. The only workaround (see component controller code) that seems to work for me is to read a "test" opportunity such that the VF template has something to display, but this seems to be a complete hack – and I'm not guaranteed to have a "test" opportunity in any environment.

ORIGINAL:
I'm trying to use a component in a VF email template that queries opportunity line items from an opportunity lookup in a custom object. I get the error "List has no rows for assignment to SObject" if I don't query at least one opportunity object, which is the basis of my question.

The gist of the VF template is this;

<messaging:emailTemplate subject="Deal Desk Template Details: {!RelatedTo.Name}" recipientType="User" relatedToType="Deal_Details__c">
  <messaging:htmlEmailBody >
    <html>
      <font face="verdana" size="1">
        [...]
        <c:findDetailData xstrOpptyId="{!relatedTo.OpportinityId__c}"/>
        [...]
      </font>
    </html>
  </messaging:htmlEmailBody>
</messaging:emailTemplate>

The Component looks like this;

<apex:component controller="findDetailData" access="global">
  <apex:attribute name="xstrOpptyId" description="opportunity ID" type="String" assignTo="{!strOptID}"/>
  <table>
  <tr>
   <td>Name</td>
   <td>Quantity</td>
   <td>UoM</td>
   <td>NRC</td>
   <td>Price</td>
   <td>Variance</td>
   <td>MRC</td>
   <td>Waived</td>
  </tr>
  <apex:repeat var="opp" value="{!opptyLines}">
     <td>{!opp.PriceBookEntry.name}</td>
     <td align="right">{!ROUND(opp.Quantity,0)}</td>
     <td align="left">{!opp.UoM__c}</td>
     <td align="right">{!ROUND(opp.NRC__c,0)}</td>
     <td align="right">{!ROUND(opp.Variance__c,0)}</td>
     <td align="right">{!ROUND(opp.TotalPrice,0)}</td>
     <td align="left">{!opp.Waived__c}</td>
  </apex:repeat>
  </table>  
</apex:component>

And the controller is this;

public class findDetailData {
public  List<OpportunityLineItem> opptyLines = new List<OpportunityLineItem>();
public String strOptID{get;set;}
public OpportunityLineItem ox = new OpportunityLineItem(Quantity=1,UoM__c='', NRC__c=0,TotalPrice=0,Waived__c=false);

public findDetailData () {
    System.debug('Constructing findDetailData : ' + strOptID);
    }

public void queryDetailData() {
    <b>if(strOptID == null) {       // <=====  ADDED FOR PREVIEW MODE
        Opportunity tempOppty = [SELECT Id FROM Opportunity  WHERE Name LIKE '%Test%' LIMIT 1];
        strOptID = tempOppty.Id;
        }</b>

    Opportunity lo;    
    lo = [SELECT Amount, Id, Name, (SELECT Quantity, NRC__c,
    PriceBookEntry.UnitPrice, PricebookEntry.Name, UoM__c,
    Variance__c, TotalPrice, Waived__c FROM OpportunityLineItems)
    FROM Opportunity  WHERE Id =  :strOptID LIMIT 1];

    System.debug('opptyLines.size(): ' + opptyLines.size());        

    For(OpportunityLineItem oli :lo.OpportunityLineItems) {
       opptyLines.add(oli);
       }
    }
public void putstrOptID(String xs)   {
    strOptID = xs;
    System.debug('opptyLines. ID: ' + strOptID);
    }

public List<OpportunityLineItem> getopptyLines() {
    System.debug('opptyLines(1) ID: ' + strOptID);
    queryDetailData();
    return opptyLines;
    }
}

But what I don't like is I'm burning an extra SOQL statement just for preview of the VF page. If strOptID is null (during preview) I have to query an "artificial" opportunity that "should" be there ("%Test%")…..

I don't like this solution and I'm sure there's a better way, and I was hoping someone might point me to a more appropriate way to accomplish this.

Thanks in advance.

Best Answer

General rule - don't return result of SOQL to an object, but return it to a list of objects. After querying items, check the list.
Don't do

Account account01 = [SELECT id FROM Account LIMIT 1];

but do

List<Account> accounts01 = [SELECT id FROM Account LIMIT 1];
if (accounts01.size() > 0) {
 Account account01 = accounts01[0];
 // do your business
}


In your case Opportunity. But you have two places where this error may appear.

Related Topic