+ Start a Discussion
Andreas WissmeyerAndreas Wissmeyer 

How to avoid "List has no rows for assignment to SObject" and return a custom error message for 5 seconds, then redirect to homepage

Hello all,
this problem is bugging me since days. Below code works as long as the SOQL query is successful (an account is found), else I get the following standard Salesforce error screen:

List has no rows for assignment to SObject 
An unexpected error has occurred. Your development organization has been notified.

Instead of that, I would like to implement a behavior that, both at the beginning or after pressing the NotOK butto, should be able to handle the case: "no accounts found" instead, showing a brief customized error message and then redirectng me to salesforce homepage.

Below my code. I think I lack some basic knowledge, probably some of you is able to provide me with a solution..

public class Account_Controller_2 
{
    public Account account;

    public Account_Controller_2()  {}
    
	public Account getAccount() 
    {
        account = [SELECT Id, Name, Last_Status__c,Additional_Information__c,Ongoing_Problem__c,Last_Update__c      FROM Account WHERE (OwnerId = :UserInfo.getUserID()) AND (Type <> 'Dead') AND (Last_Update__c < LAST_N_DAYS:150) 
ORDER BY Last_Update__c LIMIT 1];
		return account;
    }
    
    public PageReference NotOK() 
    {        
        account.Ongoing_Problem__c = true;
        account.Last_Update__c = Date.today();
        try 
        {
            upsert(account);
        }	
        catch (Exception e)
        {
            System.debug('NotOk Exception1 ===> '+e);
            ApexPages.addmessage(new ApexPages.message(ApexPages.severity.ERROR,'Please use Save button at the top     of the page to save header information.'));
        }
        update account;
        try
        {
           account = getAccount(); 
        }
        catch (Exception e)
        {
            System.debug('NotOk Exception2 ===> '+e);
            ApexPages.addmessage(new ApexPages.message(ApexPages.severity.ERROR,'Please use Save button at the top of the page to save header information.'));
        }
        return null;
    }
Thanks in advance for your help on this. Cheers, Andi
Best Answer chosen by Andreas Wissmeyer
AmrenderAmrender
Hi Andreas

You are right. I just developed the same in my developer account and figured out what is the issue. You need to call your getAccount() method in displayAccount() like below
 
public PageReference displayAccount() {
	getAccount();
	if(account.id != null) {
		return null; 
	} else {
		return new PageReference('/home/home.jsp'); //it will redirect to home tab
	}
}
I have tested this code and it is working fine. Let me know if you still face the issue.

Regards
Amrender

All Answers

AmrenderAmrender
Hello Andreas

The issue is in you getAccount() method. Use List<Account> instead of Account. Try below code:
 
public Account getAccount() 
{
	List<Account> accList = [SELECT Id, Name, Last_Status__c,Additional_Information__c,Ongoing_Problem__c,Last_Update__c FROM Account WHERE (OwnerId = :UserInfo.getUserID()) AND (Type <> 'Dead') AND (Last_Update__c < LAST_N_DAYS:150) 
	ORDER BY Last_Update__c LIMIT 1];
	if(!accList.isEmpty())
		account=accList[0];
	else
		account = new Account();
	return account;
}


Regards
Amrender
Andreas WissmeyerAndreas Wissmeyer

Hello Amrender,
your proposed solution gets things a lot better, but does not completely solve the issue. Now I get a page with an empty account (result of new Account() instrunction) and I can force the exit only after the upsert fails (just because the new accounts lacks a requred field. To sum up, now I don't get anymore the Visualforce error page but an empty account one, and I'm stuck there until I press one of my custom defined buttons..

Thanks a lot for your answer anyway!

Cheers, Andi

AmrenderAmrender
Hi Andreas

I see in your NotOK() method, first you are doing an upsert (line 20) and then update (line 27). Why??

I tried to update the logic. See below.
public PageReference NotOK() 
{  	
	account.Ongoing_Problem__c = true;
	account.Last_Update__c = Date.today();
	if(account.id == null) {
		//populate the data in the required fields here.
	}
	
	try 
	{
		
		upsert(account);
	}	
	catch (Exception e)
	{
		System.debug('NotOk Exception1 ===> '+e);
		ApexPages.addmessage(new ApexPages.message(ApexPages.severity.ERROR,'Please use Save button at the top     of the page to save header information.'));
	}
	/*update account;
	try
	{
	   account = getAccount(); 
	}
	catch (Exception e)
	{
		System.debug('NotOk Exception2 ===> '+e);
		ApexPages.addmessage(new ApexPages.message(ApexPages.severity.ERROR,'Please use Save button at the top of the page to save header information.'));
	}*/
	return null;
}

I would able to help you more if you let me know the what exactly you want to do.


Regards
Amrender
Andreas WissmeyerAndreas Wissmeyer

Hello Amrender,

In fact that part was useless, I removed it. Thanks a lot for your suggestion!

My code works almost perfectly now, except for the fact that I still get an empty account view when the SOQL query is not satisfied anymore.

I would like to simply redirect the flow towards a page with a warning ("No more accounts need to be contacted") and after 5 seconds (or also an user triggered action, like clicking on a "OK" button), getting to the "Feeds" classic tab of Salesforce1. Code below:

    public Account getAccount() 
    {
        List<Account> accList = [SELECT Id, Name, Last_Status__c,Additional_Information__c,Ongoing_Problem__c,Last_Update__c FROM Account WHERE (OwnerId = :UserInfo.getUserID()) AND (Type <> 'Dead') AND (Last_Update__c < LAST_N_DAYS:150) 
        ORDER BY Last_Update__c LIMIT 1];
        if(!accList.isEmpty())
        {
            account=accList[0];
        }           
        else
        {
            account = new account();   
            contacts = [SELECT Name FROM Contact WHERE Name = :account.Name];
        }    
        return account;
    }
    
    public List<Contact> getContacts()
    {
        if(contacts == null) 
            contacts = [SELECT Name, Phone, Email FROM Contact WHERE AccountId = :account.Id];
        return contacts;
    }
    
    public PageReference OK() 
    {
        account.Ongoing_Problem__c = false;
        account.Last_Update__c = Date.today();
        try 
        {
            upsert(account);
        }    
        catch (Exception e)
        {
            System.debug('Upsert not successful (method OK) ===>'+e);
            return new PageReference('/one/one.app');
        } 
        return null;         
    }

Is my requirement still to vague?

Cheers, Andi

AmrenderAmrender
I have modified the OK() function. Try the below code.
public PageReference OK() {
	if(account.id != null) {
		account.Ongoing_Problem__c = false;
		account.Last_Update__c = Date.today();
		try {
			upsert(account);
		} catch (Exception e) {
			System.debug('Upsert not successful (method OK) ===>'+e);
		}
	} else {
		return new PageReference('/one/one.app');
	}
    return null;
}

Let me know if you still have issues.

Regards
Amrender
Andreas WissmeyerAndreas Wissmeyer

Hello again Amrender,

every intervention you make is an improvement to my code (and I thank you for that :) ), but the issue of the empty account when no more valid ones are available is still there, and it comes from the following part of the code in function getAccount:

 else {
     account = new account();   
     contacts = [SELECT Name FROM Contact WHERE Name = :account.Name];
  }  

Cheers, Andi

AmrenderAmrender
Here account.name is null and you are trying to query all the Contacts where Name is Null. This query will not give you any result.Could you please paste your whole code here.

Regards
Amrender
Andreas WissmeyerAndreas Wissmeyer

it's necessary for me to reload the contacts at this stage in order not to get the ones from the previous account. This is a trick I use, but it's not the source of the issue. Issue comes from the fact that I am forced to create a new account at the moment in the else statement. It would be ideal for me to return a page instead, but this is not possible because the object returned by the function must be an account. Whoule interesting part here, the rest is not relevant for my question:

public class Account_Controller_2 
{
    public List<Account> accList;
    public Account account; 
    public List<Contact> contacts;

    public Account_Controller_2() 
    {       
    }
    
    public Account getAccount() 
    {
        List<Account> accList = [SELECT Id, Name, Last_Status__c,Additional_Information__c,Ongoing_Problem__c,Last_Update__c FROM Account WHERE (OwnerId = :UserInfo.getUserID()) AND (Type <> 'Dead') AND (Last_Update__c < LAST_N_DAYS:150) 
        ORDER BY Last_Update__c LIMIT 1];
        if(!accList.isEmpty())
        {
            account=accList[0];
            contacts = [SELECT Name, Phone, Email FROM Contact WHERE AccountId = :account.Id];
        }           
        else
        {
            account = new account();   
            contacts = [SELECT Name FROM Contact WHERE Name = :account.Name];
        }    
        return account;
    }
    
    public List<Contact> getContacts()
    {
        if(contacts == null) 
            contacts = [SELECT Name, Phone, Email FROM Contact WHERE AccountId = :account.Id];
		return contacts;
    }
}

Thanks a lot for your help so far. Cheers, A.
AmrenderAmrender
Hi Andreas

Everytime I see a different code :)

Your query contacts = [SELECT Name FROM Contact WHERE Name = :account.Name]; tries to fetch the contact where Name = null (because account.name is null) and will not give any result.

Can we have a skpe call to understand and resolve your issue? My skype id is ghangas.amrender

Happy to help you!!
Andreas WissmeyerAndreas Wissmeyer
Hello Amrender, sorry but I cannot do a Skype call because I work in an openspace. We're getting lost in translation I think, you keep focusing to the contacts thing, which is a code I did ON purpose to provide a limitation to the account = new account(); thing.
Basically, everything works OK until there is an account satisfying the SOQL query, but at some point there is NO account available anymore. At this point, at the moment I create a new empty account just to avoid the error from SOQL. What I would like to do instead is to be somehow redirected to Salesforce1 feed page, but at the moment I cannot do that because I have to return an account object from the getAccount function. That's basically my whole issue, hope it's clearer now :)

And again, thanks a lot for your patient help until now.. A.
AmrenderAmrender
Hi Andreas

Let me rephrase your requirement.

You have a Visualforce page with controller Account_Controller_2. Once your page is loading everything is working fine if there is an Account record returned by SOQL query.
But in case there is no account found you want to redirect to the home page. Please correct me if I misunderstood your requirement.

Now there are two scenarios 
1) you are pressing any button on visualforce page to get the account
2) without pressing any button you want to display the account returned by the SOQL query

and I assume that in your case it is second scenario. What you need to do in this case

Add an additional parameter in your <apex:page> tag called action. See below
<apex:page controller="Account_Controller_2" action="{!displayAccount}">

</apex:page>

Update your class like below
public class Account_Controller_2 
{
    public List<Account> accList;
    public Account account; 
    public List<Contact> contacts;

    public Account_Controller_2() 
    {       
    }
    
    public Account getAccount() 
    {
        List<Account> accList = [SELECT Id, Name, Last_Status__c,Additional_Information__c,Ongoing_Problem__c,Last_Update__c FROM Account WHERE (OwnerId = :UserInfo.getUserID()) AND (Type <> 'Dead') AND (Last_Update__c < LAST_N_DAYS:150) 
        ORDER BY Last_Update__c LIMIT 1];
        if(!accList.isEmpty())
        {
            account=accList[0];
            contacts = [SELECT Name, Phone, Email FROM Contact WHERE AccountId = :account.Id];
        }           
        else
        {
            account = new account();   
            //contacts = [SELECT Name FROM Contact WHERE Name = :account.Name];
        }    
        return account;
    }
    
	/*
	//not required
    public List<Contact> getContacts()
    {
        if(contacts == null) 
            contacts = [SELECT Name, Phone, Email FROM Contact WHERE AccountId = :account.Id];
		return contacts;
    }
	*/
	
	public PageReference displayAccount() {
		if(account.id != null) {
			return null; 
		} else {
			return new PageReference('/home/home.jsp'); //it will redirect to home tab
		}
	}
}
Let me know if it helps.

Regards
Amrender
 
Andreas WissmeyerAndreas Wissmeyer

Hello Amrender, your code modification makes system always default to the home page, also when an account is available. I tried to develop your idea placig debug logs everywhere, but until now the best behavior I could get is still the one I asked to solve in my question in the first place.. Feel free to leave this question unanswered, if in one week I don't get any reply you will get my "Best answer" mar anyway. Thanks a lot!

Cheers, Andreas

AmrenderAmrender
Hi Andreas

You are right. I just developed the same in my developer account and figured out what is the issue. You need to call your getAccount() method in displayAccount() like below
 
public PageReference displayAccount() {
	getAccount();
	if(account.id != null) {
		return null; 
	} else {
		return new PageReference('/home/home.jsp'); //it will redirect to home tab
	}
}
I have tested this code and it is working fine. Let me know if you still face the issue.

Regards
Amrender
This was selected as the best answer
Andreas WissmeyerAndreas Wissmeyer

Hello Amrender,

I tried this approach (adding getAccount() yesterday but it worked 50%. In fact I can consider this question solved though because:

CASE 1: no accounts available when opening the app. Your updated solution works, because it redirects me directly to the homepage.
CASE 2: I do an action on the last available account, and I switch to the next one (not available though). In this case your solution does not work, but I found a workaround myself. See below code snippet:

public Account getAccount() 
    {
        List<Account> accList = [SELECT Id, Name, Last_Status__c,Additional_Information__c,Ongoing_Problem__c,Last_Update__c FROM Account WHERE (OwnerId = :UserInfo.getUserID()) AND (Type <> 'Dead') AND (Last_Update__c < LAST_N_DAYS:150) 
        						ORDER BY Last_Update__c LIMIT 1];
        if(!accList.isEmpty())
        {
            account=accList[0];
            contacts = [SELECT Name, Phone, Email, Role__c, Area__c FROM Contact WHERE (AccountId = :account.Id) 
                        AND ((Role__c = 'Head') OR (Role__c = 'Owner') OR (Role__c = 'Staff')) AND ((Area__c =          'Music') OR (Area__c = 'IT') ORDER BY Role__c LIMIT 7];
        }
        else
        {
            account = new account(); 
            account.Last_Status__c = 'CONGRATULATIONS! NO MORE ACCOUNTS TO BE CONTACTED. PRESS "EXIT" BUTTON';
            contacts = [SELECT Name, Phone, Email FROM Contact WHERE (AccountId = :account.Id) LIMIT 0];
        }            
        return account;
    }
   
   public List<Contact> getContacts()
    {
        if(contacts == null && account.Id != null) 
            contacts = [SELECT Name, Phone, Email, Role__c, Area__c FROM Contact WHERE (AccountId = :account.Id) 
                        AND ((Role__c = 'Head') OR (Role__c = 'Owner') OR (Role__c = 'Staff')) AND ((Area__c = 'Music') OR (Area__c = 'IT'))
                        ORDER BY Role__c LIMIT 7];
		return contacts;
    }

I display an empty account but I manually fill in a field (which on my page is a big editable window) with instructions for the user ("PLEASE EXIT"). Elegant? No. Enough for my purposes? Yes.
About the contacts lines you kept suggesting me to delete, but I kept in the final version: for how my VF page is structured, they are necessary. The absence of contacts = [SELECT ... LIMIT 0]; made me appear the contacts of the previous valid account in my final "goodbye" page, while the absence of method getContacts() (which should be useless per se, I agree with you) created sometimes reading problems. 

THANKS A LOT FOR YOUR PATIENCE!

Have a nice day/week/month/year!

Cheers, Andreas