How to Prevent the System.QueryException

Question

I am getting "Error System.QueryException: List has no rows for assignment to SObject".

Line 1: Account ac = [select id from Account where name='testaccount' limit 1]
Line 2: if(ac != null) {
          //do something
        }

I got the above error in line 2 if there is no record.

Any help?

Answer 1

If you want to assign query results to a single sObject instance, you must guarantee that the query will return exactly 1 record. Not zero, not 2 or more, exactly 1.

If you can't, there are two options. First is assign to a list and test the size:

List<Account> accts = [select name from Account where id = ???]; 
if (accts.size() == 1) {
   ...do something here...
}

Alternatively, you now know the exception you will recieve, so you can use try/catch.

Account myAcct;
try {
myAcct = [select name from Account where id = ???];
} catch (SearchException e) {
myAcct = new Account(); //or do something else, perhaps
}

I like this when I either need the retrieved record, or a new empty record. I commonly use this pattern in my Visualforce page controller constructor methods.

Note: I used the where Id = where clause as this implicitly returns a maximum of 1 record on the Force.com platform, but does not guarantee a record be returned in all cases.

Answer 2

I dont know whether this is a best practice or not but, whenever i have encountered such error or rather always, i use a List instead of the object itself. and then later on in the code, check whether the list has any elements present in it or not.

So in your case, you could do something like:

List<Account> accounts = new List<Account>();accounts = [select id from Account where name='testaccount' limit 1] // Doing so does not throw any error

And then somewhere down in your code, where you are trying to access the query result, add

if(!accounts.isEmpty()){ // do something }

and since you are going to have only one record, you could simply use

accounts[0].Id to reference the Id field or for some other operation

Answer 3

List<Account> ac = [select id from Account where name='testaccount' limit 1];
if(ac.size() >0) { 
    //Do some thing
}

Check with size assigning to list.