Too Many SOQL Queries: 101

Question

I am getting the below error message when trying to add a contact to a record. Also, anytime I try to amend class I get error message stating can't alter metadata in active org? Any help is appreciated

Too many SOQL queries: 101 Error is in expression '{!createAtt}' in component in page createpcmactivity: Class.pcmTimeControllerExtension.searchCon: line 113, column 1 Class.pcmTimeControllerExtension.createAtt: line 71, column 1 An unexpected error has occurred. Your development organization has been notified.

Apex Class:

 public with sharing class pcmTimeControllerExtension {
      public String attIdChosen {get; set;} 
      public String conIdChosen {get; set;} 
      public List<PCM_Activity_Attendee__c> attendees {get; set;}
      public List<Contact> allcontacts {get; set;}
      public Contact search {get; set;}
      private ApexPages.StandardController stdCtrl {get; set;}
      private PCM_Time_Record__c pcmAct {get; set;}
      public List<Account> ownerAccs {get; set;}

    public pcmTimeControllerExtension(ApexPages.StandardController std) {
      stdCtrl=std;          
      pcmAct = (PCM_Time_Record__c)stdCtrl.getRecord();
    search = new contact(ownerid = UserInfo.getUserId());
      setupAttendees();
      setupAllCon();
    }

// *****************************************************************************************************//    
// This determines the format/columns of the Attendees added table aspect of the page
// *****************************************************************************************************//

     private void setupAttendees()
     {
      attendees=[select id, contact__c, account__c, contact__r.ownerid, notes__c, one_to_one__c 
      from PCM_Activity_Attendee__c 
      where PCM_Activity__c=:pcmAct.Id];
     } 

// *****************************************************************************************************//
// This determines the format/columns of the Search box of contacts - leave limit 0 to stop it being 
// populated with nonsense when loading / clearing data
// *****************************************************************************************************//

     private void setupAllCon()
     {
         allcontacts=[select id, name, Contact.PCM_Contact_Type__c , account.ownerid, 
                account.target_account__c 
                 from contact where Id not in (select contact__c from PCM_Activity_Attendee__c 
                 where PCM_Activity__c=:pcmAct.Id)  order by account.name asc, lastname, firstname
                      limit 0];
     }

// *****************************************************************************************************//
// Delete attendees function setup
// *****************************************************************************************************//   
    public PageReference delAtt()
    {
     PCM_Activity_Attendee__c toDel=new PCM_Activity_Attendee__c(id=attIdChosen);

     delete todel;

     setupAttendees();
     setupAllCon();

     return null;
    }

// *****************************************************************************************************//    
// Adds an attendee record and appends to the master activity record
// *****************************************************************************************************//

    public PageReference createAtt()

    {
     PCM_Activity_Attendee__c toCreate=new PCM_Activity_Attendee__c(Contact__c=conIdChosen, PCM_Activity__c=pcmAct.Id);

     insert toCreate;

     setupAttendees();
     searchCon();

     return null;
    }

// *****************************************************************************************************//    
// Search contacts function - two paths depending on whether Contact Type is or is not populated.
// Uses like and % to perform a fairly fuzzy search.
// Limit restricts the results to X amount
// *****************************************************************************************************//

    public PageReference searchCon()
    {

    // if statement to search either own or all accounts      
    if (Search.dislike_is__c == TRUE)
    {
      ownerAccs = [Select ownerid from account where ownerid=:search.ownerid];
    }
    else
    {
      ownerAccs = [Select ownerid from account];
    }

    // Prepare a list to add IDs to
    List<Id> AccIds = new List<Id>();
    // Populating IDs to list
    for(Account acc : ownerAccs)
    {
      AccIds.add(acc.ownerId);
    }

    // Query list of already added attendees to remove from search
    List<PCM_Activity_Attendee__c> AddedAttendees = [select contact__c from PCM_Activity_Attendee__c where PCM_Activity__c=:stdCtrl.getId()];
    // Prepare a list to add IDs to
    List<Id> ConIDs = new List<Id>();
    // Populating IDs to list
    for(PCM_Activity_Attendee__c accatt : AddedAttendees)
    {
      ConIds.add(accatt.contact__c);
    }

        allcontacts=[select id, firstname, lastname, Contact.PCM_Contact_Type__c , account.name, account.ownerid, account.shippingpostalcode, account.target_account__c 
                           from contact 
                           where
                           Id not in :ConIDs
                           and lastname like :'%' + search.lastname + '%'
                           and account.name like :'%' + search.department + '%'           
                           and account.shippingpostalcode like :'%' + search.MailingPostalCode + '%'
                           and account.ownerid in :AccIds
                           and Account.RecordTypeId = '01230000000Y77V'
                           order by account.name asc, lastname
                           limit 50];
        return null;
    }

// *****************************************************************************************************// 
// Clear search panel function    
// *****************************************************************************************************//

    public PageReference clearSearchCon()
    {
        search = new contact();
        setupAllCon();
        return null;
    }

// *****************************************************************************************************//    
// Save activity - inserts in instance of no record existing, updates if not.     
// *****************************************************************************************************//

    public PageReference saveAct()
    {
     if(pcmAct.Id == null)
     {
         insert pcmAct;
     }
     setupAttendees();
     setupAllCon();     
     return null;
    }   

// *****************************************************************************************************//
// Save attendees - checks to see whether this is a 121 type of call and presets all 121 Markers to True if so.
// *****************************************************************************************************//

    public pagereference mySave()
    {
        pcmAct = (PCM_Time_Record__c)stdCtrl.getRecord();
        if(pcmAct.type__c == '121 Call')
        {
            for(PCM_Activity_Attendee__c a:attendees)
            {
               a.one_to_one__c = true;
            }
        }
        update attendees;
        pagereference savepage = stdCtrl.save();
        return savepage;
    }

// *****************************************************************************************************//   
// New contact function - inserts a link to create a new contact, popping a new window    
// *****************************************************************************************************//

    public PageReference newContact()
    {
           PageReference pageRef = new PageReference('/003/e?');
           return pageRef;  
    }


}

This is my trigger code

trigger setAccountId on PCM_Activity_Attendee__c (before insert) 
{ 
  for (PCM_Activity_Attendee__c newA: Trigger.new) 
  {  
    if(newA.Account_Lookup__c == null)
    {   
        contact c = [select id, accountid from contact where id = :newA.Contact__c]; 
        list<account> a = new list<account>([select id from account where id = :c.accountid]); 
        if(a.size()>0) newA.Account_lookup__c = a[0].id; 
    }
  }
} 

Answer 1

Is it a SOQL in the loop soldier???

Based on the given trigger code the error ocurres because of use SOQL inside the for-loop:

for (PCM_Activity_Attendee__c newA: Trigger.new) { 
    if(newA.Account_Lookup__c == null) { 
        // Here is your bad code
        contact c = [select id, accountid from contact where id = :newA.Contact__c]; 

Every developer should avoid using a SOQL query inside the loop!

Like this:

trigger setAccountId on PCM_Activity_Attendee__c (before insert) { 

    Set<Id> conids = new Set<Id>();
    for (PCM_Activity_Attendee__c newA: Trigger.new) { 
        if(newA.Account_Lookup__c == null) { 
            ids.add(newA.Contact__c);
        }
    }

    Set<Id> accids = new Set<Id>()
    for(contact c : [select id, accountid from contact where id = :conids]){
        accids.add(c.accountid);
    } 

    for(Account a : [select id from account where id = :accids]){
        if(a.size()>0) newA.Account_lookup__c = a[0].id; 
    }
} 

Answer 2

Issue in your trigger code. If you look into your trigger code soql is inside for loop that is getting fire for each loop

Suppose If you are inserting more than 100 PCM_Activity_Attendee__c records then your for loop will be iterate more than 100.

So you will hit the salesforce governor limits i.e. you can use 100 soql statement for each transaction

Below is your updated trigger code. this will be resolved your 101 error (Please read the comments and try to understand)

trigger setAccountId on PCM_Activity_Attendee__c (before insert) 
{ 
    //set to store all the PCM_Activity_Attendee__c's Contact__c fields value
    set<Id> setAccountIds = new set<Id>();
    //iterate over trigger.new list and collect all contact__c field value in set
    for (PCM_Activity_Attendee__c newA: Trigger.new) 
    {
        if(newA.Contact__c != null)
        {
            setAccountIds.add(newA.Contact__c);
        }
    }
    //now query contact based on the set and store into map. 
    //SO in map contact Id will be key and contact record will be value
    map<Id, Contact> mapIdToContact = new map<Id, Contact>([SELECT Id, AccountId 
                                                              FROM Contact 
                                                            WHERE Id IN: setAccountIds]);
    //again iterate over trigger.new list 
    for(PCM_Activity_Attendee__c newA: Trigger.new)
    {
        //check field is not null and if contact Id is contain in map 
        if(newA.Contact__c != null && mapIdToContact.containsKey(newA.Contact__c))
        {
            //if contain then get the contact's AccountId from map and assign to Account_lookup__c field
            newA.Account_lookup__c = mapIdToContact.get(newA.Contact__c).AccountId ;
        }
    }
  }
}