Can't select from Accounts in Trigger

Question

I am implementing a trigger to convert Lead records that have a status of 'Qualified'.

I was able to get a trigger working except it was creating new accounts for every lead converted, which we didn't want. In the Lead record the account name is stored as a string in the Company field. I want to select from accounts with Account.Name = Lead.Company so I can get the Account.ID and use the setAccountID() function to avoid creating new accounts.

What I have so far:

trigger LeadTrigger on Lead (after insert, after update) {
    LeadStatus convertStatus = [
        select MasterLabel
        from LeadStatus
        where IsConverted = true
        limit 1
    ];

    List<Database.LeadConvert> leadConverts = new List<Database.LeadConvert>();
    for(Lead tLead : Trigger.new) {
        String accName = tLead.Company;

        Account accountId = [
            select Id from 
            Account where Account.Name = ':accName' and Account_External_Id__c != null 
            limit 1
        ];
        // and Account_External_Id__c != null
        if((!tLead.isConverted && tLead.Status== convertStatus.MasterLabel)) {
            Database.LeadConvert lc = new Database.LeadConvert();
            //String oppName = lead.Name;
            lc.setAccountId(accountId.Id);
            lc.setLeadId(tLead.Id);
            lc.setDoNotCreateOpportunity(True);
            lc.setConvertedStatus(convertStatus.MasterLabel);

            leadConverts.add(lc);
        }
    }
    If(!leadConverts.isEmpty()) {
        List<Database.LeadConvertResult> lcr = Database.convertLead(leadConverts); //convert the leads
    }
}

No matter what I try the select from Accounts gives me a fatal error:

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

... And returns no records. I know there are records in the Account object for the Company name, I've even tried hard coding values into the query.

Answer 1

Change this:

Account.Name = ':accName'

to this:

Name = :accName

so your entire query would be:

Account accountId = [
      SELECT Id, Name FROM 
      Account WHERE Name = :accName AND Account_External_Id__c != null 
      LIMIT 1
  ];

saying = ':accName' is looking for Accounts with the literal value ":accName" since you wrapped it in single quotes. You don't need single quotes around SOQL bind variables.

You should not have this SOQL query within your for loop - please see Apex best practices. For every iteration of your loop, you will accrue a SOQL query - this will quickly hit governor limits in any type of bulk scenario.