+ Start a Discussion
Michael3.BrownMichael3.Brown 

I Need Help with a Simple Dynamic SQL Query

Hello, I am trying to insert a variable into an SQL statement rather than having to hard code the SQL statement. Basically, I'm trying to build a list of cars made by a certain company.

 

I have tried two methods

 

In the first, I basically put the SQL statement into a string and concatenate the car company on the end. However, I think I need to include single quotes around the car company, but I wasn't sure how to do this since single quotes are used to open and close string variables. This method gave me an error, saying I needed to iterate my for loop over a collective item.

List<Cars__c> carList = new List <Cars__c>();


List<Cars__c> getcarList()
{
        make = ApexPages.currentPage().getParameters().get('carmake');
        sqlquery = '[SELECT Make__c, Model__c FROM Cars__c WHERE Make__c = ' + make + ']';
        
        for (Cars__c carrecord : sqlquery)
        {
            carList.add(carrecord); 
        }
 return carList;
}

 

My other method was to include the SQL within the for loop and see if I could just call the variable, make, but then I get an error saying that "make" is not recognized.

List<Cars__c> carList = new List <Cars__c>();


List<Cars__c> getcarList()
{
        make = ApexPages.currentPage().getParameters().get('carmake');
                
        for (Cars__c carrecord : [SELECT Make__c, Model__c FROM Cars__c WHERE Make__c = make])
        {
            carList.add(carrecord); 
        }
return carList;
}

 

Does anyone have any insight into how I can use my make variable within my SQL statement?

 

Thanks!

Mike

Best Answer chosen by Admin (Salesforce Developers) 
Jake GmerekJake Gmerek

 

List<Cars__c> carList = new List <Cars__c>();


List<Cars__c> getcarList()
{
        make = ApexPages.currentPage().getParameters().get('carmake');
                
        for (Cars__c carrecord : [SELECT Make__c, Model__c FROM Cars__c WHERE Make__c =: make])
        {
            carList.add(carrecord); 
        }
return carList;
}

 

You have to have a colon before you use a variable in a SOQL Query(see above).  That code should work for you.

 

All Answers

Jake GmerekJake Gmerek

 

List<Cars__c> carList = new List <Cars__c>();


List<Cars__c> getcarList()
{
        make = ApexPages.currentPage().getParameters().get('carmake');
                
        for (Cars__c carrecord : [SELECT Make__c, Model__c FROM Cars__c WHERE Make__c =: make])
        {
            carList.add(carrecord); 
        }
return carList;
}

 

You have to have a colon before you use a variable in a SOQL Query(see above).  That code should work for you.

 

This was selected as the best answer
Shashikant SharmaShashikant Sharma

Change this 

 

for (Cars__c carrecord : [SELECT Make__c, Model__c FROM Cars__c WHERE Make__c = make])

 

 

to 

 

for (Cars__c carrecord : [SELECT Make__c, Model__c FROM Cars__c WHERE Make__c =: make])

 used =: instead = so that varibable gets recognised.

 

Prafull G.Prafull G.

Jake ponting you in correct direction. Did you tried this ?

Michael3.BrownMichael3.Brown

Thanks everyone, that was it! I was unaware of the colon situation.

 

Thanks! I really appreciate it.