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();        
 }   
3
  • And which line is 141?
    – Adrian Larson
    Commented Sep 26, 2017 at 13:22
  • This is line 141, list<Account> lstAccount =DataBase.Query(strQuery);
    – user49597
    Commented Sep 26, 2017 at 13:32
  • Can u add the debug log printed by line 140? Commented Sep 26, 2017 at 13:36

3 Answers 3

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();

You must log in to answer this question.

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