troubleshooting Question

Recordset issues in classic ASP

Avatar of goware
goware asked on
ASPMicrosoft SQL Server 2005
14 Comments1 Solution564 ViewsLast Modified:
I am trying to run a join SQL statement from within a classic ASP page to display results in the recordset. I am using the code attached to this question in multiple pages on my site with no problems. I have begun to believe that it's an issue with my SQL statement yet when I put the statement into the Query window in SQL Server Manager Express, it works fine and pulls the exact data it should. This is, of course, very frustrating and I'm sure it's some minor thing I am overlooking.

A few things to note:
1. the "WHERE" statement for dealerid is filled in with a session variable. I hardcoded it into the statement for testing purposes but I know that the session variable populates correctly (based on response.write output)
2. if i put in a response.write after the NOT rs.EOF, I get nothing. If I take out the "WHERE DealerID =" from the SQL statement, it works fine
3. PerPage is already defined
Set rs = Server.CreateObject("ADODB.recordset")
rs.CursorLocation = adUseServer
strSQL = "SELECT Hyundai_Career_Job.JobTitle as JobTitle, Hyundai_Career_Job.PublicDate, Hyundai_Career_Job.JobID as JobID, Hyundai_Career_Job.Education as Education, Hyundai_Career_Job.JobType as JobType, Hyundai_Career_Job.Experience as Experience, Hyundai_Career_Dealer.DealerID as DealerID, Hyundai_Career_Dealer.Name as DealerName, Hyundai_Career_Job.RemoveDate as RemoveDate, Hyundai_Career_Dealer.City as DealerCity, Hyundai_Career_Dealer.State as DealerState, Hyundai_Career_Dealer.Zip as DealerZip, Hyundai_Career_Dealer.Status as Status FROM Hyundai_Career_Job JOIN Hyundai_Career_Dealer on Hyundai_Career_Dealer.DealerID = Hyundai_Career_Job.DealerID WHERE Hyundai_Career_Job.RemoveDate IS NOT NULL AND Hyundai_Career_Dealer.DealerID = '37' ORDER BY Hyundai_Career_Job.PublicDate DESC"
rs.Open strSQL, conn, adOpenStatic, adLockOptimistic			
if NOT rs.EOF then
		rs.AbsolutePage = PageNum 
        	x = 0 
       		while x < PerPage and not rs.eof
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 14 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros