System.QueryException: error when button click second time?

Question

vf page:

<apex:page showHeader="false" sidebar="False" controller="textInputsCon" applyHtmlTag="true" >
    <apex:form >
      Input Text1 <apex:inputText value="{!inputText1}"/><br/>   
      <apex:selectList id="inputText3" value="{!inputText3}" size="1">
            <apex:selectOption itemValue="Name" itemLabel="Quote Number"/>
            <apex:selectOption itemValue="Quote_Line_Item__r.Quote1__r.From1__c" itemLabel="from"/>
             <apex:selectOption itemValue="Null" itemLabel="None"/>
        </apex:selectList> <br/>
            Input Text2 <apex:inputText value="{!inputText2}"/><br/>
      <apex:selectList id="inputText4" value="{!inputText4}" size="1">
            <apex:selectOption itemValue="name" itemLabel="Quote Number"/>
                 <apex:selectOption itemValue="From1__c" itemLabel="from"/>
            <apex:selectOption itemValue="From1__c" itemLabel="None"/>
        </apex:selectList>        
      <apex:selectList id="operator" value="{!operator}" size="1"><br/>
            <apex:selectOption itemValue="=" itemLabel="Equal"/>
            <apex:selectOption itemValue="!=" itemLabel="Not equal to"/>
            <apex:selectOption itemValue="<=" itemLabel="Less than"/>
        </apex:selectList> 
        <apex:commandButton value="list1" action="{!showlist1}"/>
         <apex:pageBlock >
         <apex:pageBlockSection columns="1"> 
               <apex:pageBlockTable value="{!quo1}" var="q1">
                <apex:column value="{!q1.name}"/>
                <apex:column value="{!q1.Quote_Line_Item__c}"/>
                </apex:pageBlockTable>   
              </apex:pageBlockSection>            
        </apex:pageBlock>
    </apex:form>
    </apex:page>

controller:

public with sharing class textInputsCon {
     public String inputText1{get;set;} // input text1 value  from vf
     public String inputText2{get;set;} // input text2 value  from vf  
      public String inputText3{get;set;} // input text1 value  from vf
     public String inputText4{get;set;}
     Public string operator{get;set;}
    Public list<batch__c> quo1{get;set;}
     string query1='select name,Quote_Line_Item__c,Quote_Line_Item__r.Quote1__r.Quote_Number_New__c,Quote_Line_Item__r.Quote1__c,Quote_Line_Item__r.Quote1__r.from1__c  from  batch__c ';
     public void showlist1(){
      if(inputText1<>''){
         query1 += 'WHERE ' + inputText3 + ' ' + operator + ' : inputText1 ' ;  } 
     if(inputText2<>''){
       query1 +=' and  '+ inputText4 + ' ' + operator + ' : inputText2';    } 
          quo1 = database.query(query1);
     }
    }

IT IS SHOW the results when kept the data for the first time :

when i edit the textbox and and again click on the list1 it is showing this error :

System.QueryException: unexpected token: 'WHERE' Error is in expression '{!showlist1}' in component in page test

Class.textInputsCon.showlist1: line 19, column 1

can any body say me how to get rid of this error and .when i refreshed also it is showing the values in the textbox and same error i am getting .when i closed the window and open the same window .At the first time it is showing the results and i edit the text box and click on list it is throwing this error ?

Answer 1

You are initializing the query only once (it's in the body of the class), so your first query looks like:

select name, Quote_Line_Item__c, Quote_Line_Item__r.Quote1__r.Quote_Number_New__c,
Quote_Line_Item__r.Quote1__c, Quote_Line_Item__r.Quote1__r.from1__c  
from  batch__c 
WHERE x = :inputText1

(Line breaks for readability)

Then, when you click the button again, it is adding the additional text:

select name, Quote_Line_Item__c, Quote_Line_Item__r.Quote1__r.Quote_Number_New__c,
Quote_Line_Item__r.Quote1__c, Quote_Line_Item__r.Quote1__r.from1__c  
from  batch__c 
WHERE x = :inputText1
WHERE x = :inputText1

This will cause the error.

To resolve, move the String query line into the body of the function.

Also, you have another error if you put only a value in inputText2 without inputText1.

I would consider fixing it like this:

String query = 'select ...';
String[] filters = new String[0];
if(String.isnotblank(inputtext1)) {
    filters.add('...');
}
if(String.isnotblank(inputtext2)) {
    filters.add('...');
}
if(!filters.isempty()) {
    query += ' where ' + string.join(filters,' AND ');
}
...