2

I have a trigger that is failing for some reason.

I am hoping somebody is able to help me trouble shoot this, and perhaps send me a text file with the corrected code.

This is what the error looks like: Error Message

Here is the error I get in the debugger:

Apex script unhandled trigger exception by user/organization: 00580000002J2ja/00D80000000cCxR ProvincesUpdateContacts: execution of AfterUpdate caused by: System.QueryException: List has no rows for assignment to SObject Trigger.ProvincesUpdateContacts: line 33, column 1

And here is a picture of the Apex Code: pic of the Apex code

And here is the code as text: (without the numbers as I couldn't see how to cut and paste those correctly)

trigger ProvincesUpdateContacts on Province__c (after insert, after update) {
/* 
 * force an update to the contact owners when a change is made
 * to the owner of a province (if not changed, do nothing) 
 */

// move the handler into a @future class to bypass governor limits

  list<string> ProvinceIds = new list<string>();
  list<string> ProvinceCoworkers = new list<string>();
  string strProvinceId, strCoworkerId;
  for (integer a = 0; a < trigger.size; a++) 
  {
    if (trigger.isinsert || 
       (trigger.isupdate && trigger.new[a].Responsible_Coworker__c != trigger.old[a].Responsible_Coworker__c))
    {   
      strProvinceId = trigger.new[a].id;
      strCoworkerId = trigger.new[a].Responsible_Coworker__c;
      ProvinceIds.add(strProvinceId);
      ProvinceCoworkers.add(strCoworkerId);
    }  
  }  

  System.debug ('*** ProvinceIds.size: ' + ProvinceIds.size());

/* // do the update asynchronously
  if (!ProvinceIds.IsEmpty()) 
  { 
    ProvincesFutureClassUpdateContactOwners.theUpdate(ProvinceIds, ProvinceCoworkers);
  }

*/
    User userQUnknownHistory = [SELECT Id FROM User WHERE Name = 'Q Unknown History' LIMIT 1];
    system.debug('***userQUnknownHistory id: ' + userQUnknownHistory.Id);

    Account accountKanalHayat = [SELECT Id FROM Account WHERE Name = 'Kanal Hayat' LIMIT 1];
    system.debug('***accountKanalHayat id: ' + accountKanalHayat.Id);

    List<Contact> ContactUpdates = new List<Contact>();        
    System.debug ('*** ContactUpdates.size: ' + ContactUpdates.size());

    Integer i = 0;
    Integer j = 0;

    for (integer a = 0; a < ProvinceIds.size(); a++) // iterate over all the provinces
    {
      Id theProvince = ProvinceIds[a];
      Id theCoworker = ProvinceCoworkers[a];

      if (theProvince == null)
      {
        system.debug('+++ ProvinceId is NULL. Continuing.');
        continue;
      }
      if (theCoworker == null)
      {
        system.debug('+++ CoworkerId is NULL. Continuing.');
        continue;
      }

//      List<contact> ContactIds = [Select c.Id from Contact c where c.Province_lookup__c = :theProvince AND c.City_lookup__c = null AND c.AccountId != :accountKanalHayat.Id AND c.OwnerId != :userQUnknownHistory.Id AND c.Do_not_reassign__c != :true ];      
//      List<contact> ContactIds = [Select Id from Contact where Province_lookup__c = :theProvince AND City_lookup__c = null AND AccountId != :accountKanalHayat.Id AND OwnerId != :userQUnknownHistory.Id AND Do_not_reassign__c != :true ];      

//    System.debug('*** will update ' + ContactIds.size() + ' contact records.');

//      for (Contact theContact : ContactIds) 


//      for (Contact theContact : [Select Id from Contact where Province_lookup__c = :theProvince AND City_lookup__c = null AND AccountId != :accountKanalHayat.Id AND OwnerId != :userQUnknownHistory.Id AND Do_not_reassign__c != :true ]) 
// this above for loop generates errors "caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing."
// so pull the City_lookup__c out of the SELECT
    System.Debug('theProvince: ' + theProvince);
    System.Debug('AccountKH: ' + accountKanalHayat.Id);
    System.Debug('UserQ: ' + userQUnknownHistory.Id);

//      for (Contact theContact : [Select Id,City_lookup__c,AccountId,OwnerId from Contact where Province_lookup__c = : theProvince AND AccountId != :accountKanalHayat.Id AND OwnerId != :userQUnknownHistory.Id AND Do_not_reassign__c != :true ])
    date d = system.today().addDays(-3 * 365);

      for (Contact theContact : [Select Id, AccountId, City_lookup__c, do_not_reassign__c, OwnerId from Contact where Province_lookup__c = :theProvince and CreatedDate > :d ]) 
      if ((theContact.City_lookup__c == NULL)&&(theContact.AccountId != accountKanalHayat.Id)&&(theContact.OwnerId != userQUnknownHistory.Id)&&(theContact.do_not_reassign__c != true))
      {
        System.debug ('Update contact ' + theContact.Id);
        theContact.OwnerId = theCoworker;
        System.debug ('Coworker: ' + theCoworker);              
        try 
        { 
          ContactUpdates.add(theContact);
          System.Debug('Add contact id: ' + theContact.Id);
        }
        catch(Exception e) 
        { //catch errors
          System.Debug('ProvincesUpdateContacts failure: '+e.getMessage()); 
        } 
    }      
    }  
    update ContactUpdates;
}
5

You are doing a query on line 33 and the the query is limited to 1 result and it is stored in an sobject record i.e. User record to be specific. You can assign the result of a query to either an sobject or a list of sobject. But when you assign a query to an sobject instance and not a list, the query HAS TO return something and cannot be null. If it doesn't return anything then System.QueryException is thrown.

In your case, the query "SELECT Id FROM User WHERE Name = 'Q Unknown History' LIMIT 1" is returning zero results. This is causing the exception.

In case this is not the expected behaviour and the query should never be empty, debug as to why the query is returning empty. Is the query correctly constructed, do you have the correct data ready at backend? etc

Well, if the fact that query can be return empty handed is a reasonable scenario, then assign the query result to a "List" variable instead of a single user variable. Later you can access the record by "Userlistname[0]". Doing this will prevent the "System.QueryException" from being thrown because it is fine for the list to be empty when a query is assigned to it. Hope this helps.

Thanks

3

If you want to run a query for which you are not certain there will be results, the best practice is something like:

User desiredRecord;
List<User> candidates = [SELECT Id FROM User WHERE Name = 'Whatever' LIMIT 1];
if (!candidates.isEmpty()) desiredRecord = candidates[0];
else
{
    // fallback logic
}
  • 1
    @PaulWeaver Keep in mind that all this does is allow you to handle the error more elegantly, you need to figure out why you are retrieving no data when you expect to retrieve a row (I would guess you deployed the change to production and forgot to add the user that the code retrieves to the production environment). – IllusiveBrian Jan 18 '16 at 23:36
2

An alternative to Adrian's answer is to catch the exception thrown.

try
{
    User myUser = [select ID from User where Name = 'The User'];
}
catch (System.QueryException ex)
{
    System.debug(ex.getMessage());
}

The main difference with this approach is that multiple single record assignments can be done within one try-catch block, reducing the amount of code you need to write if more than one statement has to be executed that might throw an exception.

However, this only solves the problem of the code allowing the exception to bubble up to the page, you'll need to figure out why the User you are trying to retrieve doesn't exist.

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