Quey works in Access but not ASP
xcgeek | Posted 7:51pm 4. December 2004 Server Time |
This is the second time I've come across a query that runs fine when testing as a Query in Access but doesn't return any results (no errors and no records) when run from an ASP page. What gives? Does the different connection methods interpret the SQL differently.
Here is the query for what it's worth.
SELECT Results.ID as RID, Starts.ID as SID, Results.FieldName as FieldNameR, Starts.FieldName as FieldNameS FROM (SELECT * FROM PrintOrder WHERE PrintType = 1 AND Include = TRUE) as Starts RIGHT OUTER JOIN (SELECT * FROM PrintOrder WHERE PrintType = 2 AND Include = TRUE) as Results ON Results.FieldName = Starts.FieldName ORDER BY Results.ID ASC, Starts.ID ASC
Any ideas |
pacoonejr | Posted 8:24pm 4. December 2004 Server Time |
lets see some asp code to display the records
you are looking for the aliases right eg RS("SID")
xcgeek | Posted 8:53pm 4. December 2004 Server Time |
the asp code is fine (I test sql queries in asp with a script that simply spits out all records and fields so I can see all the data), this is an issue with ASO vs Access. Something (I suspect the RIGHT OUTER JOIN or sub-query part) doesn't process the same in ADO as native Access.
xcgeek | Posted 9:54pm 4. December 2004 Server Time |
It appears as if you can't use OUTER Joins in Access so I had to resort to using a distinct select on the union of the left and right join results. Messy but it appears to work. I hate access
SELECT DISTINCT * FROM (SELECT * FROM (SELECT Results.ID as RID, Starts.ID as SID, Results.FieldName as FieldName FROM (SELECT * FROM PrintOrder WHERE PrintType = 2 AND Include = TRUE) as Results LEFT JOIN (SELECT * FROM PrintOrder WHERE PrintType = 1 AND Include = TRUE) as Starts ON Results.FieldName = Starts.FieldName ORDER BY Results.ID ASC) UNION ALL ( SELECT Results.ID as RID, Starts.ID as SID, Starts.FieldName as FieldName FROM (SELECT * FROM PrintOrder WHERE PrintType = 2 AND Include = TRUE) as Results RIGHT JOIN (SELECT * FROM PrintOrder WHERE PrintType = 1 AND Include = TRUE) as Starts ON Results.FieldName = Starts.FieldName ORDER BY Results.ID ASC))
Reply to Post Quey works in Access but not ASP
|
|
|
