Subreports not displayed when connection is closed?

[DivideOverflow]

I've inherited an ASP-based online application that uses Crystal Reports 8.5 generated reports to display data from queries to SQL Server 7.0 stored procedures. We had been having problems with the application opening connections to the SQL Server implicitly and then never having them close.

Depending on where I read about the problem, this "connection creep" may or may not cause a degradation in database performance, but it is certainly not the idea situation. I personally noticed that when the number of sleeping connections to SQL Server got to be too high, we would frequently have pages just not load unless you refreshed the page, sometimes repeatedly. Therefore, I began changing our code to explicitly open and close the SQL connections.

As a side effect of these changes, several of our reports that have a main report as well as subreports are now not displaying data in the subreports if the connection is closed or if the recordsets they are reporting off of are
closed. The main report displays just fine, just not the subreports. Also, if I leave the recordsets and connections open, the subreports display just fine, but the connections never close unless I stop and restart the IIS service.

Searches of various forums as well as the Crystal Decisions Knowledge Base haven't really come up with anything exactly like my situation, so I thought I'd come here and see what suggestions I could get. I've included the relevent code below. Thanks in advance!

'Open SQL Connection
Dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.CursorLocation = adUseClient
oConn.Open "Driver={SQL Server};Server=SERVER NAME;Uid=NAME;Pwd=PASSWORD;Database=DATABASE"

ReportInit()
SetReport()
SetReportOptions()
SetReportData()
ReportPaging()
ReportCleanUp()
SetReportViewer()

Sub ReportInit()
	Dim sqlMain		'SQL string for main report
	Dim sqlSub1		'SQL string for subreport 1
	Dim sqlSub2		'SQL string for subreport 2
	
	'Set the SQL
	sqlMain = "STORED PROCEDURE"
	sqlSub1 = "STORED PROCEDURE"
	sqlSub2 = "STORED PROCEDURE"
	
	'Open recordsets for report and subreports
	Set Session("oMainRs") = Server.CreateObject("ADODB.Recordset")
	Session("oMainRs").Open sqlMain, oConn
	
	Set Session("oSub1Rs") = Server.CreateObject("ADODB.Recordset")
	Session("oSub1Rs").Open sqlSub1, oConn
	
	Set Session("oSub2Rs") = Server.CreateObject("ADODB.Recordset")
	Session("oSub2Rs").Open sqlSub2, oConn
End Sub

Sub SetReport()
	'Create the application object
	Set session("oApp") = Server.CreateObject("CrystalRuntime.Application")

	'Main Report
	Set session("oMainRpt") = session("oApp").OpenReport(PATH & "REPORTNAME", 1)

	'Subreport 1
	Set session("oSub1Rpt") = session("oMainRpt").OpenSubreport("REPORTNAME")
	
	'Subreport 2
	Set session("oSub2Rpt") = session("oMainRpt").OpenSubreport("REPORTNAME")
End Sub

Sub SetReportOptions()
	'These lines disable the Error reporting mechanism for web reporting
	session("oMainRpt").MorePrintEngineErrorMessages = False
	session("oMainRpt").EnableParameterPrompting = False

	session("oSub1Rpt").MorePrintEngineErrorMessages = False
	session("oSub1Rpt").EnableParameterPrompting = False

	session("oSub2Rpt").MorePrintEngineErrorMessages = False
	session("oSub2Rpt").EnableParameterPrompting = False
End Sub

Sub SetReportData()
	'Now we must tell the report to report off of the data in the ADO recordset
	session("oMainRpt").DiscardSavedData
	set Database = session("oMainRpt").Database
	set Tables = Database.Tables
	set Table = Tables.Item(1)
	Table.SetPrivateData 3, Session("oMainRs")

	session("oSub1Rpt").DiscardSavedData
	set s1Database = session("oSub1Rpt").Database
	set s1Tables = s1Database.Tables
	set s1Table1 = s1Tables.Item(1)
	s1Table1.SetPrivateData 3, Session("oSub1Rs")
	
	session("oSub2Rpt").DiscardSavedData
	set s2Database = session("oSub2Rpt").Database
	set s2Tables = s2Database.Tables
	set s2Table1 = s2Tables.Item(1)
	s2Table1.SetPrivateData 3, Session("oSub2Rs")
End Sub

Sub ReportPaging()
	'Retrieve the Records and Create the "Page on Demand" Engine Object
	On Error Resume Next                                                  
	session("oMainRpt").ReadRecords                                           
	If Err.Number <> 0 Then                                               
		Response.Write &quot;An Error has occured on the server in attempting to access the data source&quot;
	Else
		If IsObject(session(&quot;oPageEngine&quot;)) Then                              
  			set session(&quot;oPageEngine&quot;) = nothing
		End If
		set session(&quot;oPageEngine&quot;) = session(&quot;oMainRpt&quot;).PageEngine
	End If                                                                
End Sub

Sub ReportCleanUp()
	'Dispose of recordset vars
	Session(&quot;oMainRs&quot;).Close
	Set Session(&quot;oMainRs&quot;) = Nothing
	Session(&quot;oSub1Rs&quot;).Close
	Set Session(&quot;oSub1Rs&quot;) = Nothing
	Session(&quot;oSub2Rs&quot;).Close
	Set Session(&quot;oSub2Rs&quot;) = Nothing

	Session.Contents.Remove(&quot;oMainRs&quot;)
	Session.Contents.Remove(&quot;oSub1Rs&quot;)
	Session.Contents.Remove(&quot;oSub2Rs&quot;)
End Sub

Sub SetReportViewer()
	ACTIVEX VIEWER
End Sub

'Close SQL Connection
oConn.Close
Set oConn = Nothing

 

[DivideOverflow]

Well, isn't this ironic. Not 30 seconds after I post this, I try one more thing to try to fix the problem, and it worked. Amazing how those things work out...

I just added the two subreports to this part of the code:

Sub ReportPaging()
	'Retrieve the Records and Create the "Page on Demand" Engine Object
	On Error Resume Next
	session("oMainRpt").ReadRecords
	session("oSub1Rpt").ReadRecords
	session("oSub2Rpt").ReadRecords
	If Err.Number <> 0 Then
		Response.Write &quot;An Error has occured on the server in attempting to access the data source&quot;
	Else
		If IsObject(session(&quot;oPageEngine&quot;)) Then
  			set session(&quot;oPageEngine&quot;) = nothing
		End If
		set session(&quot;oPageEngine&quot;) = session(&quot;oMainRpt&quot;).PageEngine
	End If      
End Sub

Err... Thanks for the help!