function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Rick MacGuiganRick MacGuigan 

Process a SOQL result is null or > 1

I have a SOQL in an extended controller that supports a visualforce page. I want to make sure the result only contains one record and is not null. May need to remove the LIMIT 1 entry because I want to make sure there is truly only one record to prevent dirty data from propogating . If eeither of the two conditions are discovered (more than one record or null) then I want to write an error message to the visualforce message component. 

Below is my extended contoller. Any examples would bee apprecciated. 
  
public with sharing class ParentChildExtensionAUTOsample {

/*
This code supports the PROD_Audiit_Auto_PolicySample visualforce page.
FUNCTION:
Receives parent ID (accID) passed via commandbutton or custom button on parent related list.
AccId must be passed via URL since we are instantiating a new object reccord which is not saved.
accID is needed to query parent fields that are used on the visualforce page for rendering.
A non list object is used for the SOQL query to the parent. The result set is used in the visualforce page for setting control variables for rendering. 
* we are always passing the parent ID to this query and using LIMIT=1 per salesforce best practices. 
The parent ID is loaded into the related list field on the instanitated object to establish relationship when saved. 
*/

public Audit__c audit{get;set;}
Auto_Audit_Sample_Policy__c record;

String accId;
@TestVisible private Apexpages.StandardController controller; 

public ParentChildExtensionAUTOsample(ApexPages.StandardController standardController)
{
//set sample to current record.
this.record = (Auto_Audit_Sample_Policy__c)standardController.getrecord();
//grab AccID passed in from URL
accID=ApexPages.currentPage().getParameters().get('AccId');
  
 audit= [SELECT Id, Name, Policy_Information__c, Policy_Number__c, Prior_Carrier__c, Expiring_Premium__c, Reinstatement_Information__c, Insured_Location__c, Agent_Information__c, Agent_City__c, Agent_Name__c, 
    Driver_Information__c, Age__c, Gender__c, Marital_Status__c, Named_Insured_Add_Driver__c, Occupation__c, Business_Use__c, Vehicle_Information__c, Model_Year__c, Vehicle_Type__c, ISO_Symbol__c     
    FROM Audit__c WHERE Id = :ApexPages.currentPage().getParameters().get('AccId') LIMIT 1];

//Load the parent Name (ID) into the Audit field. 
record.Audit__c = audit.Id;
}
}

 
Best Answer chosen by Rick MacGuigan
Chris GaryChris Gary

Hi Rick -
If you are using a LIMIT 1 in your SOQL and assigning to a List, then it won't be necessary to test for more than one record - but you will definitely need to test to make sure at least one record fulfilled the SOQL result.
When I develop in SOQL, and I only am expecting one result, then usually what I do is to perform the SOQL statement and assign it to a single variable, and wrap that in a try-catch.  Then in my catch, I account for if there is an error. I usually assume that having more than one result is just as bad as having none, and therefore I usually treat the error the same. 
However, If you have the situation where you need to treat having more than one record that fulfills the criteria differently than if there were no records, then you may want to stick with the idea of putting the result in a list, and test for the  List.size() > and < 1 in two different ifs.  Code sample attached.  I hope this help you.  If so, please like and vote!
  
Account a;

//use this if having more than one record is just as bad as having none
try {
    a = [SELECT Id, Name FROM Account WHERE ThisIsSpecial__c = true];
}catch(System.Exception ex){
    System.debug(LoggingLevel.ERROR,'I came across a problem! Either more than one or no records were found!');
}

//use this if you need to treat having more than 1 record differently than none;
List<Account> aList = [SELECT Id, Name FROM Account WHERE ThisIsSpecial__c = true];
if(aList.size() > 1) {
  System.debug(LoggingLevel.ERROR,'More than 1 record found!');
} else if(aList.size() == 0) {
  System.debug(LoggingLevel.ERROR,'No records were found!');
} else {
  //you got what you needed.
}

All Answers

Chris GaryChris Gary
SImply wrap your SQL Statement in a Try and catch the Exception .  When you are trying to perform a SOQL query and assign it to a Single SObject, not a list or map, if the Query returns more than one record or no records at all, a System.DMLException is thrown.  Make sure you also catch System.Exception as well. 
public with sharing class ParentChildExtensionAUTOsample {

/*
This code supports the PROD_Audiit_Auto_PolicySample visualforce page.
FUNCTION:
Receives parent ID (accID) passed via commandbutton or custom button on parent related list.
AccId must be passed via URL since we are instantiating a new object reccord which is not saved.
accID is needed to query parent fields that are used on the visualforce page for rendering.
A non list object is used for the SOQL query to the parent. The result set is used in the visualforce page for setting control variables for rendering. 
* we are always passing the parent ID to this query and using LIMIT=1 per salesforce best practices. 
The parent ID is loaded into the related list field on the instanitated object to establish relationship when saved. 
*/

public Audit__c audit{get;set;}
Auto_Audit_Sample_Policy__c record;

String accId;
@TestVisible private Apexpages.StandardController controller; 

public ParentChildExtensionAUTOsample(ApexPages.StandardController standardController)
{
//set sample to current record.
this.record = (Auto_Audit_Sample_Policy__c)standardController.getrecord();
//grab AccID passed in from URL
accID=ApexPages.currentPage().getParameters().get('AccId');
  
try{
 audit= [SELECT Id, Name, Policy_Information__c, Policy_Number__c, Prior_Carrier__c, Expiring_Premium__c, Reinstatement_Information__c, Insured_Location__c, Agent_Information__c, Agent_City__c, Agent_Name__c, 
    Driver_Information__c, Age__c, Gender__c, Marital_Status__c, Named_Insured_Add_Driver__c, Occupation__c, Business_Use__c, Vehicle_Information__c, Model_Year__c, Vehicle_Type__c, ISO_Symbol__c     
    FROM Audit__c WHERE Id = :ApexPages.currentPage().getParameters().get('AccId') LIMIT 1];
}catch(System.DMLException){
 //you decide what to do here.
}catch(System.Exception){
 //also decide what to do here
}

//Load the parent Name (ID) into the Audit field. 
record.Audit__c = audit.Id;
}
}

 
Rick MacGuiganRick MacGuigan
Thanks for the response Chris. I do have other controllers that use a list object. How would you recommend I test to make sure the result only contains one record and is not null ?
  
List< Audit__c> drivers = [ select Id, Driver_Information__c, Age__c, Gender__c, Marital_Status__c, Named_Insured_Add_Driver__c, Occupation__c, Business_Use__c, MVR_Violations__c, Credit_Scoring__c                
           from Audit__c where Id = :ApexPages.currentPage().getParameters().get('accId')];

 
Chris GaryChris Gary

Hi Rick -
If you are using a LIMIT 1 in your SOQL and assigning to a List, then it won't be necessary to test for more than one record - but you will definitely need to test to make sure at least one record fulfilled the SOQL result.
When I develop in SOQL, and I only am expecting one result, then usually what I do is to perform the SOQL statement and assign it to a single variable, and wrap that in a try-catch.  Then in my catch, I account for if there is an error. I usually assume that having more than one result is just as bad as having none, and therefore I usually treat the error the same. 
However, If you have the situation where you need to treat having more than one record that fulfills the criteria differently than if there were no records, then you may want to stick with the idea of putting the result in a list, and test for the  List.size() > and < 1 in two different ifs.  Code sample attached.  I hope this help you.  If so, please like and vote!
  
Account a;

//use this if having more than one record is just as bad as having none
try {
    a = [SELECT Id, Name FROM Account WHERE ThisIsSpecial__c = true];
}catch(System.Exception ex){
    System.debug(LoggingLevel.ERROR,'I came across a problem! Either more than one or no records were found!');
}

//use this if you need to treat having more than 1 record differently than none;
List<Account> aList = [SELECT Id, Name FROM Account WHERE ThisIsSpecial__c = true];
if(aList.size() > 1) {
  System.debug(LoggingLevel.ERROR,'More than 1 record found!');
} else if(aList.size() == 0) {
  System.debug(LoggingLevel.ERROR,'No records were found!');
} else {
  //you got what you needed.
}
This was selected as the best answer
Rick MacGuiganRick MacGuigan
Chris, excellent and thanks for the examples. If I take this a step further into my visualforce page then am I correct in simply passing the apex catch to the visualforce 'Messages' component ? I do like your first example . The second example may save us some steps in any problem determination. In the event we have duplicate ids then we would want to know that specific level of detail. 
  
<apex:messages title="data incorrectly entered." style="background-color:yellow; color: red" /> 


Account a;

//use this if having more than one record is just as bad as having none
try {
    a = [SELECT Id, Name FROM Account WHERE ThisIsSpecial__c = true];
}
catch(System.Exception ex)
{
    System.debug(LoggingLevel.ERROR,'I came across a problem! Either more than one or no records were found!');
	ApexPages.addMessages(ex);
}

//use this if you need to treat having more than 1 record differently than none;
List<Account> aList = [SELECT Id, Name FROM Account WHERE ThisIsSpecial__c = true];
if(aList.size() > 1) {
  System.debug(LoggingLevel.ERROR,'More than 1 record found!');
  ApexPages.addMessages('More than 1 record found!');
} else if(aList.size() == 0) {
  System.debug(LoggingLevel.ERROR,'No records were found!');
  ApexPages.addMessages('No records were found!');
} else {
  //you got what you needed.
}






 
Chris GaryChris Gary
Hey Rick,

If you want to actually pass the message that you receive over to your visual force page into that <apex:messages/> element,  I have included a revision of the code to show you how to do so. 
Account a;

//use this if having more than one record is just as bad as having none
try {
    a = [SELECT Id, Name FROM Account WHERE ThisIsSpecial__c = true];
}
catch(System.Exception ex)
{
    System.debug(LoggingLevel.ERROR,'I came across a problem! Either more than one or no records were found!');
       //not the right way. - it will not compile.
      //ApexPages.addMessages(ex);
       //this is the better way
      ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,ex.getMessage()));
}

//use this if you need to treat having more than 1 record differently than none;
List<Account> aList = [SELECT Id, Name FROM Account WHERE ThisIsSpecial__c = true];
if(aList.size() > 1) {
  System.debug(LoggingLevel.ERROR,'More than 1 record found!');
  //this will not compile
  //ApexPages.addMessages('More than 1 record found!');
  //try this
  ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,'More than 1 record Found'));
} else if(aList.size() == 0) {
  System.debug(LoggingLevel.ERROR,'No records were found!');
  //this will not compile
  //ApexPages.addMessages('No records were found!');
  //try this
  ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,'No records were found!'));
} else {
  //you got what you needed.
}