LLBLGen Version: 2.0.0.0 Final, Self-Service Database Version: Access 2003

Hi,

I was trying to formulate a SQL query, it returns result in Access, however, when I try to retrieving using code, it returns an empty datatable. I'm sure I made a mistake somewhere, but haven't seem to be able to find it. Thanks ahead for all of your help~!

Sql query trying to formulate:

/// SELECT Code, Name, Contents, Contents1, Items AS relatedItems
/// FROM [_Products] LEFT JOIN [RelatedItems] ON p.Contents1 = r.Id
/// WHERE Supplier = @supplierid 
///  AND Name NOT LIKE @excludeword_1
///  ...
///  AND Name NOT LIKE @excludeword_n
///  AND Code IN (
///      SELECT Code
///      FROM [_Products] INNER JOIN [ProductCat2] ON p.Code = p2.Child
///      WHERE Supplier = @supplierid
///          AND Parent NOT IN ( @excludecategories )
///  )
/// ORDER BY Name

Code written:

ResultsetFields rs;
IRelationCollection relation;
IPredicateExpression filter;
ISortExpression sorter;
string[] excludewords, excludecategories;

ResultsetFields subFields;
IRelationCollection subRelation;
IPredicateExpression subFilters;
FieldCompareSetPredicate inSubQueryClause;

excludewords = new string[] { "someword1", ..., "someword_n" }
excludecategories = new stringp[ { "cate_1", ..., "cate_n" }

rs = new ResultsetFields(5);
rs.DefineField(ProductsFields.Code, 0);
rs.DefineField(ProductsFields.Name, 1);
rs.DefineField(ProductsFields.Contents, 2);
rs.DefineField(ProductsFields.Contents1, 3);
rs.DefineField(RelatedItemsFields.Items, 4, "relatedItems");

relation = new RelationCollection();
relation.Add(
       ProductsEntity.Relations.RelatedItemsEntityUsingContents1,
       JoinHint.Left);

filter = new PredicateExpression(ProductsFields.Supplier == supplierId);

for(int i = 0; i < excludewords.Length; i++)
{
     filter.AddWithAnd(
           new FieldLikePredicate(ProductsFields.Name, excludewords[i] + " %", true)
}

subFields = new ResultsetFields(1);
subFields.DefineField(ProductsFields.Code, 0);

subRelation = new RelationCollection();
subRelation.Add(
      ProductsEntity.Relations.ProductCat2EntityUsingChild, 
      JoinHint.Inner);

subFilters =
      new PredicateExpression(ProductsFields.Supplier == supplier);
subFilters.AddWithAnd(ProductCat2Fields.Parent != cate2ToExclude);

inSubQueryClause = new FieldCompareSetPredicate(
      ProductsFields.Code, subFields["Code"], SetOperator.In,
      subFilters, subRelation, false);

filter.AddWithAnd(inSubQueryClause);

sorter = new SortExpression(ProductsFields.Name | SortOperator.Ascending);

TypedListDAO list = new TypedListDAO();
DataTable result = new DataTable();
list.GetMultiAsDataTable(rs, result, 0, sorter, filter, relation,
                    true, null, null, 0, 0);

Generated SQL from output. I tried this query in Access, replacing the parameters with actual parameters and it returns the correct result. I'm not sure why it returns an empty DataTable in code.

Query: SELECT [_Products].Code, [_Products].[Name], [_Products].[Contents], [_Products].[Contents1], [RelatedItems].[Items] AS [relatedItems] FROM ( [RelatedItems]  RIGHT JOIN [_Products]  ON  [RelatedItems].[ID]=[_Products].[Contents1]) WHERE ( ( [_Products].[Supplier] = @Supplier1 AND ( NOT [_Products].[Name] LIKE @Name2 AND NOT [_Products].[Name] LIKE @Name3 AND NOT [_Products].[Name] LIKE @Name4 AND NOT [_Products].[Name] LIKE @Name5 AND NOT [_Products].[Name] LIKE @Name6 AND NOT [_Products].[Name] LIKE @Name7) AND [_Products].Code IN (SELECT [_Products].Code FROM ( [_Products]  INNER JOIN [ProductCat2]  ON  [_Products].Code=[ProductCat2].[Child]) WHERE ( [_Products].[Supplier] = @Supplier8 AND [ProductCat2].[Parent] NOT IN (@Parent9))))) ORDER BY [_Products].[Name] ASC
    
        Parameter: @Supplier1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 24.
    Parameter: @Name2 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "someword_1 %".
    Parameter: @Name3 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "someword_2 %".
    Parameter: @Name4 : String. Length: 13. Precision: 0. Scale: 0. Direction: Input. Value: "someword_3 %".
    Parameter: @Name5 : String. Length: 7. Precision: 0. Scale: 0. Direction: Input. Value: "someword_4 %".
    Parameter: @Name6 : String. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: someword_5 %".
    Parameter: @Name7 : String. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: someword_6 %".
    Parameter: @Supplier8 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 24.
    Parameter: @Parent9 : String. Length: 128. Precision: 0. Scale: 0. Direction: Input. Value: "some_category".

Thanks again for your help!

Doesn't the Jet driver used by Access MDB files use * instead of % for wild card characters?

Hi Jim,

Thanks for the response~ I use "" for wild card character in Access query, but it seems in code using OleDb I still have to use "%" for wild card character, hence I used "%" with the LLBLGen LIKE predicates. But just in case, I also just tried using "" instead, but still doesn't seem to have any luck with it. Thanks for the response though~!

Which runtime library version are you using?

Hi~ Sorry, forgot to list that... Runtime Library is 2.0.50727. Thanks~

excludecategories = new stringp[ { "cate_1", ..., "cate_n" }

Here I assume there's a typo.. isn't?

subFilters.AddWithAnd(ProductCat2Fields.Parent != cate2ToExclude);

What's cate2ToExlude? Is the same that excludecategories?

Could you paste the most realistic code you can? Also could you please enable/disable some predicate filters to determine where exactly the issue is?

Regards...