ADODB.Recordset error '800a0e7d' 2

[gchaves]

HELP! I created two reports - one to pull # of leads submitted between two dates and another to pull # leads sold between two dates. Both were working yesterday...yet later that night (after not making any changes to the code or db), both stopped working and kept throwing the following error message:

ADODB.Recordset error '800a0e7d'

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

/includes/reports/date_counts.asp, line 37

I have no problem connecting to the database (I can add, edit, list, delete, etc. with no problem), so I am not sure
why I'm getting this error? I'll list my queries below...am I using any reserved words that could be throwing my queries off?

Query 1 (get count of leads by date submitted)

			'create recordset object
			Set objRSDateSrchResults = Server.CreateObject("ADODB.Recordset")

			sqlDateSrchResults = "SELECT test_request.curDate, Count(test_request.bus_name) AS CountOfbus_name FROM test_request GROUP BY test_request.curDate HAVING (((test_request.curDate) BETWEEN #" & date1 & "# AND #" & date2 & "#));"
			
			'open the recordset
			objRSDateSrchResults.Open sqlDateSrchResults, objConn, 2, 3

Query 2 (sum the count of leads by the date range in above query)

			Set objRSDateCountResults = Server.CreateObject("ADODB.Recordset")

			sqlDateCountResults = "SELECT Count(test_request.bus_name) AS CountOfbus_name FROM test_request HAVING (((test_request.curDate) BETWEEN #" & date1 & "# AND #" & date2 & "#));"
			
			'open the recordset
			objRSDateCountResults.Open sqlDateCountResults, objConn, 2, 3

query 3 (get count of leads sold between date range)

			Set objRSDateCntResults = Server.CreateObject("ADODB.Recordset")

			sqlDateCntResults = "SELECT Count(test_request.ID_lead_status) AS lead_count, test_request.ID_lead_status, test_request.curDate, test_request.bus_name FROM test_request INNER JOIN lead_status ON test_request.ID_lead_status = lead_status.ID_lead_status GROUP BY test_request.ID_lead_status, test_request.curDate, test_request.bus_name HAVING (((test_request.ID_lead_status)=1 OR (test_request.ID_lead_status)=2) AND ((test_request.curDate) BETWEEN #" & date1 & "# AND #" & date2 & "#));"

			'open the recordset
			objRSDateCntResults.Open sqlDateCntResults, objConn, 2, 3

query 4 (sum count of sold leads in date range from query above)

			Set objRSDateCountResults = Server.CreateObject("ADODB.Recordset")

			sqlDateCountResults = "SELECT Count(test_request.ID_lead_status) AS CountOfID_lead_status FROM test_request INNER JOIN lead_status ON test_request.ID_lead_status = lead_status.ID_lead_status WHERE test_request.curDate BETWEEN #" & date1 & "# AND #" & date2 &"# HAVING (((lead_status.ID_lead_status)=1 Or (lead_status.ID_lead_status)=2));"
			
			'open the recordset
			objRSDateCountResults.Open sqlDateCountResults, objConn, 2, 3

Any help would be greatly appreciated!

 

[DreXor]

what is on and or around line 37 ?

also, the error seems like you're trying to use something that isn't there like objconn is closed or mis-created.. something along those lines.



DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[ahmun]

Are you running all 4 of those queries on the same page? You may have to close the recordset before re-opening it for the next query...

Open objRSDateCountResults for query 1,
store query results into variable(s)
close objRSDateCountResults for query 1

Open objRSDateCountResults for query 2
store query results into variable(s)
close objRSDateCountResults for query 2

etc...

Earnie Eng

 

[DreXor]

queries 1,2,3 are diff names, query 4 reuses query 2's name, that might be one of the conflicts.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[gchaves]

Thanks for the help! I did figure out what was causing the error. It was actually a totally separate include file that wasn't closing out the objConn. Because it was in a separate file (and I did happen upon it by accident)....everything is working fine! Also, because it was in a separate file...I was pulling my hair out trying to find the cause because I was looking in the WRONG FILE!

Thanks, again, for the help! I appreciate it!