0

I am getting this error when I type a string in search and select an alphabet for showing the accounts,

unexpected token: 'Where'
Error is in expression '{!Showaccounts1}' in page emailtoolpage:
Class.EmailController.showAccounts1: line 141, column 1

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

The following is the code,

public void showAccounts1(){
        system.debug('test');
        if (AlphaFilter == null || AlphaFilter.trim().length() == 0) {
            AlphaFilter = 'All';
        }
        string strQuery = 'select id,name,(select id,Email from contacts where Receives_Maintenance_Notifications__c=true) from account';
        if (AlphaFilter != 'All') {
            if(strQuery.indexOf('Where')!=-1){
                         strQuery = strQuery + ' and Name LIKE \'' + String.escapeSingleQuotes(AlphaFilter) + '%\'';   
            }
            //QueryWhere = BuildWhere(QueryWhere, '(' + String.escapeSingleQuotes(SortField) + ' LIKE \'' + String.escapeSingleQuotes(AlphaFilter) + '%\')' );
            else{
            strQuery = strQuery + ' where Name LIKE \'' + String.escapeSingleQuotes(AlphaFilter) + '%\'';
            }
        }

        system.debug('strQuery ====***'+strQuery);
        //string strQuery = 'select id,name,(select id,Email from contacts where Receives_Maintenance_Notifications__c=true) from account';
        strSearchBy='Name';
        if(strSearchBy!=null && strSearchBy!='' && strSearchText!=null && strSearchText!=''){
             if(strQuery.indexOf('Where')!=-1){
                         strQuery = strQuery + ' and Name LIKE \'' + String.escapeSingleQuotes(strSearchText) + '%\'';   
            }
            else{
            //strQuery = strQuery+ ' where '+strSearchBy+ '=\''+strSearchText+'\'';
            strQuery = strQuery + ' Where Name LIKE \'' + String.escapeSingleQuotes(strSearchText) + '%\'';   
            }
        }
        system.debug('objToolFinal==='+strQuery);
        list<Account> lstAccount =DataBase.Query(strQuery);
        system.debug('lstAccount==='+lstAccount);
        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); 

            }  
        }
        obj = new CustomIterableClass (lstWrapper); 
        system.debug('RecPerPage===='+RecPerPage);
        obj.setPageSize = integer.valueof(RecPerPage);
        next();  
    }
    public Boolean hasNext {             
get { 
return obj.hasNext(); 
}    set;  }      
public Boolean hasPrevious { 
get {   
    return obj.hasPrevious(); 
    //return null;
}   
set;}                 
 public void next()         
 {             
lstWrapper= obj.next();        
 }                  
public void previous()          
{             
lstWrapper= obj.previous();        
 }   
  • And which line is 141? – Adrian Larson Sep 26 '17 at 13:22
  • This is line 141, list<Account> lstAccount =DataBase.Query(strQuery); – user49597 Sep 26 '17 at 13:32
  • Can u add the debug log printed by line 140? – Irina Clington Sep 26 '17 at 13:36
1

indexOf is case-sensitive, so you're actually building a query that ends up looking like this:

SELECT ... FROM Account where ... where ...

Instead, you would want to use indexOfIgnoreCase.

However, that's a kind of naive way of doing this. What I typically do is build a list of filters:

String[] filters = new String[0];
...
if(...) {
    filters.add('Name LIKE \'%'+String.escapeSingleQuotes(someField)+'%\'');
}
...
if(!filters.isEmpty()) {
    strQuery += ' WHERE '+String.join(filters, ' AND ');
}

You might also be interested in using something like apex-lang or fflib to help you build your dynamic queries.

0

I believe the problem is that you have WHERE twice in your query. Check your debug logs.

I would assume that this one:

system.debug('objToolFinal==='+strQuery);

will print out your final query which will contain 2x WHERE in it

0

It's recommended that you use a dynamic query builder instead of building the query string by yourself. So using Query.apex you can do this much easier and with fewer mistakes:

Query q = new Query('Account')
    .selectFields('Id, Name')
    .addSubquery(Query.subquery('Contacts')
        .selectFields('Id, Email')
        .addConditionEq('Receives_Maintenance_Notifications__c', true)
    );

if (AlphaFilter != 'All') {
    q.addConditionLike('Name', AlphaFilter + '%'); // Escape is not needed
}

if (String.isNotBlank(strSearchText)) {
    q.addConditionLike('Name', strSearchText + '%');
}

List<Account> lstAccount = q.run();

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.