I am trying to create a dynamic list. I just can't figure out why this is so difficult to implement and would appreciate any comments or suggestions.

I have two tables related to each other in a one to many manner as such MyTblDocCatEntity.Relations.TblDocSpecCatEntityUsingDocCatId

This is the SQL I'd like to replicate with a dynamic list.

SELECT 
    dt.DocCatID, dt.DocCatName 
FROM 
    tblDocCat dt, tblDocSpecCat dst 
WHERE 
    dt.DocCatID = dst.DocCatID 
    AND 
    dst.DocSpecID = @lngDocSpecID 
ORDER BY 
    dt.DocCatName

I have stripped the code down to just one field to try and identify why this is not working:

//Define fields
ResultsetFields fields = new ResultsetFields(1);

fields.DefineField(TblDocCatFields.DocCatId, 0,"DocCatId","DocCat");

//Build relation predicate
IRelationPredicateBucket bucket = new RelationPredicateBucket();

bucket.Relations.Add(MyTblDocCatEntity.Relations.TblDocSpecCatEntityUsingDocCatId,"DocCat");    

//Populate table
DataTable dynamicList = new DataTable();
//Error raised on this line
adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true);

The error message returns this : The multi-part identifier "DocCat.DocCatID" could not be bound.

Any suggestions or comments appreciated. Thank you

(MyTblDocCatEntity.Relations.TblDocSpecCatEntityUsingDocCatId,"DocCat");

You are using Aliases in the worng way. The alias string used in the above line of code is for the second end of the relation.

Try the following code:

//Define fields ResultsetFields fields = new ResultsetFields(1);

fields.DefineField(TblDocCatFields.DocCatId, 0,"DocCatId");

//Build relation predicate IRelationPredicateBucket bucket = new RelationPredicateBucket();

bucket.Relations.Add(MyTblDocCatEntity.Relations.TblDocSpecCatEntityUsingDocCatId);

//Populate table DataTable dynamicList = new DataTable(); //Error raised on this line adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true);

If you don't join to the same table more than once, then you don't need to use aliases at all.

Thank you very much. You were spot on - I was not using it correctly

And thank you to all the people who respond here. This is one of the few - actually the only forum I have used - where someone usually responds within hours, if not minutes with something insightful and helpful.

Keep it up!

Best regards to you too. IMHO, this forum is a big happy family :>