How many times have we seen this error? What does it mean? Usually it means you’ve requested a record using SOQL from an Object which returns zero results.

The simplest way is always the best I always say. Consider the following:


Account a = [SELECT Id, Name FROM Account WHERE Id= :myId];

This should return an Account record. BUT what if myId contains a value that is no longer available (for whatever reason). Then you’ll get the “List has no rows for assignment” error. To solve this problem we have two solutions.

The first is an easy way which doesn’t require nesting try..catch for every SOQL query call. If you always return the Object query as a List<> you can check the .size() property to determine if the query found any results.


List aList = [SELECT Id, Name FROM Account WHERE Id= :myId]

If( aList.size() > 0 )
{
   a = aList[0];
}
else
{
   System.debug('Empty Results');
}

While this works great you do need to put an if() statement everywhere. Another way is to wrap the code in a try..catch…


try
{
   Account a = [SELECT Id, Name FROM Account WHERE id= :myId];
}
catch( DmlException de )
{
   // Handle the Exception
}

The advantage to using the second method is that now we can get creative and build a simple class to use this to test any Query so you don’t have to put the code in everywhere.


Public Class QueryChecker
{
    public Boolean QueryHasResults( String q )
   {
      List sobjList = Database.query( q );
      return( sobjList.size() > 0)
   }  
}

Now you can test the condition before you even execute the query.


Boolean isValidQuery = QueryChecker.QueryHasResults('SELECT Id, Name FROM Account WHERE Id = XXXXXX');

if(! isValidQuery)
{
   // code to do something
}

There you have it. A simple solution to handling an annoying error.

[contact-form][contact-field label='Name' type='name' required='1'/][contact-field label='Email' type='email' required='1'/][contact-field label='Website' type='url'/][contact-field label='Comment' type='textarea' required='1'/][/contact-for

Filed under: Salesforce.com Programming