1

When i try to search with 1000000 and 1500000 in Price Min and Max fields.the soql query is executing fine.but when i enter the values seperated with commas like SFmin=10,00,000 and SFmax=15,00,000.then i am getting error like

"System.QueryException: unexpected token: ,"

enter image description here

searchquery += 'and Initial_List_Price__c >= '+ILPminimum+' and      Initial_List_Price__c <= '+ILPmaximum+' ';

Below is the Visualforce Page Code.

<apex:page Standardcontroller="Opportunity" extensions="Condosearchcontroller"     showHeader="true"> 
<style type="text/css">
        body {background: #F3F3EC; padding-top: 5px}
    </style>
    <apex:form >
        <apex:pageBlock >
            <apex:pageBlockButtons >
                <apex:commandButton value="Find Units" action="{!search}"/>  
               <apex:commandButton value="Reset" action="{!Clear}"/>
               <apex:commandButton action="{!cancel}" value="Cancel" immediate="true" html-formnovalidate="formnovalidate" />
               </apex:pageBlockButtons>
               <apex:pageBlockSection columns="2" collapsible="false">
                     <apex:inputText value="{!beds}"  label="Beds" />
                     <apex:inputText value="{!SFminimum}"  label="Min" />
                     <apex:inputText value="{!SFmaximum}"  label="Max"/>
        </apex:pageBlockSection>
       <apex:outputLabel value="Price" for="first" />
      <apex:panelGrid columns="2">
                <apex:input value="{!ILPminimum}" html-placeholder="Min"          style="width:100px"/>
                <apex:input value="{!ILPmaximum}" html-placeholder="Max" style="width:100px"/>
                   </apex:panelGrid>
                      </apex:pageBlockSectionItem>
                            <apex:pageblockTable value="{!Cond}" var="a" id="table">  
                     <apex:column >  
                       <apex:outputlink  value="https://cs19.salesforce.com/{!a.id}">{!a.Name} </apex:outputlink>  
                           </apex:column>
         <apex:column value="{!a.Beds__c}"/>
         <apex:column value="{!a.Square_Footage__c}"/>
         <apex:column value="{!a.Initial_List_Price__c}"/> 
    </apex:pageBlockTable>
    </apex:pageBlock>
    </apex:form>
</apex:page>

Below is the apex Controller

public with sharing class Condosearchcontroller { 
public Condosearchcontroller() {
}
public list <Condo__c> Cond {get;set;}
public String LeadRecordId {get;set;}
public string beds {get;set;} 
public string squarefootage {get;set;}
public String SFminimum {get;set;}
public String SFmaximum {get;set;}
public string InitialListPrice {get;set;}
public String ILPminimum {get;set;}
public String ILPmaximum {get;set;}
public string Unitnamevf {get;set;}
public Boolean refreshPage {get; set;}
public Condosearchcontroller(ApexPages.StandardController controller) {}
  public List<SelectOption> getbedsOptions() {
    List<SelectOption> bedsoptions = new List<SelectOption>();
    bedsoptions.add(new SelectOption('','ALL'));
    bedsoptions.add(new SelectOption('0','0'));
    bedsoptions.add(new SelectOption('1','1'));
    bedsoptions.add(new SelectOption('2','2'));
    bedsoptions.add(new SelectOption('3','3'));
    return bedsoptions;
}
         public PageReference  Search(){
    string searchquery= 'select name,Beds__c,Square_Footage__c,Initial_List_Price__c,Status__c from Condo__c where name!=null and Status__c in (\'Available\',\'Unsold\') ';


           if (beds >= '0')
               searchquery+= ' and Beds__c = '+beds+'';

            if (SFminimum > '0' && SFmaximum > '0') 
               searchquery+= ' and Square_Footage__c >= '+SFminimum+' and Square_Footage__c <= '+SFmaximum+' ';


            if (ILPminimum >'0' && ILPmaximum > '0')
               searchquery += 'and Initial_List_Price__c >= '+ILPminimum+' and Initial_List_Price__c <= '+ILPmaximum+' ';


            if (beds >= '0' && SFminimum > '0' && SFmaximum > '0')
               searchquery+= ' and Beds__c = '+beds+' and Square_Footage__c >= '+SFminimum+' and Square_Footage__c <= '+SFmaximum+' '; 

             if (beds >= '0' && ILPminimum > '0' && ILPmaximum > '0')

                 searchquery += 'and Beds__c = '+beds+' and Initial_List_Price__c >= '+ILPminimum+' and Initial_List_Price__c <= '+ILPmaximum+' ';

              if(beds >= '0' && SFminimum > '0' && SFmaximum > '0' && ILPminimum > '0' && ILPmaximum > '0')

                 searchquery += ' and Beds__c = '+beds+' and Square_Footage__c >= '+SFminimum+' and Square_Footage__c <= '+SFmaximum+' and Initial_List_Price__c >= '+ILPminimum+' and Initial_List_Price__c <= '+ILPmaximum+' ';

             if(beds >= '0' && SFminimum > '0')

                  searchquery += ' and Beds__c = '+beds+' and Square_Footage__c  >= '+SFminimum+' ';

              if(beds >= '0' && SFmaximum > '0')

                  searchquery += ' and Beds__c = '+beds+' and Square_Footage__c  <= '+SFmaximum+' ';

              if(beds >= '0' && ILPminimum > '0')

                 searchquery += ' and Beds__c = '+beds+' and Initial_List_Price__c  >= '+ILPminimum+' ';

               if(beds >= '0' && ILPmaximum > '0')

                 searchquery += ' and Beds__c = '+beds+' and Initial_List_Price__c  <= '+ILPmaximum+' ';

               if(SFminimum.isNumeric() && string.isempty(SFmaximum))

                  searchquery += ' and Square_Footage__c >= '+SFminimum+' ';

                 if(string.isempty(SFminimum) && SFmaximum.isNumeric())

                  searchquery += ' and Square_Footage__c  <= '+SFmaximum+' ';

                 if( ILPminimum > '0')

                   searchquery += ' and Initial_List_Price__c  >= '+ILPminimum+' ';

                 if(ILPmaximum > '0')

                 searchquery += ' and Initial_List_Price__c  <= '+ILPmaximum+' ';

       if (SFminimum.isAlpha() || SFmaximum.isAlpha() || ILPminimum.isAlpha() || ILPmaximum.isAlpha()){
       ApexPages.addmessage(new ApexPages.message(ApexPages.severity.FATAL,'Please enter Numerical values for SquareFootage and Price Fields'));
       } else
     cond = Database.query(searchquery);
       return null;
}
}
  • Post your query – highfive Apr 18 '16 at 7:46
  • I have updated my question with query – chaitanya Apr 18 '16 at 7:57
  • Remove commas from your values and apply them in the query. – highfive Apr 18 '16 at 8:09
  • kindly show me how to do it? – chaitanya Apr 18 '16 at 8:16
  • Basically you can't set formatted fields in query, better you post your VF code and controller to show how you bind these UI values in the back end – highfive Apr 18 '16 at 8:21
1

Just do a replaceAll before adding string variables to your SOQL statement

e.g. ILPminimum = ILPminimum.replaceAll('\,','');

That way your SOQL should be properly formatted.

  • 1
    Richard - I think OP wants to remove commas - typo? in your answer. shouldn't it be ...replaceAll('\\,','') ? – cropredy Apr 19 '16 at 2:10
  • You are correct @cropredy - I will update my answer – Richard Durrant Apr 19 '16 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.