+ Start a Discussion
Matt TindallMatt Tindall 

Expecting a semi-colon

Hello,

 

I’m using a class to dynamically update and display data from OpportunityLineItem.

 

I’ve got the data coming through fine; i now want to tweak the filer of the SOQL search to make sure the object is related to an actual customer. I’ve run my query through the editor and it tests fine but when i place it in the actual code i get the following error: line 29 expecting a semi-colon, found 'Waste'. 

 

Any help would be most appreciated !

 

Here is my code:

public with sharing class WasteSheetController{

    //The SOQL without the order and limit
    private String soql{get;set;}
    
    //The collection of Products
    public List<OpportunityLineItem> products {get;set;}
    
    //The current sort direction
    public String sortDir {
        get{if(sortDir == null){sortDir = 'asc';} return sortDir;}
        set;
    }
        
    //Sort the data by Opportunity
    public String sortField {
        get{if(sortField == null){sortField ='Opportunity.Name';} return sortField;}
        set;
    }
        
    // format the soql for display on the visualforce page
    public String debugSoql {
        get { return soql + ' order by ' + sortField + ' ' + sortDir + ' limit 999'; }
        set;
    }

    // init the controller and display some sample data when the page loads
    public WasteSheetController() {
        soql = 'select Id, Opportunity.Name, PricebookEntry.Product2.Name, Delivered__c, Contact_Sent__c, Contract_Signed__c, Ordered__c, Order_Confirmed__c, Opportunity.Postcode__c from OpportunityLineitem WHERE Opportunity.StageName='Waste Sale!'AND Opportunity.Name !=null';
        runQuery();
    }
    
    // toggles the sorting of query from asc<-->desc
    public void toggleSort() {
        // simply toggle the direction
        sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
        // run the query again
        runQuery();
    }
    
    // runs the actual query
    public void runQuery() {
        try {
            products = Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' limit 999');
        } catch (Exception e) {
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Something has gone wrong this the search!'));
        }
    }
        
    // runs the search with parameters passed via Javascript
    public PageReference runSearch() {
        String Opportunity = Apexpages.currentPage().getParameters().get('Opportunity');
        
        soql = 'select Id, Opportunity.Name, PricebookEntry.Product2.Name, Delivered__c, Contact_Sent__c, Contract_Signed__c, Ordered__c, Order_Confirmed__c, Opportunity.Postcode__c from OpportunityLineitem WHERE Opportunity.StageName='Waste Sale!'AND Opportunity.Name !=null';
            if (!Opportunity.equals(''))
                soql += ' and Opportunity.Name LIKE \''+String.escapeSingleQuotes(Opportunity)+'%\'';

                
        //Run the query again
        runQuery();
        
        return null;
    }
}

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Sean TanSean Tan

You need to be sure to escape the single quotes in your SOQL query. I also would strongly recommend against filtering name is not null in the query as it will slow down the query signficantly as well as not do anything since the name will be required by Salesforce anyways.

 

To fix the error try this:

 

// init the controller and display some sample data when the page loads
    public WasteSheetController() {
        soql = 'select Id, Opportunity.Name, PricebookEntry.Product2.Name, Delivered__c, Contact_Sent__c, Contract_Signed__c, Ordered__c, Order_Confirmed__c, Opportunity.Postcode__c from OpportunityLineitem WHERE Opportunity.StageName=\'Waste Sale!\'';
        runQuery();
    }

 

All Answers

Matt TindallMatt Tindall

This is the section where the error is:

    // init the controller and display some sample data when the page loads
    public WasteSheetController() {
        soql = 'select Id, Opportunity.Name, PricebookEntry.Product2.Name, Delivered__c, Contact_Sent__c, Contract_Signed__c, Ordered__c, Order_Confirmed__c, Opportunity.Postcode__c from OpportunityLineitem WHERE Opportunity.StageName='Waste Sale!'AND Opportunity.Name !=null';
        runQuery();
    }

 

Sean TanSean Tan

You need to be sure to escape the single quotes in your SOQL query. I also would strongly recommend against filtering name is not null in the query as it will slow down the query signficantly as well as not do anything since the name will be required by Salesforce anyways.

 

To fix the error try this:

 

// init the controller and display some sample data when the page loads
    public WasteSheetController() {
        soql = 'select Id, Opportunity.Name, PricebookEntry.Product2.Name, Delivered__c, Contact_Sent__c, Contract_Signed__c, Ordered__c, Order_Confirmed__c, Opportunity.Postcode__c from OpportunityLineitem WHERE Opportunity.StageName=\'Waste Sale!\'';
        runQuery();
    }

 

This was selected as the best answer
Matt TindallMatt Tindall
Thanks for your help, that worked fine!
Matt TindallMatt Tindall

How would i go about putting extra values in? Say i wanted to also include option 2 or option 3?

So Opportunity.StageName could be one of 3 vaules, how would i go about writng that?

Sean TanSean Tan

A couple options for that. You can either construct the query via using an IN clause or just use OR separators. If it's the same field then an IN clause will work fine. Try something like this:

 

// init the controller and display some sample data when the page loads
    public WasteSheetController() {
        soql = 'select Id, Opportunity.Name, PricebookEntry.Product2.Name, Delivered__c, Contact_Sent__c, Contract_Signed__c, Ordered__c, Order_Confirmed__c, Opportunity.Postcode__c from OpportunityLineitem WHERE Opportunity.StageName IN (\'Waste Sale!\', \'Option 2\', \'Option 3\')';
        runQuery();
    }