Visual Force Error : System.QueryException: invalid ID field: null

[KNK]

Hi,

I am getting  below error

Visualforce Error

Help for this Page

System.QueryException: invalid ID field: null
Class.AddProduct.refreshProductData: line 286, column 1
Class.AddProduct.invalidateProductData: line 307, column 1
Class.AddProduct.changeProductFamily: line 187, column 1
for find vf code for this

global with sharing class AddProduct {

    /*************************************************************************************************/
    /* Options
    /*************************************************************************************************/

    private static final integer[] defaultPages = new integer[]{10};

    global with sharing class Options {
        
        global string country { get; set; }
        global string[] filterCP { get; set; }
        global string[] filterSE { get; set; }
        global integer[] pageSizes {get; set;}
        global ID[] excludeProducts {get; set;}
        global boolean allowDuplicates {get; set;}


        
        global Options()
        {
            this.country = null;
            this.filterCP = new string[]{};
            this.filterSE = new string[]{};
            this.pageSizes = AddProduct.defaultPages;
            this.excludeProducts = new ID[]{};
            this.allowDuplicates = true;
        }
        
        private transient Set<string> internalProductFamilyFilterCP = null;
        global Set<string> productFamilyFilterCP {
            get {
                if (internalProductFamilyFilterCP==null)
                    internalProductFamilyFilterCP = this.FilterCP==null ? new Set<string>() : new Set<string>(this.FilterCP);
                return internalProductFamilyFilterCP;
            }
        }
        
        private transient Set<string> internalProductFamilyFilterSE = null;
        global Set<string> productFamilyFilterSE {
            get {
                if (internalProductFamilyFilterSE==null)
                    internalProductFamilyFilterSE = this.filterSE==null ? new Set<string>() : new Set<string>(this.filterSE);
                return internalProductFamilyFilterSE;
            }
        }

        private transient List<SelectOption> cachedPageSizesSO = null;
        global List<SelectOption> pageSizesSO {
            get {
                if (cachedPageSizesSO==null) {
                    cachedPageSizesSO = new List<SelectOption>();
                    for (integer i : (pageSizes==null || pageSizes.size()==0) ? AddProduct.defaultPages : pageSizes)
                        cachedPageSizesSO.add(new SelectOption(string.valueOf(i),string.valueOf(i)));
                }   
                return cachedPageSizesSO;
            }
        }
        
        private transient Set<string> internalProductFamilyFilter = null;
        global Set<string> productFamilyFilter {
            get {
                if (internalProductFamilyFilter==null){
                    internalProductFamilyFilter = new Set<string>();
                    internalProductFamilyFilter.addAll(productFamilyFilterSE);
                    internalProductFamilyFilter.addAll(productFamilyFilterCP);
                }
                return internalProductFamilyFilter;
            }
        }
        
    }    
    
    /*************************************************************************************************/
    /* Initialize class
    /*************************************************************************************************/
    global AddProduct()
    {
        options = new Options();
        doSearchClicked = false;

        try {
            pageSize = pageSizeTop = pageSizeBottom = this.options.pageSizes[0];
        }
        catch (Exception e) {
            pageSize = pageSizeTop = pageSizeBottom = AddProduct.defaultPages[0];
        }

        selectedProductFamily = null;
        doSearchClicked = false;

        gotoPage(0);
    }
    
    
    global Options options {get; set;}
    
    /*************************************************************************************************/
    /* Common things
    /*************************************************************************************************/
    
    global boolean isOk {
        get {
            return options.country!=null;
        }
    }
    
    private List<SelectOption> newSelectList(boolean empty) {
         return empty ? new List<SelectOption>() : new List<SelectOption>{new SelectOption('', '  -- Select --  ')};
    }

    private List<SelectOption> newSelectList() {
         return newSelectList(true);
    }

    
    private void appendNothingToSelectList(List<SelectOption> l) {
        if (l!=null)
            if (l.size()==0)
                l.add(new SelectOption('', '  -- Select --  '));
            else    
                l.add(0, new SelectOption('', '  -- Select --  '));
    }

    /*************************************************************************************************/
    /* Search form
    /*************************************************************************************************/

    global string selectedProductFamily { get; set; }
    global string selectedProduct {get; set; }
    

    global string doResetSearch {
        get {
            return null;
        }
        set {
            doSearchClicked = false;

            //selectedProductFamily = null;
            //selectedProduct = null;
            
            invalidateProductData();
            gotoPage(0);
        }
    }



    global boolean isSE {
        get {
            return selectedProductFamily!=null && options.productFamilyFilterSE.contains(selectedProductFamily);
        }
    }

    global boolean isCP {
        get {
            return selectedProductFamily!=null && options.productFamilyFilterCP.contains(selectedProductFamily);
        }
    }



    private transient List<SelectOption> cacheProductFamilies = null;
    global List<SelectOption> productFamilies
    {  
        get {
            if (cacheProductFamilies==null){
                cacheProductFamilies = newSelectList(false);
                cacheProductFamilies.add(new SelectOption('CP','CP'));
                cacheProductFamilies.add(new SelectOption('Seeds','Seeds'));
            }
            return cacheProductFamilies;            
        }
    }
    
    global boolean readyForSearch {
        get {
            return selectedProductFamily!=null;
        }
    }

    // POSTBACK
    global PageReference changeProductFamily() {
            doSearchClicked = false;
            selectedProduct = null;
            invalidateProductData();
            return null;
    }

    global boolean doSearchClicked {get; set;}
    

    // POSTBACK
    global PageReference doSearch() {
        doSearchClicked = true;

        invalidateProductData();

        gotoPage(0);
        return null;     
    }

    /*************************************************************************************************/
    /* Search results
    /*************************************************************************************************/

    global class ProductData {
        global integer flag {get; set;}
        global Product_Shipper__c product {get; set;}
        private AddProduct page {get; set;}
        
        global ProductData(Product_Shipper__c product, AddProduct page) {
            this.product = product;
            this.page = page;
            this.flag = -1;
        }
        global boolean isCP {
            get {
                return product!=null && product.Family__c!=null && page.options.productFamilyFilterCP.contains(product.Family__c);
            }
        }
        global boolean isSE {
            get {
                return product!=null && product.Family__c!=null && page.options.productFamilyFilterSE.contains(product.Family__c);
            }
        }   
    }
 
    private transient List<ProductData> cacheProductData = null;
    private static final List<ProductData> emptyProductData = new List<ProductData>();

    
    // search query
    private transient boolean productdata_refreshed = false;
    private void refreshProductData()
    {
            if (productdata_refreshed==null || productdata_refreshed!=true) {
                cacheProductData = new List<ProductData>();
            
                //system.debug('--------------- refreshProductData reads data');
            
                Set<ID> exclude = new Set<ID>();
                if (options.excludeProducts!=null)
                    exclude.addAll(options.excludeProducts);
                
                string sexclude = null;
                if (exclude.size()>0)
                    sexclude = 'not in (' + Utils.qjoin(',', exclude) + ')';
                
                exclude = null;
                
                               
                    string soql =   'Select p.Name,p.Id, p.Family__c,p.APVMA_Approval_number__c, ' +
                                    'p.Price__c, p.Label__r.Country__c ' +
                                    'From Product_Shipper__c p ' +
                                    'Where ' +
                                    'p.Active__c = True '+
                                    'and (p.Label__r.Country__c = \'' + options.country + '\') ';
                             
                    if (sexclude!=null)
                        soql += 'and (p.Id '+sexclude +') ';
                                    
                    if (selectedProductFamily!=null)
                    {
                        soql += 'and (p.Family__c=\'' + selectedProductFamily + '\') ';
                        
                    }
                    else
                        soql += 'and (p.Family__c in ('+ Utils.qjoin(',', options.productFamilyFilter) +')) ';
    
                    /*
                    if (selectedProduct != null && selectedProduct.trim()!='')
                        soql += 'and (p.Name like  \'' + string.escapeSingleQuotes(selectedProduct.trim()) + '%\') ';
                    */
                    if (selectedProduct != null && selectedProduct.trim()!='')
                    {
                        if(selectedproduct.endsWith('*'))
                         {    
                            soql += 'and (p.Name like \'' + selectedproduct.removeEnd('*')+'%\') ';
                         }else{
                            soql += 'and (p.Name =  \''+string.escapeSingleQuotes(selectedProduct.trim()) + '\') ';
                         }
                    }  
                    soql += 'order by p.Name limit 300';
                    for (Product_Shipper__c p : Database.query(soql))
                        cacheProductData.add(new ProductData(p, this));
                    
                productdata_refreshed = true;    
            }
    }
    
    global  List<ProductData> productData {
        get {
            if (readyForSearch && doSearchClicked)
            {   
                refreshProductData();   
                return cacheProductData;
            }
            else
                return emptyProductData;
        }
    }
    
    private void invalidateProductData() {
        productdata_refreshed = false;
        refreshProductData();
    }
    
    
    global boolean hasProductData {
        get {
            return totalProducts>0;
        }
    }   
    
    private void gotoPage(integer no) {
        pageNo = no>totalPages ? totalPages-1 : (no<0 ? 0: no);
    }       


    global integer pageSize { get; set; }
    global integer pageSizeTop { get; set; }
    global integer pageSizeBottom { get; set; }


    // POSTBACK
    global PageReference rePaginateTop() {
        
        pageSizeBottom = pageSize = pageSizeTop;
        gotoPage(0);
        
        return null;
    }

    // POSTBACK
    global PageReference rePaginateBottom() {
        
        pageSizeTop = pageSize = pageSizeBottom;
        gotoPage(0);
        
        return null;
    }

    // POSTBACK
    global PageReference nextPage() {
        gotoPage(pageNo+1);
        return null;
    }

    // POSTBACK
    global PageReference prevPage() {
        gotoPage(pageNo-1);
        return null;
    }
    
    global boolean canGoBack {
        get {
            return pageNo>0;
        }
    }
    global boolean canGoForward {
        get {
            return pageNo<totalPages-1;
        }
    }

    global integer pageNo { get; private set; }
    global integer totalProducts {
        get {
            return productData.size();
        }
    }
    
    global integer totalPages {
        get {
            integer i = (totalProducts / pageSize);
            if (i*pageSize<totalProducts) i++;
            return i;
        }
    }
    
}

can any one help me on this.

[AdrianCC]

Hello!

The problem appears to be in the soql string for hte Database.query() at line 286. Add a System.debug to get the soql value before the query:

        soql += 'and (p.Name =  \''+string.escapeSingleQuotes(selectedProduct.trim()) + '\') ';
                         }
                    }  
                    soql += 'order by p.Name limit 300';
                    System.debug('soql has this value: ' + soql); 
                    for (Product_Shipper__c p : Database.query(soql))
                        cacheProductData.add(new ProductData(p, this));
                    
                productdata_refreshed = true; 

Open the Developer Console and check the debug log to see what value the soql has when you open the page. Use open raw to see all the text. 

From what I see the problem is somewhere in the sexclude string.

You can also try to move the Database.query outside the for. Use a List to get its value and surround that piece of code with a try/catch(careful with this one, check that you don't have logic prblems in your code before using it!!).

Have a nice day!

Adrian