System.QueryException: Only variable references are allowed in dynamic SOQL/SOSL.

Error:

Whenever your working with Dynamic SOQL construction you should be very careful regarding the Single Quotes(',') or Open/Close parentheses'(' ')'  and bind variables(':') in query string.If your also getting the same error as mentioned above please refer below code.

Source Code:

public void fetchOrders()
{
List<Order__c> listOrders = new List<Order__c>();
String selWOStatus ='Test'  // Usually value for this comes from visual force page

String q ='SELECT Id, Name, Location__c,End_Date__c,Createddate FROM Order__c where 
           Location__r.OwnerId=:'+UserInfo.getUserId();
                        
if('All'.equalsIgnoreCase(selWOStatus))
  q + = ' AND (Createddate =LAST_N_MONTHS:6 OR End_Date__c < NEXT_N_MONTHS:6) LIMIT 1000'; 

else
  q + =' AND (Status =\''+String.escapeSingleQuotes(selWOStatus)+'\' 
         AND Createddate = LAST_N_MONTHS:6) LIMIT 1000';

System.debug('q....'+q); 

listOrders  = Database.query(q);

System.debug('listTSOOrders....'+listOrders);

}

When you run this above code at run time you will end up with System.QueryException: Only variable references are allowed in dynamic SOQL/SOSL .This is due to the (':') bind variable usage in dynamic query construction which will cause the error and that causes missing a single quote for logged in user ID as shown query string coming at run time as shown below

Actual Query String at Run time:

'SELECT Id, Name, Location__c,End_Date__c,Createddate 
FROM Order__c 
where Location__r.OwnerId=:00560000004LFQkAAO 
AND (Status ='Test' AND Createddate = LAST_N_MONTHS:6) LIMIT 1000';

Excepted Query String at Run time:

'SELECT Id, Name, Location__c,End_Date__c,Createddate 
FROM Order__c 
where Location__r.OwnerId='00560000004LFQkAAO'
AND (Status ='Test' AND Createddate = LAST_N_MONTHS:6) LIMIT 1000';

Work Around Solution
Remove bind variable from query string and add single quotes before and after the user id

public void fetchOrders()
{
List<Order__c> listOrders = new List<Order__c>();
String selWOStatus ='Test'  // Usually value for this comes from visual force page

String qry ='SELECT Id, Name, Location__c,End_Date__c,Createddate FROM Order__c where 
           Location__r.OwnerId=\''+UserInfo.getUserId()+'\'';
                        
if('All'.equalsIgnoreCase(selWOStatus))
  qry + = ' AND (Createddate =LAST_N_MONTHS:6 OR End_Date__c < NEXT_N_MONTHS:6) LIMIT 1000'; 

else
  qry + =' AND (Status =\''+String.escapeSingleQuotes(selWOStatus)+'\' 
         AND Createddate = LAST_N_MONTHS:6) LIMIT 1000';

System.debug('qry....'+qry); 

listOrders  = Database.query(qry);

System.debug('listTSOOrders....'+listOrders);

}

Hope this helps you..Enjoy..!

Please comment or write us if you have any queries/requirements.

Please like,follow,bookmark,subscribe this site to receive daily updates.