1

I've been exploring different ways to assign a SOQL query that would potentially have no results in some situations (when working correctly).

However, I can't seem to avoid the System.QueryException: List has no rows for assignment to SObject error.

Here is the relevant bit from my code:

list<User> ccUsers = new list<User>();
ccUsers.addAll([SELECT id FROM User WHERE ContactID = :joint.contact__c AND isActive = True LIMIT 1]);
  • I thought that by using a List, rather than assigning the SOQL directly to the User sObject, I could avoid the error, but I was wrong.
  • Then I thought that by using the list method .addAll(list<>), I could avoid the error (after all, shouldn't you be able to add an empty list to another list?), but I still get the error.

Bottom line, I don't want my code to break and stop running just because a SOQL query doesn't return any results. I just want it to return an empty list of the sObject being queried.

edit: I just ran my current code in Execute Anonymous in console, and it successfully ran without error, with CCusers being an empty list, as desired. So why am I getting Developer script exception emails with a stacktrace to the exact line of code where I'm adding a SOQL query result to a list?

1
  • 2
    interesting -- the examples in the Apex doc suggest that when using Limit 1 - you can assign to a single sobject without using a [0] list index (that is, the SOQl does not return a list). If it works in DC but not in your apex class; change the apex class version to V35
    – cropredy
    Oct 22, 2015 at 17:49

4 Answers 4

2

You instead need to be assigning it directly to the list:

List<User> ccUsers = [SELECT Id FROM User WHERE ContactID = :joint.contact__c AND isActive = true LIMIT 1];

if (ccUsers.isEmpty())
{
  //Do something because we found no matching Users
} else
{
  //Do something else because there is a matching user
}
4
  • This didn't work, as I stated in my OP. I do have an if clause that tests if the list is empty, but I still get the null error when assigning the SOQL query directly to list.
    – smohyee
    Oct 22, 2015 at 16:03
  • @smohyee This is not stated in your OP. In your OP you describe setting it directly to a User, not a List<User>.
    – dphil
    Oct 22, 2015 at 16:06
  • Shouldn't be important, but the first bullet point below my current code in the OP states that after trying assigning directly to a user, i then tried assigning to a list, but still got the error.
    – smohyee
    Oct 22, 2015 at 16:09
  • @smohyee I would try it again. The way I describe is the correct way you should be doing it. Maybe you accidentally didn't save or something but if the way I describe truly doesn't work then you need to open a bug with Salesforce.
    – dphil
    Oct 22, 2015 at 16:33
2

I would suggest something similar to @dphil, but also instanciate the list as well.

list<User> ccUsers = new list<User>([SELECT id FROM User WHERE ContactID = :joint.contact__c AND isActive = True LIMIT 1]);

Now, even if the SQOL returns no rows, you still have a list of size 0, and you won't get the query exception.

1

You can use try/catch block for this. But it is strange that you're getting error on a list.

With try catch it will look like this;

try{
  list<User> ccUsers = new list<User>();
  ccUSers = [SELECT id FROM User WHERE ContactID = :joint.contact__c AND isActive = True LIMIT 1];
   //not empty
}catch(Exception e){
    //empty list
}
1

The error which we are getting here is because the SOQL returns 0 result.

You need to make the query conditional - ie have some check prior to sending the query or the last resort can be try catch block. Although i personally feel you must go with the first option

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .