Man I need a cold beer. Been messing with a problem for what seems like forever today. Have a simple project so decided to use MS Access and didn't want to mess with MSDE.

Entities: Job (1:n) Subcontract (m:1) Contact

Used the following code which uses a FieldCompareSetPredicate. Oh and before I go much further, there is a reason I'm doing things this way. This just happens to be a simplified version for testing purposes.

         Dim relationsInSet As RelationCollection = New RelationCollection
        Dim filterInSet As IPredicateExpression = New PredicateExpression
        
        relationsInSet.Add(JobEntity.Relations.SubContractEntityUsingJobId)
        relationsInSet.Add(SubContractEntity.Relations.ContactEntityUsingContactId)
        filterInSet.Add(PredicateFactory.CompareValue(JobFieldIndex.JobId, ComparisonOperator.Equal, _job.JobId))

        Dim filterSelect As IPredicateExpression = New PredicateExpression(PredicateFactory.CompareValue(ContactFieldIndex.ContactTypeId, ComparisonOperator.Equal, 4))
        filterSelect.AddWithOr(New FieldCompareSetPredicate(EntityFieldFactory.Create(ContactFieldIndex.ContactId), EntityFieldFactory.Create(ContactFieldIndex.ContactId), SetOperator.In, filterInSet, relationsInSet))

        Dim fields As New HelperClasses.ResultsetFields(4)
        fields.DefineField(ContactFieldIndex.ContactId, 0, "ContactId")
        fields.DefineField(ContactFieldIndex.FileAs, 1, "FileAs")
        fields.DefineField(ContactFieldIndex.Hidden, 2, "Hidden")
        fields.DefineField(ContactTypeFieldIndex.Description, 3, "ContactType")

        Dim relations As IRelationCollection = New RelationCollection
        relations.Add(ContactEntity.Relations.ContactTypeEntityUsingContactTypeId)

        Dim dao As New DaoClasses.TypedListDAO
        dao.GetMultiAsDataTable(fields, _subcontractorList, 0, Nothing, filterSelect, relations, True, Nothing, Nothing, 0, 0)

The AccessDQE Produced the correct query as far as I can tell. This is from the trace output.

Generated Sql query: Query: SELECT [Contact].[ContactID] AS [ContactId],[Contact].[FileAs] AS [FileAs],[Contact].[Hidden] AS [Hidden],[ContactType].[Description] AS [ContactType] FROM ( [ContactType] INNER JOIN [Contact] ON [ContactType].[ContactTypeID]=[Contact].[ContactTypeID]) WHERE ( [Contact].[ContactTypeID] = @ContactTypeId1 Or [Contact].[ContactID] IN (SELECT [Contact].[ContactID] AS [ContactId] FROM (( [Job] INNER JOIN [SubContract] ON [Job].[JobID]=[SubContract].[JobID]) INNER JOIN [Contact] ON [Contact].[ContactID]=[SubContract].[ContactID]) WHERE ( [Job].[JobID] = @JobId2))) Parameter: @ContactTypeId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 4. Parameter: @JobId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

The data I was expecting with these particular parameters would have been 1 record. This didn't happen. I got back like 11 or 12 rows totally unrelated in every way to what was supposed to come back. On the bright side, I guess, the 1 I expected was among those. Now, here's the kicker. I copied that SELECT statement into a query in the Access GUI and plugged in the parameter values and it did exactly what it was supposed to do by returning that 1 row.

Heck, I even went and upsized part of the database along with the data to SQL Server 2000 and created a new LLBL project around it and made a test with the same exact code and it worked as expected. The SQLServerDQE generated the same SQL and here it is for reference.

Generated Sql query: Query: SELECT [dbo].[Contact].[ContactID] AS [ContactId],[dbo].[Contact].[FileAs] AS [FileAs],[dbo].[Contact].[Hidden] AS [Hidden],[dbo].[ContactType].[Description] AS [ContactType] FROM ( [dbo].[ContactType] INNER JOIN [dbo].[Contact] ON [dbo].[ContactType].[ContactTypeID]=[dbo].[Contact].[ContactTypeID]) WHERE ( [dbo].[Contact].[ContactTypeID] = @ContactTypeId1 Or [dbo].[Contact].[ContactID] IN (SELECT [dbo].[Contact].[ContactID] AS [ContactId] FROM (( [dbo].[Job] INNER JOIN [dbo].[SubContract] ON [dbo].[Job].[JobID]=[dbo].[SubContract].[JobID]) INNER JOIN [dbo].[Contact] ON [dbo].[Contact].[ContactID]=[dbo].[SubContract].[ContactID]) WHERE ( [dbo].[Job].[JobID] = @JobId2))) Parameter: @ContactTypeId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 4. Parameter: @JobId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

I don't even know where to start looking for the cure. This obviously isn't a bug in LLBLGen Pro. It's just funny that the query works in the Access GUI. Would it be a problem with the OleDB driver? How do I find out what version of the Access OleDB driver is on my system and where do I go to get an update?

Jeff

Hmm. Well, it IS an or query:

SELECT  [Contact].[ContactID] AS [ContactId],
        [Contact].[FileAs] AS [FileAs],
        [Contact].[Hidden] AS [Hidden],
        [ContactType].[Description] AS [ContactType] 
FROM    ( 
            [ContactType] INNER JOIN [Contact] 
            ON [ContactType].[ContactTypeID]=[Contact].[ContactTypeID]
        ) 
WHERE   ( 
            [Contact].[ContactTypeID] = @ContactTypeId1 
            Or 
            [Contact].[ContactID] 
            IN 
            (
                SELECT [Contact].[ContactID] AS [ContactId] 
                FROM (
                        (   [Job] INNER JOIN [SubContract] 
                            ON [Job].[JobID]=[SubContract].[JobID]
                        ) INNER JOIN [Contact] 
                        ON [Contact].[ContactID]=[SubContract].[ContactID]
                    ) 
                WHERE ( [Job].[JobID] = @JobId2)
            )
        )

which means that either contact.contacttypeid = 4 or contact.contactid is in a set defined by the subquery.

I wonder if this really gives just 1 row. Could you check if the resultset set of rows indeed match the criteria set? If not, something weird is going on, but if they do, I can only conclude the query fired to the db is correct and the access gui makes a mistake (that sounds like "It's printed wrong in the encyclopedia!", I know )

OleDb drivers for access come with the MDAC version you can download from the www.microsoft.com/data website. Though the latest versions don't contain any JET engine anymore, so I don't think you have an outdated version.

Otis wrote:

Hmm. Well, it IS an or query:

SELECT  [Contact].[ContactID] AS [ContactId],
        [Contact].[FileAs] AS [FileAs],
        [Contact].[Hidden] AS [Hidden],
        [ContactType].[Description] AS [ContactType] 
FROM    ( 
            [ContactType] INNER JOIN [Contact] 
            ON [ContactType].[ContactTypeID]=[Contact].[ContactTypeID]
        ) 
WHERE   ( 
            [Contact].[ContactTypeID] = @ContactTypeId1 
            Or 
            [Contact].[ContactID] 
            IN 
            (
                SELECT [Contact].[ContactID] AS [ContactId] 
                FROM (
                        (   [Job] INNER JOIN [SubContract] 
                            ON [Job].[JobID]=[SubContract].[JobID]
                        ) INNER JOIN [Contact] 
                        ON [Contact].[ContactID]=[SubContract].[ContactID]
                    ) 
                WHERE ( [Job].[JobID] = @JobId2)
            )
        )

which means that either contact.contacttypeid = 4 or contact.contactid is in a set defined by the subquery.

I wonder if this really gives just 1 row. Could you check if the resultset set of rows indeed match the criteria set? If not, something weird is going on, but if they do, I can only conclude the query fired to the db is correct and the access gui makes a mistake (that sounds like "It's printed wrong in the encyclopedia!", I know )

The subquery does return 1 row in it's resultset. Is that what you wanted to know? Also, the same contact returned by the subquery happens to be the one and only one that has a ContactTypeId of 4. So, the entire query should return 1 row. For testing, I changed ContactTypeId = 5, which there is only one contact with that type at this time and obviously, it isn't the same as the row returned in the subquery. So, I should get 2 rows back from the entire query, which I do in the Access GUI, but not when ran from my program. Something stange is indeed going on.

taylor74 wrote:

Otis wrote:

Hmm. Well, it IS an or query:

SELECT  [Contact].[ContactID] AS [ContactId],
        [Contact].[FileAs] AS [FileAs],
        [Contact].[Hidden] AS [Hidden],
        [ContactType].[Description] AS [ContactType] 
FROM    ( 
            [ContactType] INNER JOIN [Contact] 
            ON [ContactType].[ContactTypeID]=[Contact].[ContactTypeID]
        ) 
WHERE   ( 
            [Contact].[ContactTypeID] = @ContactTypeId1 
            Or 
            [Contact].[ContactID] 
            IN 
            (
                SELECT [Contact].[ContactID] AS [ContactId] 
                FROM (
                        (   [Job] INNER JOIN [SubContract] 
                            ON [Job].[JobID]=[SubContract].[JobID]
                        ) INNER JOIN [Contact] 
                        ON [Contact].[ContactID]=[SubContract].[ContactID]
                    ) 
                WHERE ( [Job].[JobID] = @JobId2)
            )
        )

which means that either contact.contacttypeid = 4 or contact.contactid is in a set defined by the subquery.

I wonder if this really gives just 1 row. Could you check if the resultset set of rows indeed match the criteria set? If not, something weird is going on, but if they do, I can only conclude the query fired to the db is correct and the access gui makes a mistake (that sounds like "It's printed wrong in the encyclopedia!", I know )

The subquery does return 1 row in it's resultset. Is that what you wanted to know? Also, the same contact returned by the subquery happens to be the one and only one that has a ContactTypeId of 4. So, the entire query should return 1 row.

Ah, then it indeed should return 1 row.

For testing, I changed ContactTypeId = 5, which there is only one contact with that type at this time and obviously, it isn't the same as the row returned in the subquery. So, I should get 2 rows back from the entire query, which I do in the Access GUI, but not when ran from my program. Something stange is indeed going on.

yeah, as if the query isn't executed in full (i.e. without where clauses)

Hmm, guess I'll have to come up with a different way of doing what I want, unless you have any suggestions of where to look for the problem. The project is in the early stages with very little code written so far, thus I could switch the DB to MSDE as a last resort. It's a small project and the reason I went with Access is because I didn't wan't to deal with MSDE and I figured Access was a way to not run into potential surprises if I had went with firebird or mysql. Guess the surprise is on me. I didn't expect Access to goof like that.

taylor74 wrote:

Hmm, guess I'll have to come up with a different way of doing what I want, unless you have any suggestions of where to look for the problem. The project is in the early stages with very little code written so far, thus I could switch the DB to MSDE as a last resort. It's a small project and the reason I went with Access is because I didn't wan't to deal with MSDE and I figured Access was a way to not run into potential surprises if I had went with firebird or mysql. Guess the surprise is on me. I didn't expect Access to goof like that.

There is a potential problem with the query though.

You join Contact twice: once in the main query and once in the subquery. It can be the query engine of access gets confused sometimes which Contact you're referring to.

It might be best to alias the subquery's contact. You can do that in the Add() line where you add the relation. Don't know if it helps, but it can be the engine implicitly makes it a co-related subquery while you don't want that, or it doesn't do that while you DO want that.

Otis wrote:

It might be best to alias the subquery's contact. You can do that in the Add() line where you add the relation. Don't know if it helps, but it can be the engine implicitly makes it a co-related subquery while you don't want that, or it doesn't do that while you DO want that.

Never thought of that. I'll give it a try. I change the line that reads:

 relationsInSet.Add(SubContractEntity.Relations.ContactEntityUsingContactId)

to

 relationsInSet.Add(SubContractEntity.Relations.ContactEntityUsingContactId, "ContactInSet")

Now, don't I also need to create an alias for the "setField" paramater in the Predicate I'm adding to the filter and how do I do that?

BTW, I don't want it to be co-related.

Frans,

Here is what I finally ended up doing. I decided to hit the DB with the subquery by itself first and loop through returned values and put them in an array. Then, I use that array as input to a FieldCompareRangePredicate that I add to the main query and hit the DB again. That seems to work.

Thanks for your thoughts on this. Jeff

taylor74 wrote:

Otis wrote:

It might be best to alias the subquery's contact. You can do that in the Add() line where you add the relation. Don't know if it helps, but it can be the engine implicitly makes it a co-related subquery while you don't want that, or it doesn't do that while you DO want that.

Never thought of that. I'll give it a try. I change the line that reads:

 relationsInSet.Add(SubContractEntity.Relations.ContactEntityUsingContactId)

to

 relationsInSet.Add(SubContractEntity.Relations.ContactEntityUsingContactId, "ContactInSet")

Now, don't I also need to create an alias for the "setField" paramater in the Predicate I'm adding to the filter and how do I do that?

The field you specify for setField, using EntityFieldFactory.Create... has a property, ObjectAlias, you can set that to the alias to use.

That makes the query not co-related.

Otis wrote:

The field you specify for setField, using EntityFieldFactory.Create... has a property, ObjectAlias, you can set that to the alias to use.

That makes the query not co-related.

I tried this, but I must have done something wrong, because I got an OleDbExecption. So, I just went in an aliased everything I could. Here is the SQL, now.

 SELECT [ContactTable].[ContactID] AS [ContactId],
[ContactTable].[FileAs] AS [FileAs],
     [ContactTable].[Hidden] AS [Hidden],
     [ContactTypeTable].[Description] AS [ContactTypeDescription]
 FROM ( [ContactType] [ContactTypeTable] INNER JOIN [Contact] [ContactTable]
      ON  [ContactTypeTable].[ContactTypeID]=[ContactTable].[ContactTypeID])
 WHERE ( [ContactTypeTable].[ContactTypeID] = @ContactTypeId1 
          Or
      [ContactTable].[ContactID] IN 
          (SELECT [ContactInSet].[ContactID] AS [ContactId]
 FROM (( [Job] INNER JOIN [SubContract] ON  [Job].[JobID]=[SubContract].[JobID])
 INNER JOIN [Contact] [ContactInSet] ON  [ContactInSet].[ContactID]=[SubContract].[ContactID])
 WHERE ( [Job].[JobID] = @JobId2)))

I wouldn't think Access would be mixing anything up with that query, but it still returns incorrect results.

Then I'm out of options...

Otis wrote:

Then I'm out of options...

Yup, me to. I'm just going to stick with my solution above of running the subquery separate and plugging the results into a RangePredicate.

The closest I came to finding the problem with the subquery was an MS KB article about ODBC and Access 97 and earlier. Plus, I checked and I am running the latest Jet engine, so nothing to update there.

Oh well, just write it off as one of those strange things and move on. - Jeff

Hi,

i found exactly same problem.

have anyone found a solution?

thanks!

Would you please post on a new thread with more details about your setup and about the issue?

refer to the following guidelines: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722