SQL query works fine in SQL2K AND Access, but not in CR via .NET?!

[ChainsawJoe]

The following SQL query run perfectly in SQL 2K and even in Access (I tried creting it in access to get the bracketing that both Access and CR use, in case that was the issue) - I've even removed the left joins to see if that was the problem, but here's what happens;

SELECT
    Pages."Title",
    Pages."Abstract",
    Pages."UpdatedDate",
    KnowledgeBaseItem."CreatedDate",
    KnowledgeBaseItem."Hits",
    Person."Forename",
    Person."Surname",
    KnowledgeBaseGroupSubtype."Name"
FROM
    ((PageWebs INNER JOIN ((Pages INNER JOIN KnowledgeBaseItem ON Pages.Page_PKID = KnowledgeBaseItem.Page_FKID) INNER JOIN Person ON Pages.Person_FKID = Person.Person_PKID) ON PageWebs.Page_FKID = Pages.Page_PKID) LEFT outer JOIN KnowledgeBaseItemSelectedSubtypes ON KnowledgeBaseItem.Item_PKID = KnowledgeBaseItemSelectedSubtypes.Item_FKID) LEFT outer JOIN KnowledgeBaseGroupSubtype ON KnowledgeBaseItemSelectedSubtypes.Subtype_FKID = KnowledgeBaseGroupSubtype.Subtype_PKID              
WHERE
    (((KnowledgeBaseItem.EndDate)>getdate() Or (KnowledgeBaseItem.EndDate) Is Null))

returns incorrect data via Crystal Report - but ONLY when viewed via a C# .NET page.. within CR, it's fine. in a .NET webform page the results are very strange - rows are returned that I can't make appear via query analyser no mater how I screw around with the SQL. it's not the "left joins" that are being missed (as I thought it might be) and it's not the "where" clause being ignored either.

I'm relly getting annoyed with this now - can anyone help please?!

--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------

 

[synapsevampire]

Try applying the Monthly Hotfix:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/cr10netwin_de.zip.asp

Not sure what "here's what happens" means, is that some incorrect SQL being generated, or?

How are you using this SQL?

-k

 

[ChainsawJoe]

yeah, sorry - wasn't making too much sense I guess.

That SQL is used by being pasted into the "Database->View SQL Query" box. Or in fact just being created via the CR Report Expert.

The results returned within CR itself are the correct results.

Running the report thru a .NET webform returns spurious results - nothing like that which was expected. I can't really explain it better than that; it's as if there's different SQL being used returning the correct fields but incorrect results for those fields.

Does anyone know if, for example, the "getdate()" might die when used in a CR report thru .NET?

I'm using CR7 upgraded to 8.5, MS Development Environment 2002 v7.0.9466, MS .NET Framework 1.0 v1.0.3705 with Crystal Reports for Visual Studio .NET.

I find it hard to explain what's going wrong, other than the results returned are not the correct results, and the same report accesses the same database with the same SQL but through different applications (CR -> CR via .NET webform).

Muchas grassy arse for all help tendered!

--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------