Search Button fails with repeated searches

Question

Can anyone help me figure out what's wrong in the code, the search button only worked once and second time onwards it's not working?

Apex Code

public void showAccounts(){
    system.debug('Entethere=====');

    string strQuery = 'select id,name,(select id,Email from contacts where Receives_Maintenance_Notifications__c=true) from account ORDER BY Name ASC';
    //strSearchBy = 'Name';
    if(strSearchBy!=null && strSearchBy!=''){
        strQuery = strQuery+ ' where '+strSearchBy+ '=\''+strSearchText+'\'';  
         strQuery = strQuery+ ' where '+strSearchBy+ '=\''+strSearchText+'\'';  
    }
    system.debug('strQuery===='+strQuery);
    list<Account> lstAccount =DataBase.Query(strQuery);
    lstWrapper.clear();
    for(account objAccount :lstAccount){
        system.debug('objAcc====='+objAccount);
        if(objAccount.contacts.size()!=0){
            AccountWrapper objWrapper = new AccountWrapper();
            objWrapper.AccountRecord = objAccount;
            objWrapper.numberOfContacts = objAccount.contacts.size(); 
            lstWrapper.add(objWrapper); 

        }  
    }

Visualforce

<apex:inputText value="{!strSearchText}"/>        
    <apex:commandButton value="Search" action="{!showAccounts}"/>
     <apex:pageBlockTable value="{!lstWrapper}" var="vrAccount">
         <apex:column headerValue="Account">
         <apex:inputcheckbox value="{!vrAccount.bSelectedrecord}"/>
         </apex:column> 
         <apex:column value="{!vrAccount.AccountRecord.name}"/>   
         <apex:column value="{!vrAccount.numberOfContacts}" headerValue="Number of Contacts"/>   
     </apex:pageBlockTable>

unexpected token: where

Error is in expression '{!showAccounts}' in component <apex:commandButton> in page emailtool: Class.EmailToolController.showAccounts: line 52, column 1

An unexpected error has occurred. Your development organization has been notified.

Answer 1

The syntax of your SOQL query should look something like this:

SELECT Name, Id FROM Account WHERE Name LIKE '%A' ORDER BY Name

Currently your code creates a query that has the incorrect syntax. The query your code creates will look something like this:

SELECT Name, Id FROM Account ORDER BY Name WHERE Name LIKE '%A'

This is incorrect because the ORDER BY clause should be the last part of the query.

So your code should look something like the below:

public void showAccounts(){
    system.debug('Entethere=====');

    string strQuery = 'select id,name,(select id,Email from contacts where Receives_Maintenance_Notifications__c=true) from account ';
    //strSearchBy = 'Name';
    if(strSearchBy!=null && strSearchBy!=''){
        strQuery = strQuery+ ' where '+strSearchBy+ '=\''+strSearchText+'\'';  
         strQuery = strQuery+ ' where '+strSearchBy+ '=\''+strSearchText+'\'';  
    }

    strQuery = strQuery + ' ORDER BY Name ASC';
    system.debug('strQuery===='+strQuery);
    list<Account> lstAccount =DataBase.Query(strQuery);
    lstWrapper.clear();
    for(account objAccount :lstAccount){
        system.debug('objAcc====='+objAccount);
        if(objAccount.contacts.size()!=0){
            AccountWrapper objWrapper = new AccountWrapper();
            objWrapper.AccountRecord = objAccount;
            objWrapper.numberOfContacts = objAccount.contacts.size(); 
            lstWrapper.add(objWrapper); 

        }  
    }
}

If you look closely at the code I add the ORDER BY clause at the end of the query string. That way your query should have the right syntax.