SQL connections left open using Crystal 11 and MS .NET Framework

[mam668]

We built a UI in MS .NET Framework to run Crystal Reports version 11. The Report accesses a SQL Server database using a stored procedure. We've tried using ODBC and OLE to connect to the database. When the user runs the report using the UI and then exits, the connection stays open in SQL Server. The connection never goes away until we kill the process. When I run the report from within the Crystal application, the connection stays open until I exit, as it should.

This is connection information I see within SQL Server:

SPID = 134.00
DB = db_share
Program = Microsoft (R) .NET Framework
Status = sleeping
Command = AWAITING COMMAND
Input_Buffer = exec "db_share"."dbo"."Report_SQ";1 15, '03/02/06 11:26:00'

The Input_Buffer is the stored procedure that is called from Crystal.

Does anyone have any ideas how to make sure the connection is closed when the user exits the UI?

Thanks.

 

[Turkbear]

Hi,
There must be a connection object method that explicity closes the database connection..Be sure to call it after the data is retreived..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mam668]

We tried adding the following code in the 'Page_Unload' method of the web form:

'crReport is the report object
crReport.Close()
crReport.Dispose()
GC.Collect()

And we've tried different variations of closing/disposing of it, but the connections still stay open. It's related to connection pooling, but we don't want to have connection pooling. Each connection needs to close at the end of each call of a report.

If you have any more ideas, we'd appreciate them.

Thanks.

 

[Turkbear]

Hi,
Instead of just closing the crReport object,you may need to close the datasource object as well..Not sure, in .NET apps, where the connection object is, but I'm pretty sure there must be one - it is what the crReport object uses as its datasource ..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mam668]

We found the problem. .Net was using Crystal Version 9 and the Reports were written in Version 11. These issues were resolved in version 11. Once we upgraded and rebooted, the open connections went away. There's one caveat to upgrading in Visual Studio, the upgrade changed some other parts of the project and they no longer work. But the developer said that none of the errors he's seeing are anything major and all are fixable.

Thanks for all your help.