Dynamic Visualforce Search Page

Question

I'm running into two problems on my search page. Nothing is happening when I click the 'Reset' button and I am getting this error when I search using a date field:

System.QueryException: expecting a colon, found '(' Error is in expression '{!searchEng}' in component in page engagement_search: Class.Engagements_Search_Controller.searchEngagementss: line 94, column 1 Class.Engagements_Search_Controller.searchEng: line 26, column 1 Class.Engagements_Search_Controller.searchEngagementss: line 94, column 1 Class.Engagements_Search_Controller.searchEng: line 26, column 1

Here is my code:

   public with sharing class Engagements_Search_Controller {
public Engagements__c eng{get;set;}
public List<Engagements__c> engagementList {get;set;}

// create a list of strings to hold the conditions
List<string> conditions = new List<string>();
private integer totalRecs = 0;
private integer OffsetSize = 0;
private integer LimitSize= 25;
public integer total_size; //used to show user the total size of the list
public Integer totalPages{get;set;}


public Engagements_Search_Controller(){
system.debug('==>Engagements_Search_Controller  is calling==>');
 eng = new Engagements__c();
 //engagementList  = new List<refount>();
}

public void searchEng(){
totalRecs = 0;
OffsetSize = 0;
if(engagementList !=null && engagementList.size()>0){
 engagementList=null;
}
searchEngagementss ();
conditions.clear();
}


public Void searchEngagementss(){

System.debug('Total Records is ==>'+totalRecs);
System.debug('OffsetSize is ==>'+OffsetSize);

if(engagementList != null && !engagementList.isEmpty()){
  engagementList.clear();
}
 String strQuery ='SELECT Name,Pending_Go_No_Go_Canceled__c,Additional_SIC_Sector__c, Additional_SIC_Industry__c,What_We_Found__c,What_We_Did__c, SIC_Final__c, Unit_Name__c, Company_Name__c, Ultimate_Parent_Company__c, Country__c, BR_Start_Date__c, Project_Start_Date__c,Scope_of_BR_Area__c,Scope_of_BR_Theme__c  From Engagements__c ';

 if(eng.Name !=null && eng.Name !=''){
   conditions.add('Name Like \'%' +eng.Name +'%\' ');
 }
 if(eng.Additional_SIC_Sector__c !=null && eng.Additional_SIC_Sector__c !=''){
    conditions.add('Additional_SIC_Sector__c Includes (\'' +eng.Additional_SIC_Sector__c +'\') ');
 }
 if(eng.Additional_SIC_Industry__c !=null && eng.Additional_SIC_Industry__c !=''){
    conditions.add('Additional_SIC_Industry__c Includes (\'' +eng.Additional_SIC_Industry__c +'\') ');
 }
  if(eng.Scope_of_BR_Area__c !=null && eng.Scope_of_BR_Area__c !=''){
    conditions.add('Scope_of_BR_Area__c Includes (\'' +eng.Scope_of_BR_Area__c +'\') ');
 }
 if(eng.Scope_of_BR_Theme__c !=null && eng.Scope_of_BR_Theme__c !=''){
    conditions.add('Scope_of_BR_Theme__c Includes (\'' +eng.Scope_of_BR_Theme__c +'\') ');
 }
 if(eng.SIC_Final__c !=null && eng.SIC_Final__c !=''){
   conditions.add('SIC_Final__c Like\'%' +eng.SIC_Final__c +'%\' ');
 }

if(eng.Unit_Name__c !=null && eng.Unit_Name__c !=''){
   conditions.add('Unit_Name__c Like\'%' +eng.Unit_Name__c +'%\' ');
 }
if(eng.Company_Name__c !=null && eng.Company_Name__c !=''){
   conditions.add('Company_Name__c Like\'%' +eng.Company_Name__c +'%\' ');
 }
 if(eng.Ultimate_Parent_Company__c !=null && eng.Ultimate_Parent_Company__c !=''){
   conditions.add('Ultimate_Parent_Company__c Like\'%' +eng.Ultimate_Parent_Company__c +'%\' ');
 }
 if(eng.Country__c !=null && eng.Country__c !=''){
   conditions.add('Country__c Like\'%' +eng.Country__c +'%\' ');
 }

 if(eng.BR_Start_Date__c !=null){
    conditions.add('BR_Start_Date__c Like (\'' +eng.BR_Start_Date__c +'\') ');
 }
 if(eng.Project_Start_Date__c !=null){
    conditions.add('Project_Start_Date__c Like (\'' +eng.Project_Start_Date__c +'\') ');
 }
if(eng.What_We_Found__c !=null && eng.What_We_Found__c !=''){
   conditions.add('What_We_Found__c Like\'%' +eng.What_We_Found__c +'%\' ');
 }
 if(eng.What_We_Did__c !=null && eng.What_We_Did__c !=''){
   conditions.add('What_We_Did__c Like\'%' +eng.What_We_Did__c +'%\' ');
 }



  if (conditions.size() > 0) {
   strQuery += '  WHERE ' + conditions[0];
   for (Integer i = 1; i < conditions.size(); i++)
            strQuery += '  AND ' + conditions[i];
  }
 if(totalRecs !=null && totalRecs ==0){
    List<Engagements__c> engTemp = Database.query(strQuery);
    totalRecs = (engTemp !=null &&engTemp.size()>0)?engTemp.size():0;
 }

 system.debug('strQuery ==>'+strQuery );
 // add sort and limits at the end  
  strQuery += ' ORDER BY Name  ASC, Unit_Name__c DESC LIMIT :LimitSize OFFSET :OffsetSize';

  engagementList  =Database.query(strQuery);



  //conditions.clear();
  //return engagementList.size();
}


public void FirstPage()
{
OffsetSize = 0;
searchEngagementss();
}
public void previous()
{
OffsetSize = (OffsetSize-LimitSize);
searchEngagementss();
}
public void next()
{
OffsetSize = OffsetSize + LimitSize;
searchEngagementss();
}
public void LastPage()
{
OffsetSize = totalrecs - math.mod(totalRecs,LimitSize);
searchEngagementss();
}
public boolean getprev()
{

if(OffsetSize == 0){

return true;
}
else {

return false;
}
}
public boolean getnxt()
{
if((OffsetSize + LimitSize) > totalRecs){

return true;
}
else {

return false;
}
}

 public Integer getTotal_size() {
      return totalRecs;
   }

   public Integer getPageNumber() {
      return OffsetSize/LimitSize + 1;
   }

   public Integer getTotalPages() {
      if (math.mod(totalRecs, LimitSize) > 0) {
         return totalRecs/LimitSize + 1;
      } else {
         return (totalRecs/LimitSize);
      }
   }


}

VF:

<apex:page controller="Engagements_Search_Controller" action="{!searchEng}" >
<script type="text/javascript">
    window.onload=function() {
    // document.getElementById("{!$Component.thePb.thepbs.engName}").focus();
    }   
</script>
 <apex:form >
  <apex:pageBlock id="thePb" title="Engagements Details To Search">
   <apex:pageblockSection id="thepbs" columns="3" collapsible="true">
    <apex:inputField value="{!eng.Name}" required="false" id="engName"/>
    <apex:inputfield value="{!eng.Company_Name__c}"/>
    <apex:inputfield value="{!eng.Ultimate_Parent_Company__c}"/>
    <apex:inputfield value="{!eng.Unit_Name__c}"/>
    <apex:inputfield value="{!eng.Country__c}"/>
    <apex:inputfield value="{!eng.SIC_Final__c}"/>
   </apex:pageblockSection>  
   <apex:pageblockSection >
     <apex:inputField value="{!eng.Scope_of_BR_Area__c}"/>
     <apex:inputfield value="{!eng.Scope_of_BR_Theme__c}"/>
     <apex:inputField value="{!eng.Additional_SIC_Sector__c}"/>
     <apex:inputfield value="{!eng.Additional_SIC_Industry__c}"/>
     <apex:inputfield value="{!eng.BR_Start_Date__c}"/>
     <apex:inputfield value="{!eng.Project_Start_Date__c}"/>
     <apex:inputfield value="{!eng.What_We_Found__c}" style="width:85%;"/>
     <apex:inputfield value="{!eng.What_We_Did__c}" style="width:85%;"/>
   </apex:pageblockSection>
   <apex:pageblockButtons location="bottom">
      <apex:commandButton value="Search"  action="{!searchEng}"/>
      <apex:commandButton value="Reset" status="idStatus" onclick="this.form.reset();return false;" />
     </apex:pageblockButtons>  
  </apex:pageBlock>

   <apex:pageBlock title="Engagements Details" id="noRec" rendered="{! IF( engagementList != null && engagementList.size ==0 , true, false)}" >
  <apex:outputPanel >
    <h1>No Records Found </h1>
</apex:outputPanel>
  </apex:pageBlock>


  <apex:pageBlock title="Engagements Details (Total List Size: {!total_size})" id="details" rendered="{! IF( engagementList != null && engagementList.size >0, true, false)}" >

   <apex:pageBlockTable value="{!engagementList}" var="a">
   <apex:column headerValue="Engagements Name">
    <apex:outputLink target="_blank" value="/{!a.id}">{!a.Name}</apex:outputLink> 
   </apex:column>   
    <!--  If you want facet style you can add like this.
   <apex:column >
     <apex:facet name="header">Link Name</apex:facet>
     <apex:outputLink target="_blank" value="/{!a.id}">{!a.Name}</apex:outputLink> 
    </apex:column>
    -->

    <apex:column value="{!a.Pending_Go_No_Go_Canceled__c}" headerValue="Pending/Go/No-Go/Canceled"/>
    <apex:column value="{!a.Additional_SIC_Sector__c}" headerValue="Additional SIC Sector"/>
    <apex:column value="{!a.Additional_SIC_Industry__c}" headerValue="Additional SIC Industry"/>
    <apex:column value="{!a.SIC_Final__c}" headerValue="SIC Final"/>
    <apex:column value="{!a.Unit_Name__c}" headerValue="Unit Name"/> 
    <apex:column value="{!a.Company_Name__c}" headerValue="Company Name"/>
    <apex:column value="{!a.Ultimate_Parent_Company__c}" headerValue="Ultimate Parent Company"/> 
    <apex:column value="{!a.Country__c}" headerValue="Country"/> 
    <apex:column value="{!a.BR_Start_Date__c}" headerValue="BR Start Date"/> 
    <apex:column value="{!a.Project_Start_Date__c}" headerValue="Project Start Date"/>


   </apex:pageBlockTable>

    <apex:pageblockButtons >
        <apex:commandButton value="First Page" rerender="details" action="{!FirstPage}" disabled="{!prev}"/>
        <apex:commandButton value="Previous" rerender="details" action="{!previous}" disabled="{!prev}"/>
        <apex:commandButton value="Next" rerender="details" action="{!next}" disabled="{!nxt}"/>
        <apex:commandButton value="Last Page" rerender="details" action="{!LastPage}" disabled="{!nxt}"/>
        <apex:facet name="footer">Showing Page # {!pageNumber} of {!totalPages}</apex:facet>

    </apex:pageblockButtons>

  </apex:pageBlock>

 </apex:form>
</apex:page>

Answer 1

For your reset issue, check both answers here: Form reset after clicking a button?.

For the date issue, Vigneshwaran is right that you can't use 'LIKE', but if you are putting them in a string like that, you will also need to format them. Something like: eng.Project_Start_Date__c.format('yyyy-MM-dd'). There are other solution to this, but this one is most similar to what you already have, IMO.

You also have large issues with SOQL injection vulnerabilities. There is plenty of reading on it with a quick Google search. https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_security_tips_soql_injection.htm https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_security_tips_soql_injection.htm