Hi,

I want to filter two fields in the DB using the typed list below. I'm not successful. Any ideas please?

Philip

            If chkTSX.Checked = True Then
                TSfilter.Add(New FieldCompareValuePredicate(TimeSheetFields.EmployeeId, ComparisonOperator.Equal, empAll(empi).EmployeeId))
                TSfilter.AddWithAnd(New FieldCompareValuePredicate(TimeSheetFields.DaySheetDate, ComparisonOperator.Equal, StartDate))
                TSfilter.AddWithAnd(New FieldCompareValuePredicate(TimesheetDetailFields.AllocatedPaycode, ComparisonOperator.NotEqual, ""))
                If chkAddMemo.Checked = True Then
                    TSfilter.AddWithAnd(New FieldCompareValuePredicate(TimeSheetFields.TimeSheetAdditionalMemo, ComparisonOperator.Equal, ddlAddMemo.SelectedItem.Value))
                End If
                TSfilter.AddWithAnd(New FieldCompareValuePredicate(TimesheetDetailFields.AllocatedTime, ComparisonOperator.NotEqual, New Expression(TimesheetDetailFields.OrgAllocatedTime)))
                TSfilter.AddWithOr(New FieldCompareValuePredicate(TimesheetDetailFields.Authorisation, ComparisonOperator.NotEqual, New Expression(TimesheetDetailFields.OrgAuthorisation)))
            Else

In what way "Not Successful"...?

We need to know what you were expecting to happen and what actually happend to be able to help you any further. Generated and Expected SQL is always useful in cases like this.

Matt

Hi Matt,

I want to compare two fields, i.e. AllocatedTime with OrgAllocatedTime and if they differ to include them in the TypedList. Also then to compare Authorisation with OrgAuthorisation and if they differ to include in the TypedList. As you can see from the code included there are a few other parameters also to include. I want the above mentioned to be the last of the filters and Authorisation Filter to be added with an "OR".

Hope this helps.

Regards,

Philip

Sorry, still not entirely clear. Could you give us an example of the SQL you need to produce?

I'm assuming it to be something like

SELECT * FROM xxx WHERE AllocatedTime <> OrgAllocatedTime OR Authorisation <> OrgAuthorisation

?

Again, we also need to know what "not successful" means - what were you expecting to happen, and what actually happened ?

Matt

Hi,

Your SQL is what I want. I want the Typed list to return the values as the SQL query should and it is not.

SQL Query and it returns 26 858 rows:

SELECT TimeSheet.DaySheetDate, TimeSheet.FirstOn, TimeSheet.Lastoff, TimeSheet.GraceGiven, TimeSheet.WorkscheduleId, TimeSheet.TimeFinal, TimeSheet.AuthFinal, TimeSheet.Memo, TimeSheet.CallOutCount, TimesheetDetail.AllocatedTime, TimesheetDetail.Authorisation, TimesheetDetail.OrgAllocatedTime, TimesheetDetail.OrgAuthorisation FROM TimeSheet INNER JOIN TimesheetDetail ON TimeSheet.DaySheetId = TimesheetDetail.DaysheetId WHERE (TimesheetDetail.AllocatedTime <> TimesheetDetail.OrgAllocatedTime) OR (TimesheetDetail.Authorisation <> TimesheetDetail.OrgAuthorisation)

Regards,

Philip

Hi Philip

Ok, so you run this SQL in directly against the DB and it returns 26858 rows, yes ? This is the result you are expecting ?

Using SQL Profiler, what is the SQL that gets generated for your typed list, and how many rows does it return...?

Matt

Matt,

I'm a massive fan of LLBLGen and honestly don't have time to look and play around with SQL, etc. Are there any samples I can try to get this resolved with LLBLGen?

Regards,

Philip

Philip

We are here to try and help you get your issues resolved, but at the moment we still can't tell what the actual issue is. Don't for get we can't see your system, database, code etc - all we have to go on is the information in this thread.

You tell us that a query is not producing the results that you expect - the quickest and easiest way for us to help you is to look at the SQL the query is meant to produce and the SQL that is actually producing, and to try and work out the discrepancy between the two.

LLBLGen, at it's core, is a tool for producing SQL statements. At times when it is not working as you would expect, it is the SQL that it generates that are the single most valuable debugging aid that we have. SQL Profile is your friend and ally - use it to help you to help us.

Matt

Hi Philip ,

Ok, just to elaborate on what Matt said, we need as much relevant information as you can give us to understand the problem. Remember that we are here and we just know what you tell us. For instance, at this point we don't know what is the actual error, or what LLBLGen version are you using. You can read the guidelines here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7718

BTW, this kind of questions generally should be posted on "Generated Code" forum.

Another recommendation is to use UBB tags at least for code snippets so we can read easily the code.

Thanks for take a look on these recommendations in the future.

Now, back to your code. I think you are using the wrong predicate class here:

TSfilter.AddWithAnd(New FieldCompareValuePredicate(TimesheetDetailFields.AllocatedTime, ComparisonOperator.NotEqual, New Expression(TimesheetDetailFields.OrgAllocatedTime)))

TSfilter.AddWithOr(New FieldCompareValuePredicate(TimesheetDetailFields.Authorisation, ComparisonOperator.NotEqual, New Expression(TimesheetDetailFields.OrgAuthorisation)))

You are using FieldCompareValuePredicate, but that class is used when you compare a Field and a discrete value. If you want to compare two fields or two expressions you should use FieldCompareExpressionPredicate. So try this:

TSfilter.AddWithAnd(
     new FieldCompareExpressionPredicate(
          TimesheetDetailFields.AllocatedTime, ComparisonOperator.NotEqual,
         New Expression(TimesheetDetailFields.OrgAllocatedTime) ))
...

Tip: above snippet is equivalent to:

TSfilter.AddWithAnd(TimesheetDetailFields.AllocatedTime != TimesheetDetailFields.OrgAllocatedTime)
...

For more info read this.

Hope helpul

Thanks, I found a work-around.