Hi to all, Using LLblGen v2.6 .Net Vers 3.5 Self Servicing VB Code from Access 2007 dB

I need to fill 2 listboxes which form a Mover ListBoxes functionality. I have no trouble with the listbox which displays a list of students that are booked into a Training Course Session. This is filled after the user selects a course and session from cascading comboboxes that provide CourseID and SessionID.

tblPerson has a 1:m relationship to tblRegister (personID ersonID) tblRegister has a m:1 relationship to tblCourse (courseID:CourseID) tblCourse has a 1:m relationship to tblSession (SessionID:SessionID)

This where I become stumped:

Another listbox should display Candidate students that are not already booked into the Course.

However I do have the SQL as :

SELECT personID, Fullname
FROM tblPerson
WHERE personID NOT IN
(SELECT personID
FROM tblRegister
WHERE tblRegister.CourseID = cboCourseID.Value)
ORDER BY Fullname

I would like some direction on how to convert this SQL into LLBLGen generated code that will facilitate setting the result as a datasource for the Listbox.

I believed that the desired code would utilise:

filter.Add(new FieldCompareSetPredicate(......)

I'm having trouble correlating FooFields.fieldB in the example Code

How do I ...? Writing a filter which does WHERE field IN...

code to my subselect SQL code above as my need is for a select statement not just a direct read from a table as illustrated in the example code. This is making me think also that I'm probably pursuing a wrong line.

Any and all help is VERY much appreciated.

Hi Valkiri,

Your code should looks like this:

// The filter
// note the last "true" parameter. It says: convert the "IN" into "NOT IN"
FieldCompareSetPredicate subQueryFilter = new FieldCompareSetPredicate(
    PersonFields.PersonId, RegisterFields.PersonId,  SetOperator.In,
     (RegisterFields.CourseId > cboCourseID.Value), null, true);

IPredicateExpression filter = new PredicateExpression;
filter.Add(subQueryFilter);

// The sorter
SortExpression sorter = new SortExpression();
sorter.Add(PersonFields.FullName | SortOperator.Ascending);

// Fetch results
PersonCollection people = new PersonCollection();
people.GetMulti(filter, 0, sorter);

For more info read this

Cheers.

Thanks Daelmo as usual a quick and precise response.

I had to modify your code somewhat but without the solution I was completley stumped.

// The filter
// note the last "true" parameter. It says: convert the "IN" into "NOT IN"
FieldCompareSetPredicate subQueryFilter = new FieldCompareSetPredicate(
    PersonFields.PersonId, RegisterFields.PersonId, SetOperator.In,
     (RegisterFields.CourseId > cboCourseID.Value), null, true);

IPredicateExpression filter = new PredicateExpression;
filter.Add(subQueryFilter);

// The sorter
SortExpression sorter = new SortExpression();
sorter.Add(PersonFields.FullName | SortOperator.Ascending);

// Fetch results
PersonCollection people = new PersonCollection();
people.GetMulti(filter, 0, sorter);

for any one else following this thread then... I found that the FieldCompareSetPredicate had to be changed from :

... SetOperator.In, (RegisterFields.CourseID > cboCourseID.Value),null,true

to:

... setOperator.In, (RegisterFields.CourseID = cboCourseID.SelectedValue),True)

The '>' was corrected to read '=' The null value included in daelmos code somehow did not match the interface for the function so I had to delete it.

Very trivial changes and I only mention it in passing in case someone else is looking for a similar solution at some time in the future.

Once again thanks daelmo you make it look so easy.

Good it works and sorry for my typo...

Cheers.

Absolutely no apologies necessary.

I offer my thanks again.

End of thread!!