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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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