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;
}
}
7
  • Post your query
    – highfive
    Apr 18, 2016 at 7:46
  • I have updated my question with query
    – chaitanya
    Apr 18, 2016 at 7:57
  • Remove commas from your values and apply them in the query.
    – highfive
    Apr 18, 2016 at 8:09
  • kindly show me how to do it?
    – chaitanya
    Apr 18, 2016 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, 2016 at 8:21

1 Answer 1

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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .