Tuesday, July 12, 2016

System.QueryException: List has no rows for assignment to SObject

Usually we will come across this type issue in many places and in many situations also .In the same way I have come across the same issue in one of the my class and I have done some work around it to fix this issue.I will explain this one with an example which may brings you an complete understanding about the issue.

Scenario:
I have 3 objects called Time_Track__c,Expert__c and User.Expert__c and User having a lookup relationship where User (Sforce_User__c) is acting as parent for Expert object and Time_Track__c and Expert__c also having lookup relationship where Time_Track__c is acting as child for Expert (Expert_Name__c) object .I have an visual force page which is using Time_Track__c  as a Standard Controller and I am having some extension class on same page .In the constructor of the extension class I am trying to query on Expert__c object based on logged in user to assign this expert to new time track as shown below

Source Code:
public class TrackExtn {
  public Time_Track__c timeTrack{get;set;} 

  public TrackExtn(ApexPages.StandardController controller) {
    timeTrack = new Time_Track__c();        
    timeTrack= (Time_Track__c)controller.getRecord();
       
    if(controller.getId()==null)
    {
       Expert__c exp = new Expert__c();
       //In below line you will receive List has no rows for assignment to SObject
       exp = [Select id from Expert__c where Sforce_User__c=:UserInfo.getUserId() limit 1];
       timeTrack.Expert_Name__c = exp!=null?exp.id:null;
           
    }

    }
}

Error:

When you execute the above code at run time if your having at least one matching Expert__c record for logged in user it will work very smoothly .If there is no matching Expert__c record for logged in user will end up with System.QueryException: List has no rows for assignment to SObject . Even if the matching record is not there it should continue fir the next lines with out throwing any error but here it's failing and terminating the complete execution. To handle this we need to follow below work around solution.


Workaround Solution:

We need to convert the Query result holder from SObject type to List<SObject> type that will resolve the issue .Please look at the below code for the work around solution

public class TrackExtn {
  public Time_Track__c timeTrack{get;set;} 

  public TrackExtn(ApexPages.StandardController controller) {
    timeTrack = new Time_Track__c();        
    timeTrack= (Time_Track__c)controller.getRecord();
      
    if(controller.getId()==null)
    {
        //Convert Sobject type to List<Sobject> type to store query result
        List<Expert__c> listexp =new List<Expert__c>();
        listexp = [Select id from Expert__c where Sforce_User__c =:UserInfo.getUserId() limit 1];
        System.debug('loggedinTech ..'+listexp );
        td.Technician__c = listexp.isEmpty()?null:listexp[0].id;
           
    }

    }
}

Thanks for visiting...Enjoy!