function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MVJMVJ 

Return more then 1000 records for DataTable

I have a VF page that uses a custom controller to query the Account Object.  It uses the Apex:DataTable to display the data returned fromthe controller.  It works perfect when there is 1,000 rows or less since that is the max size of a list.

 

How can I change the code to return more data?  Can the Apex:DataTable go thru a Map?  Can it go thru an object?

 

What changes do I need to make to the code?

 

Thanks in advance.

 

VF Page:

 

 

<apex:page Controller="AllAccountSearchController" tabstyle="Account" sidebar="false">
<!-- Used to include sorttable static resource for applying client sorting functionality -->
    <script type="text/javascript" src="{!URLFOR($Resource.sorttable, 'sorttable.js')}"/>

 <!-- Filter criteria -->
 
 <apex:form id="Filter">
    <apex:pageBlock title="Filter">
        <apex:outputPanel id="searchpanel">
        <apex:pageMessages ></apex:pageMessages>
        </apex:outputPanel>
        <apex:pageBlockSection title="Criteria" columns="6" collapsible="false">
            <apex:panelGrid columns="6" id="theGrid">
                <apex:outputLabel style="font-weight:bold;"  value="Duns Number" for="DunsNumber"/>
                <apex:inputText id="searchDunsNumber" value="{!searchDunsNumber}" size="10"/>  
                <apex:outputLabel style="font-weight:bold;"  value="" for=""/>
                <apex:outputLabel style="font-weight:bold;"  value="" for=""/>
                <apex:commandButton action="{!search}" value="Search" id="searchBtn"/>
            </apex:panelGrid>
        </apex:pageBlockSection>
    </apex:pageBlock>
    </apex:form>

    
    <!-- Displays the Search Results section -->
    <apex:pageBlock title="Account(s)" >
    <apex:pageBlockSection title="Search Results" columns="1" collapsible="false">
         <apex:dataTable value="{!AllAccounts}" var="each" cellpadding="10" border="0" width="100%" columnsWidth="1000,200,200,200,200,200,200,200" style="vertical-align:top;word-wrap: break-word;" styleClass="sortable list">
            <apex:column headerValue="Account Name"><a href="/{!each.Id}">{!each.Name}</a>&nbsp;</apex:column>  
            <apex:column headerValue="Account Duns">{!each.Site}&nbsp;</apex:column>
            <apex:column headerValue="Address">{!each.BillingStreet}&nbsp;</apex:column>
            <apex:column headerValue="City">{!each.BillingCity}&nbsp;</apex:column>
            <apex:column headerValue="State">{!each.BillingState}&nbsp;</apex:column>
            <apex:column headerValue="Zip">{!each.BillingPostalCode}&nbsp;</apex:column>  
            <apex:column headerValue="Account Owner">{!each.Owner.Name}&nbsp;</apex:column>
            <apex:column headerValue="Owner email">{!each.Owner.email}&nbsp;</apex:column>
            <apex:column headerValue="Created Date">{!each.createddate}&nbsp;</apex:column>  
            <apex:column headerValue="Modified Date">{!each.LastModifiedDate}&nbsp;</apex:column>  
        </apex:dataTable>
    </apex:pageBlockSection>        
    </apex:PageBlock>
</apex:page>
 
Controller:
 
 
public class AllAccountSearchController {
    private Boolean searched = False;
    private String searchDunsNumber = removedashes(ApexPages.currentPage().getParameters().get('duns'));
    String strQuery = 'Select id, name, site, BillingStreet, BillingCity, BillingState, BillingPostalCode, createddate, LastModifiedDate, Owner.Name, Owner.email from Account where site like ' +'\'%'+searchDunsNumber +'%\'' + 'order by Name asc limit 10000';
    
    public string removedashes(string str){
        return str.replaceall( '-', '' );
    }
    

    public PageReference search() {
        
        searchDunsNumber = removedashes(searchDunsNumber);
        if (this.searchDunsNumber.length() > 3 && this.searchDunsNumber.length() < 10){
            //this.searched = True;
            strQuery = 'Select id, name, site, BillingStreet, BillingCity, BillingState, BillingPostalCode, createddate, LastModifiedDate, Owner.Name, Owner.email from Account where site like ' +'\'%'+searchDunsNumber +'%\'' + 'order by Name asc limit 10000';
        }
        else{
           ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.ERROR,'Please enter a DUNS Number with at least 4 characters and less then 10.');
           ApexPages.addMessage(myMsg);
        }
        PageReference pr = New PageReference('/apex/accountownersearch?DUNS='+removedashes(searchDunsNumber));
        pr.setRedirect(true);
        return pr;
    }


    public Account getAllAccounts(){
            Account listAccounts = new Account();
            //listAccounts = [Select id, name, site, BillingStreet, BillingCity, BillingState, BillingPostalCode, createddate, LastModifiedDate, Owner.Name, Owner.email from Account where site like'%123456789%' order by Name desc];
            //if (searched) listAccounts = Database.query(strQuery );
            if (this.searchDunsNumber.length() > 3 && this.searchDunsNumber.length() < 10){
                try{listAccounts = Database.query(strQuery);}
                catch(Exception ex){ApexPages.addMessages(ex);}
            }
            else{
               ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.ERROR,'Please enter a DUNS Number with at least 4 characters and less then 10.');
               ApexPages.addMessage(myMsg);
            }    
            return listAccounts ;
    }

    public String getsearchDunsNumber() { 
        return removedashes(searchDunsNumber);               
    }

    public void setsearchDunsNumber(String searchDunsNumber){ 
        this.searchDunsNumber = removedashes(searchDunsNumber.trim()); 
    }

}
 
 
 
 
 


Best Answer chosen by Admin (Salesforce Developers) 
stephanstephan
Appologies if the docs and release notes are a little unclear on this.
 
While the limit of 1000 items in collections was relaxed in Spring '10, there is still a limit of 1000 items that can be iterated over in VF pages. Generaly speaking, when displaying large collections to users, you should aim to paginate such large data sets. Part of this is just good UI design. The other part has to do with us needing to protect the infrastructure from having to render potentially huge pages. 
 
See the following section in the Visualforce developer guide for more on pagination:
 
http://www.salesforce.com/us/developer/docs/pages/index_Left.htm#StartTopic=Content/pages_controller_sosc_pagination.htm?SearchType=Stem 
 
...stephan 


All Answers
Rajesh ShahRajesh Shah
I am not sure if this helps but from Spring'10, there would be no limit on collections. So you won't have to worry about the more than 1000 records.


MVJMVJ
I am getting the error in a Spring10 sandbox.  The error I get is that the query returns 10,000 records but the list can only hold 1,000.  The actuall error is:
 
Collection size 10,000 exceeds maximum size of 1,000. 
 
 
 Also I attached the wrong Controller here is the correct one:
 
 
public class AllAccountSearchController {
    private Boolean searched = False;
    private String searchDunsNumber = removedashes(ApexPages.currentPage().getParameters().get('duns'));
    String strQuery = 'Select id, name, site, BillingStreet, BillingCity, BillingState, BillingPostalCode, createddate, LastModifiedDate, Owner.Name, Owner.email from Account where site like ' +'\'%'+searchDunsNumber +'%\'' + 'order by Name asc limit 10000';
    
    public string removedashes(string str){
        return str.replaceall( '-', '' );
    }
    

    public PageReference search() {
        
        searchDunsNumber = removedashes(searchDunsNumber);
        if (this.searchDunsNumber.length() > 3 && this.searchDunsNumber.length() < 10){
            //this.searched = True;
            strQuery = 'Select id, name, site, BillingStreet, BillingCity, BillingState, BillingPostalCode, createddate, LastModifiedDate, Owner.Name, Owner.email from Account where site like ' +'\'%'+searchDunsNumber +'%\'' + 'order by Name asc limit 10000';
        }
        else{
           ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.ERROR,'Please enter a DUNS Number with at least 4 characters and less then 10.');
           ApexPages.addMessage(myMsg);
        }
        PageReference pr = New PageReference('/apex/accountownersearch?DUNS='+removedashes(searchDunsNumber));
        pr.setRedirect(true);
        return pr;
    }


    public List<Account> getAllAccounts(){
            List<Account> listAccounts = new List<Account>();
            if (this.searchDunsNumber.length() > 3 && this.searchDunsNumber.length() < 10){
                try{listAccounts = Database.query(strQuery);}
                catch(Exception ex){ApexPages.addMessages(ex);}
            }
            else{
               ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.ERROR,'Please enter a DUNS Number with at least 4 characters and less then 10.');
               ApexPages.addMessage(myMsg);
            }    
            return listAccounts ;
    }

    public String getsearchDunsNumber() { 
        return removedashes(searchDunsNumber);               
    }

    public void setsearchDunsNumber(String searchDunsNumber){ 
        this.searchDunsNumber = removedashes(searchDunsNumber.trim()); 
    }

}
 
 
 


Rajesh ShahRajesh Shah
Well I have prerelease spring 10 dev org and I tried holding and inserting 1500 contacts in a list and it worked. Also tried querying all the records in a list and it gave no error. Not sure why you are getting the error. Here is the code if it is of any help:
 
List<Contact> con = new List<Contact>();
    
for(Integer i=0; i<1500; i++)
{
     Contact c = new Contact();
     c.Lastname = 'Test Contact ' + i;
     con.add(c);
}
insert con;
 
Contact[] con = [Select Id from Contact];
System.debug('Size: ' + con.size());
 
//Output: 
//Size: 1521 
 
 
 
Message Edited by Rajesh Shah on  02-02-2010 11:00 AM


narsavagepnarsavagep
Tried to find the 1000-record limit in writing, but couldn't.  Appears that the "apex:dataTable" doesn't allow more than 1000 records, even though the List collection is now unlimited.
 
Found this... might be able to create multiple pages:
	http://salesforcesource.blogspot.com/2008/09/how-to-support-paging-of-data-in-your.html 
 
Paul 


Rajesh ShahRajesh Shah
Ya ... that might be the reason, the data table not supporting more than 1000 records.
 
Regarding the 1000-record limit in writing, you can check the page no 126 of Salesforce Spring 10 Release Notes: 


narsavagepnarsavagep

Rajesh Shah wrote: 
Regarding the 1000-record limit in writing, you can check the page no 126 of Salesforce Spring 10 Release Notes:
 
 
Yes, that shows the limitation being removed from collections.  
But no mention that dataTable's are limited to 1000.
 


stephanstephan
Appologies if the docs and release notes are a little unclear on this.
 
While the limit of 1000 items in collections was relaxed in Spring '10, there is still a limit of 1000 items that can be iterated over in VF pages. Generaly speaking, when displaying large collections to users, you should aim to paginate such large data sets. Part of this is just good UI design. The other part has to do with us needing to protect the infrastructure from having to render potentially huge pages. 
 
See the following section in the Visualforce developer guide for more on pagination:
 
http://www.salesforce.com/us/developer/docs/pages/index_Left.htm#StartTopic=Content/pages_controller_sosc_pagination.htm?SearchType=Stem 
 
...stephan 
This was selected as the best answer
Rajesh ShahRajesh Shah
Thanks for clarifying that.