Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

ASP.NET / SQL2005 Close Connection

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
jseg
Asked:
jseg
  • 2
1 Solution
 
ChetOS82Commented:
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
 
ChetOS82Commented:
Also, you don't need to close the connection in the catch if you close it in the finally.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now