Solved

ASP.NET / SQL2005 Close Connection

Posted on 2011-03-01
2
258 Views
Last Modified: 2012-06-27
When executing my web application I noticed that the database connection stays opened even after I close the connection.  I see this by running the sp_who in SQL2005.  I listed below a piece of code that shows I closed the connection and even try to set it to nothing.  Is there something I am missing?  I am ready to deploy but do not want to run into any connection issues.  There will be roughly 100 uses accessing the application.


    Function GetUserNameByID(ByVal lUserID As Long) As String

        Dim ds As New DataSet
        Dim cn = New SqlConnection(msSQLConnectionString)
        cn.Open()

        Try

            Dim cmd As New SqlCommand("getUserByID", cn)
            cmd.CommandType = CommandType.StoredProcedure


            '...Set up parameter for stored procedure
            cmd.Parameters.Add("@UserID", SqlDbType.Int)
            cmd.Parameters("@UserID").Value = lUserID
            Dim Reader As SqlDataReader = cmd.ExecuteReader

            Reader.Read()
            GetUserNameByID = Reader("Fullname").ToString

            cmd.Dispose()
            Reader.Close()
            Reader = Nothing

        Catch ex As Exception
            cn.Close()
            GetUserNameByID = ""
        Finally
            cn.Close()
            cn = Nothing

        End Try



    End Function
0
Comment
Question by:jseg
  • 2
2 Comments
 
LVL 18

Accepted Solution

by:
ChetOS82 earned 250 total points
ID: 35008153
If you run the page a bunch of times (like press and hold Ctrl+F5) do you get dozens of connections that won't close, or only one or two?

Connection pooling is probably enabled (as it should be) so even though you "close" the connection, the SQL client maintains the actual connection so it can be resused.
0
 
LVL 18

Expert Comment

by:ChetOS82
ID: 35008166
Also, you don't need to close the connection in the catch if you close it in the finally.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question