Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Generate FieldCompareSetPredicate
 

Pages: 1
LLBLGen Pro Runtime Framework
Generate FieldCompareSetPredicate
Page:1/1 

  Print all messages in this thread  
Poster Message
yruheresteve
User



Location:
Chino, California, US
Joined on:
25-Jul-2007 22:32:33
Posted:
9 posts
# Posted on: 13-Sep-2007 18:29:25.  
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:
Code:

/// 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:
Code:

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.
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!
  Top
JimHugh
User



Location:
Davis, CA
Joined on:
16-Nov-2005 14:32:25
Posted:
191 posts
# Posted on: 13-Sep-2007 18:54:00.  
Doesn't the Jet driver used by Access MDB files use * instead of % for wild card characters?

Jim Hughes  Top
yruheresteve
User



Location:
Chino, California, US
Joined on:
25-Jul-2007 22:32:33
Posted:
9 posts
# Posted on: 13-Sep-2007 19:10:15.  
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~!
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14626 posts
# Posted on: 14-Sep-2007 13:15:46.  
Which runtime library version are you using?

  Top
yruheresteve
User



Location:
Chino, California, US
Joined on:
25-Jul-2007 22:32:33
Posted:
9 posts
# Posted on: 14-Sep-2007 18:18:14.  
Hi~
Sorry, forgot to list that... Runtime Library is 2.0.50727. Thanks~
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8137 posts
# Posted on: 17-Sep-2007 06:39:00.  
Code:
excludecategories = new stringp[ { "cate_1", ..., "cate_n" }

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

Code:
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...


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.