Visualforce Error:
System.QueryException: List has no rows for assignment to SObject Class.opportunityProductEntryExtension.: line 30, column 1
theOpp = [
SELECT Id, Pricebook2Id, PriceBook2.Name FROM Opportunity
WHERE Id = :controller.getRecord().Id LIMIT 1
];
I understand this is returning no record from the object and it should be in a list but I'm having trouble getting that to work. This only happens when I add 8 or more products to the opportunity. Anything less and it works perfectly. Thanks in advance.
Here is the full code:
public with sharing class opportunityProductEntryExtension {
public Opportunity theOpp {get;set;}
public String searchString {get;set;}
public opportunityLineItem[] shoppingCart {get;set;}
public priceBookEntry[] AvailableProducts {get;set;}
public Pricebook2 theBook {get;set;}
public String toSelect {get; set;}
public String toUnselect {get; set;}
public Decimal Total {get;set;}
public Boolean overLimit {get;set;}
public Boolean multipleCurrencies {get; set;}
private Boolean forcePricebookSelection = false;
private opportunityLineItem[] forDeletion = new opportunityLineItem[]{};
public opportunityProductEntryExtension(ApexPages.StandardController controller) {
// Need to know if org has multiple currencies enabled
multipleCurrencies = UserInfo.isMultiCurrencyOrganization();
// Get information about the Opportunity being worked on
if(multipleCurrencies)
theOpp = database.query('select Id, Pricebook2Id, Pricebook2.Name, CurrencyIsoCode from Opportunity where Id = \'' + controller.getRecord().Id + '\' limit 1');
else
//theOpp = [select Id, Pricebook2Id, PriceBook2.Name from Opportunity where Id = :controller.getRecord().Id limit 1];
theOpp = [select Id, Pricebook2Id, PriceBook2.Name from Opportunity where Id = :controller.getId() limit 1];
// If products were previously selected need to put them in the "selected products" section to start with
shoppingCart = [select Id, Quantity, Contract_Discount2__c, TotalPrice, UnitPrice, Description, Discount__c, Net_Price__c, PriceBookEntryId, PriceBookEntry.Name, PriceBookEntry.IsActive, PriceBookEntry.Product2Id, PriceBookEntry.Product2.Name, PriceBookEntry.Product2.ProductCode, PriceBookEntry.PriceBook2Id from opportunityLineItem where OpportunityId=:theOpp.Id];
// Check if Opp has a pricebook associated yet
if(theOpp.Pricebook2Id == null){
Pricebook2[] activepbs = [select Id, Name from Pricebook2 where isActive = true limit 2];
if(activepbs.size() == 2){
forcePricebookSelection = true;
theBook = new Pricebook2();
}
else{
theBook = activepbs[0];
}
}
else{
theBook = theOpp.Pricebook2;
}
if(!forcePricebookSelection)
updateAvailableList();
}
// this is the 'action' method on the page
public PageReference priceBookCheck(){
// if the user needs to select a pricebook before we proceed we send them to standard pricebook selection screen
if(forcePricebookSelection){
return changePricebook();
}
else{
//if there is only one active pricebook we go with it and save the opp
if(theOpp.pricebook2Id != theBook.Id){
try{
theOpp.Pricebook2Id = theBook.Id;
update(theOpp);
}
catch(Exception e){
ApexPages.addMessages(e);
}
}
return null;
}
}
public String getChosenCurrency(){
if(multipleCurrencies)
return (String)theOpp.get('CurrencyIsoCode');
else
return '';
}
public void updateAvailableList() {
// We dynamically build a query string and exclude items already in the shopping cart
String qString = 'select Id, Pricebook2Id, IsActive, Product2.Name, Product2.Family, Product2.IsActive, Product2.Description, Product2.ProductCode, UnitPrice from PricebookEntry where IsActive=true and Pricebook2Id = \'' + theBook.Id + '\'';
if(multipleCurrencies)
qstring += ' and CurrencyIsoCode = \'' + theOpp.get('currencyIsoCode') + '\'';
// note that we are looking for the search string entered by the user in the name OR description or ProductCode
// modify this to search other fields if desired
if(searchString!=null){
qString+= ' and (Product2.Name like \'%' + searchString + '%\' or Product2.Description like \'%' + searchString + '%\' or Product2.ProductCode like \'%' + searchString + '%\')';
}
Set<Id> selectedEntries = new Set<Id>();
for(opportunityLineItem d:shoppingCart){
selectedEntries.add(d.PricebookEntryId);
}
if(selectedEntries.size()>0){
String tempFilter = ' and Id not in (';
for(Id i : selectedEntries){
tempFilter+= '\'' + (String)i + '\',';
}
String extraFilter = tempFilter.substring(0,tempFilter.length()-1);
extraFilter+= ')';
qString+= extraFilter;
}
qString+= ' order by Product2.Name';
qString+= ' limit 101';
system.debug('qString:' +qString);
AvailableProducts = database.query(qString);
// We only display up to 100 results... if there are more than we let the user know (see vf page)
if(AvailableProducts.size()==101){
AvailableProducts.remove(100);
overLimit = true;
}
else{
overLimit=false;
}
}
public void addToShoppingCart(){
// This function runs when a user hits "select" button next to a product
for(PricebookEntry d : AvailableProducts){
if((String)d.Id==toSelect){
shoppingCart.add(new opportunityLineItem(OpportunityId=theOpp.Id, PriceBookEntry=d, PriceBookEntryId=d.Id, UnitPrice=d.UnitPrice));
break;
}
}
updateAvailableList();
}
public PageReference removeFromShoppingCart(){
// This function runs when a user hits "remove" on an item in the "Selected Products" section
Integer count = 0;
for(opportunityLineItem d : shoppingCart){
if((String)d.PriceBookEntryId==toUnselect){
if(d.Id!=null)
forDeletion.add(d);
shoppingCart.remove(count);
break;
}
count++;
}
updateAvailableList();
return null;
}
public PageReference onSave(){
// If previously selected products are now removed, we need to delete them
if(forDeletion.size()>0)
delete(forDeletion);
// Previously selected products may have new quantities and amounts, and we may have new products listed, so we use upsert here
try{
if(shoppingCart.size()>0) {
upsert(shoppingCart);
}
}
catch(Exception e){
ApexPages.addMessages(e);
return null;
}
//PageReference pr = new PageReference('/apex/opportunityProductEntry?id=' + theOpp.Id);
PageReference pr = new PageReference(ApexPages.currentPage().getUrl());
pr.setRedirect(true);
return pr;
// After save return the user to the Opportunity
// return new PageReference('/' + ApexPages.currentPage().getParameters().get('Id'));
}
public PageReference onCancel(){
// If user hits cancel we commit no changes and return them to the Opportunity
return new PageReference('/' + ApexPages.currentPage().getParameters().get('Id'));
}
public PageReference changePricebook(){
// This simply returns a PageReference to the standard Pricebook selection screen
// Note that is uses retURL parameter to make sure the user is sent back after they choose
PageReference ref = new PageReference('/oppitm/choosepricebook.jsp');
ref.getParameters().put('id',theOpp.Id);
ref.getParameters().put('retURL','/apex/opportunityProductEntry?id=' + theOpp.Id);
return ref;
}
}
And...
<apex:sectionHeader Title="Manage {!$ObjectType.Product2.LabelPlural}" subtitle="{!opportunity.Name}"/>
<apex:messages style="color:red"/>
<style>
.search{
font-size:14pt;
margin-right: 20px;
}
.fyi{
color:red;
font-style:italic;
}
.label{
margin-right:10px;
font-weight:bold;
}
</style>
<script type='text/javascript'>
// This script assists the search bar functionality
// It will execute a search only after the user has stopped typing for more than 1 second
// To raise the time between when the user stops typing and the search, edit the following variable:
var waitTime = 1;
var countDown = waitTime+1;
var started = false;
function resetTimer(){
countDown=waitTime+1;
if(started==false){
started=true;
runCountDown();
}
}
function runCountDown(){
countDown--;
if(countDown<=0){
fetchResults();
started=false;
}
else{
window.setTimeout(runCountDown,1000);
}
}
</script>
<apex:form >
<apex:outputPanel id="mainBody">
<apex:outputLabel styleClass="label">PriceBook: </apex:outputLabel>
<apex:outputText value="{!theBook.Name}"/>
<apex:commandLink action="{!changePricebook}" value="change" immediate="true"/>
<br/>
<!-- not everyone is using multi-currency, so this section may or may not show -->
<apex:outputPanel rendered="{!multipleCurrencies}">
<apex:outputLabel styleClass="label">Currency: </apex:outputLabel>
<apex:outputText value="{!chosenCurrency}"/>
<br/>
</apex:outputPanel>
<br/>
<!-- notice we use a lot of $ObjectType merge fields... I did that because if you have changed the labels of fields or objects it will reflect your own lingo -->
<apex:pageBlock title="Selected {!$ObjectType.Product2.LabelPlural}" id="selected">
<apex:pageblockTable value="{!shoppingCart}" var="s">
<apex:column >
<apex:commandLink value="Remove" action="{!removeFromShoppingCart}" reRender="selected,searchResults" immediate="true">
<!-- this param is how we send an argument to the controller, so it knows which row we clicked 'remove' on -->
<apex:param value="{!s.PriceBookEntryId}" assignTo="{!toUnselect}" name="toUnselect"/>
</apex:commandLink>
</apex:column>
<apex:column headerValue="{!$ObjectType.Product2.LabelPlural}" value="{!s.PriceBookEntry.Product2.ProductCode}"/>
<apex:column headerValue="{!$ObjectType.Product2.LabelPlural}" value="{!s.PriceBookEntry.Product2.Name}"/>
<apex:column headerValue="{!$ObjectType.OpportunityLineItem.Fields.Quantity.Label}">
<apex:inputField value="{!s.Quantity}" style="width:70px" required="true" onkeyup="refreshTotals();"/>
</apex:column>
<apex:column headerValue="{!$ObjectType.OpportunityLineItem.Fields.UnitPrice.Label}">
<apex:inputField value="{!s.UnitPrice}" style="width:70px" required="true" onkeyup="refreshTotals();"/>
</apex:column>
<!-- Added Contract Discount -->
<apex:column headerValue="{!$ObjectType.OpportunityLineItem.Fields.Contract_Discount2__c.Label}" style="width:125px;" value="{!s.Contract_Discount2__c}" />
<apex:column headerValue="{!$ObjectType.OpportunityLineItem.Fields.Discount__c.Label}">
<apex:inputField value="{!s.Discount__c}" style="width:70px" required="true" onkeyup="refreshTotals();"/>
</apex:column>
<apex:column headerValue="{!$ObjectType.OpportunityLineItem.Fields.Net_Price__c.Label}">
<apex:inputField value="{!s.Net_Price__c}" style="width:70px" required="true"/>
</apex:column>
<apex:column headerValue="{!$ObjectType.OpportunityLineItem.Fields.Description.Label}">
<apex:inputField value="{!s.Description}" required="false"/>
</apex:column>
</apex:pageblockTable>
<apex:pageBlockButtons >
<!-- Changed name to Recalculate -->
<apex:commandButton action="{!onSave}" value="Recalculate"/>
<!-- Changed name to Close -->
<apex:commandButton action="{!onCancel}" value="Close" immediate="true"/>
</apex:pageBlockButtons>
</apex:pageBlock>
<apex:pageBlock >
<apex:outputPanel styleClass="search">
Search for {!$ObjectType.Product2.LabelPlural}:
</apex:outputPanel>
<apex:actionRegion renderRegionOnly="false" immediate="true">
<apex:actionFunction name="fetchResults" action="{!updateAvailableList}" reRender="searchResults" status="searchStatus"/>
<!-- here we invoke the scripting to get out fancy 'no button' search bar to work -->
<apex:inputText value="{!searchString}" onkeydown="if(event.keyCode==13){this.blur();}else{resetTimer();}" style="width:300px"/>
<i>
<!-- actionStatus component makes it easy to let the user know when a search is underway -->
<apex:actionStatus id="searchStatus" startText="searching..." stopText=" "/>
</i>
</apex:actionRegion>
<br/>
<br/>
<apex:outputPanel id="searchResults">
<apex:pageBlockTable value="{!AvailableProducts}" var="a">
<apex:column headerValue="{!$ObjectType.Product2.Fields.ProductCode.Label}" value="{!a.Product2.ProductCode}"/>
<apex:column headerValue="{!$ObjectType.Product2.Fields.Name.Label}" value="{!a.Product2.Name}" />
<apex:column headerValue="{!$ObjectType.Product2.Fields.Family.Label}" value="{!a.Product2.Family}"/>
<apex:column headerValue="{!$ObjectType.Product2.Fields.Description.Label}" value="{!a.Product2.Description}"/>
<apex:column headerValue="Unit Price" value="{!a.UnitPrice}" style="text-align:right;" />
<apex:column >
<!-- command button in a column... neato -->
<apex:commandButton value="Select" action="{!addToShoppingCart}" reRender="selected,searchResults" immediate="true">
<!-- again we use apex:param to be able to tell the controller which row we are working with -->
<apex:param value="{!a.Id}" assignTo="{!toSelect}" name="toSelect"/>
</apex:commandButton>
</apex:column>
</apex:pageBlockTable>
<!-- We put up a warning if results exceed 100 rows -->
<apex:outputPanel styleClass="fyi" rendered="{!overLimit}">
<br/>
Your search returned over 100 results, use a more specific search string if you do not see the desired {!$ObjectType.Product2.Label}.
<br/>
</apex:outputPanel>
</apex:outputPanel>
</apex:pageBlock>
</apex:outputPanel>
</apex:form>
SObjectType
is correct. You should also just usegetId()
instead ofgetRecord().Id
. – Adrian Larson♦ Dec 17 '15 at 19:55