0

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.

  • 1
  • 4
    I've voted to close this question as the general error has been covered previously in other questions. As a general rule, don't assign the results of a SOQL query to a singular sObject. If the query doesn't return any rows you will get the "List has no rows for assignment to SObject" exception. Instead, assign the results to a list of sObjects and check the size of the list. Then only use the first record in the list if it is present. – Daniel Ballinger Mar 2 '15 at 18:33
  • Hi all, that link doesn't cover components - I vote that it's a different problem. I've searched "VF Component" and "No Rows" and this is the only question. – user2223 Mar 2 '15 at 18:37
  • 1
    I don't believe the exception differs because it is a component. Can you indicate which line in your example code is throwing the exception? Maybe you would like to rework your question to emphasis that that the issue is getting a default record in preview mode rather than the resulting exception. – Daniel Ballinger Mar 2 '15 at 18:47
  • 2
    The problem is doing this in Apex: Opportunity lo = [SELECT Id, Name FROM Opportunity WHERE Id = :strOptID LIMIT 1];. You need to assign the SOQL results to a List<Opportunity>, check the resulting List size and then get lo from the first record in the list. If the list is empty you need to return. – Daniel Ballinger Mar 2 '15 at 18:56
1

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.

| improve this answer | |
0

I would suggest that you refactor your queryDetailData method to query into a list instead of a single record. Then, if the resulting size of the List is 0, then set a boolean rendered flag to false, thus hiding the component, and preventing the error from occurring when you are in preview mode.

| improve this answer | |
  • Thanks JimRae. Great idea about the rendered flag, but since it's an email that's related to one record, (Limit 1), can't I still just check for the return opportunity being null after the SOQL? Thanks again. – user2223 Mar 2 '15 at 21:39
  • You could, but you may still have the no results error. The other way to handle this would be to put the SOQL query in a try/catch block, then catch the Query Exception and as long as it is "no rows" related, you could set the no render flag, or handle the optional processing another way of your choosing. – JimRae Mar 2 '15 at 22:19
0

tempOppty is a single sObject. May be the soql is not assigning any record to this. You would want to convert this into a list and proceed accordingly.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.