+ Start a Discussion
zjlgzjlg 

The problem is :Too many SOQL queries: 101

public class AllNewWelcomeLetter{

List<mbMemberAC__c> memberac;

public AllNewWelcomeLetter() {

}

List<Attachment> att;

public PageReference savePdf() {

memberac=[select id,MEnrollmentDate__c,Member_A_C_Client_name__c,Member_Chinese_Name__c,PRIVE_ADDR1__c,PRIVE_ADDR2__c,PRIVE_ADDR3__c,PRIVE_ADDR4__c,name from mbMemberAC__c where WelcomeLetterSenton__c=:null and MEnrollmentDate__c!=:null and MemberStatus__c=:'Active' ];

if(memberac.size()>0){

for(mbMemberAC__c mem:memberac){
//search the pdf name from attachment and judge if the old name is same as the new pdf name,pdf in present memAC is unique
att=[select Name from Attachment where ParentId =:mem.id and name=:mem.Member_A_C_Client_name__c+'.pdf'];

if(att.size()==0){

PageReference pdf = Page.WelcomeLetterForEach;

pdf.getParameters().put('id',mem.id);

Attachment attach = new Attachment();

// the contents of the attachment from the pdf
Blob body;

try {
// returns the output of the page as a PDF
body = pdf.getContent();

// need to pass unit test -- current bug
} catch (VisualforceException e) {
body = Blob.valueOf('Some Text');
}

attach.Body = body;
attach.Name = mem.Member_A_C_Client_name__c+'.pdf';
attach.IsPrivate = false;
// attach the pdf to the account
attach.ParentId =mem.id;
insert attach;

mem.WelcomeLetterSenton__c=date.today();
update mem;
}
}
}else{
ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.FATAL, '(error):The object memshipAC is null!');
ApexPages.addMessage(myMsg);
return page.WelcomeLetterPage;

}

return page.ListMembershipAC;
}

}

if i change the number 100 into 10 or less in method savePDF() , then it's ok! otherwise ,there will be the error message occurred,can you tell me how can i solve it. thank you very much!! 

 
Best Answer chosen by Admin (Salesforce Developers) 
nick1505nick1505

Hi,

 

You can use the following code:

 

public class AllNewWelcomeLetter
{
Map<Id, mbMemberAC__c> mapmemberac = new Map<Id, mbMemberAC__c>();
Map<Id, Attachment> mapAttachment = new Map<Id, Attachement>();
public AllNewWelcomeLetter()
{
}

public PageReference savePdf()
{

for(mbMemberAC__c obj: [select id,MEnrollmentDate__c,Member_A_C_Client_name__c,Member_Chinese_Name__c,PRIVE_ADDR1__c,PRIVE_ADDR2__c,PRIVE_ADDR3__c,PRIVE_ADDR4__c,name from mbMemberAC__c where WelcomeLetterSenton__c=:null and MEnrollmentDate__c!=:null and MemberStatus__c=:'Active'])
{
mapmemberac.put(obj.Id, obj);
}


for(Attachment obj: [select Name from Attachment where ParentId IN: mapmemberac.keyset()])
{
if(mapmemberac != null && mapmemberac.containskey(obj.ParentId) && mapmemberac.get(obj.ParentId) != null && obj.Name == mapmemberac.get(obj.ParentId).mem.Member_A_C_Client_name__c+'.pdf')
{
mapAttachment.put(obj.ParentId, obj);
}
}

if(mapmemberac != null && mapmemberac.size()>0)
{

for(mbMemberAC__c mem:mapmemberac.keyset())
{
//search the pdf name from attachment and judge if the old name is same as the new pdf name,pdf in present memAC is unique

if(!mapAttachment.containskey(mem.id))
{

PageReference pdf = Page.WelcomeLetterForEach;

pdf.getParameters().put('id',mem.id);

Attachment attach = new Attachment();

// the contents of the attachment from the pdf
Blob body;

try
{
// returns the output of the page as a PDF
body = pdf.getContent();

// need to pass unit test -- current bug
}
catch (VisualforceException e)
{
body = Blob.valueOf('Some Text');
}

attach.Body = body;
attach.Name = mem.Member_A_C_Client_name__c+'.pdf';
attach.IsPrivate = false;
// attach the pdf to the account
attach.ParentId =mem.id;
insert attach;

mem.WelcomeLetterSenton__c=date.today();
update mem;
}
}
}
else
{
ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.FATAL, '(error):The object memshipAC is null!');
ApexPages.addMessage(myMsg);
return page.WelcomeLetterPage;
}
return page.ListMembershipAC;
}
}

 

All Answers

AmitSahuAmitSahu

att=[select Name from Attachment where ParentId =:mem.id and name=:mem.Member_A_C_Client_name__c+'.pdf'];

 

The above statement needs to be put out of the loop. The query is creating issue in the loop. Just create a list of attachment out side of the loop and iterate over the list in the for loop..

zjlgzjlg

Hi j020,

Thanks for your reply, but i want implement the logic that if the Attachment name is exist under the memberAC, then it will not save any more, so ,as you say, i put the list out of the loop, i wonder how can i implement the logic...can you give me any opinion? thanks and hope you reply for me.

Rahul SharmaRahul Sharma

Try using a maps.

- Prepare map of Attachment with required key's.

- And inside your main for loop just fetch the requied values from map.

zjlgzjlg

Hi Rahul,

I think the opinion you afford for me is very interesting, but can you give me a specific explanation? or give me an example, by the way, do you know what's the major cause of the error which is 'Too many DML statements: 151' ,thanks a lot!! 

BDArnzBDArnz

Salesforce is a multi-tenant environment.  All users are essentially querying the same resources and having a single query inside a loop causes the code to initiate multiple queries to the servers.  SF implements govenor limits on the number of queries to keep from dragging down resources that others need.

 

As said previously, you need to create a map variable that includes the ids off all the records that you would otherwise query individually.  Once the map is ready, issue a single query for all records whose ID is in the map.  You then iterate over the results to deal with the individual values as necessary.  

 

This processes a single query on the server instead of hundreds.  Much less resource consumption.

zjlgzjlg

Hi BDArnz,

Thank you for your reply,i don't know how to use map into my apex class,can you give me a sample?

nick1505nick1505

Hi,

 

You can use the following code:

 

public class AllNewWelcomeLetter
{
Map<Id, mbMemberAC__c> mapmemberac = new Map<Id, mbMemberAC__c>();
Map<Id, Attachment> mapAttachment = new Map<Id, Attachement>();
public AllNewWelcomeLetter()
{
}

public PageReference savePdf()
{

for(mbMemberAC__c obj: [select id,MEnrollmentDate__c,Member_A_C_Client_name__c,Member_Chinese_Name__c,PRIVE_ADDR1__c,PRIVE_ADDR2__c,PRIVE_ADDR3__c,PRIVE_ADDR4__c,name from mbMemberAC__c where WelcomeLetterSenton__c=:null and MEnrollmentDate__c!=:null and MemberStatus__c=:'Active'])
{
mapmemberac.put(obj.Id, obj);
}


for(Attachment obj: [select Name from Attachment where ParentId IN: mapmemberac.keyset()])
{
if(mapmemberac != null && mapmemberac.containskey(obj.ParentId) && mapmemberac.get(obj.ParentId) != null && obj.Name == mapmemberac.get(obj.ParentId).mem.Member_A_C_Client_name__c+'.pdf')
{
mapAttachment.put(obj.ParentId, obj);
}
}

if(mapmemberac != null && mapmemberac.size()>0)
{

for(mbMemberAC__c mem:mapmemberac.keyset())
{
//search the pdf name from attachment and judge if the old name is same as the new pdf name,pdf in present memAC is unique

if(!mapAttachment.containskey(mem.id))
{

PageReference pdf = Page.WelcomeLetterForEach;

pdf.getParameters().put('id',mem.id);

Attachment attach = new Attachment();

// the contents of the attachment from the pdf
Blob body;

try
{
// returns the output of the page as a PDF
body = pdf.getContent();

// need to pass unit test -- current bug
}
catch (VisualforceException e)
{
body = Blob.valueOf('Some Text');
}

attach.Body = body;
attach.Name = mem.Member_A_C_Client_name__c+'.pdf';
attach.IsPrivate = false;
// attach the pdf to the account
attach.ParentId =mem.id;
insert attach;

mem.WelcomeLetterSenton__c=date.today();
update mem;
}
}
}
else
{
ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.FATAL, '(error):The object memshipAC is null!');
ApexPages.addMessage(myMsg);
return page.WelcomeLetterPage;
}
return page.ListMembershipAC;
}
}

 

This was selected as the best answer