SQLConnections not "closing"

[jonbatts]

I'm having the same problem as the user in thread855-1063340, but he seemed to figure his problem out. I'm closing SQLConnections, but when I go to my Microsoft SQL Server, Management, Current Activity, Process Info, the 2 processes I opened are shown with a status of sleeping. I know .Close is being run because I'm watching it when I debug. Here's my entire login page with the connections being closed in bold. Any ideas? Thanks.

Imports System.IO
Imports System.Data.sqlclient

Public Class Login
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "
    ...
#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            Session.Clear()
            LoadAppINI()
        End If

        SetFocus(txtUsername)
    End Sub

    Private Sub LoadAppINI()
        ...
        <SET Session("ConnectionString")>
        ...
    End Sub

    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnSubmit.Click
        If CheckLogin() Then
            Server.Transfer(txtStartingPage.Text, True)
        End If
    End Sub

    Private Function CheckLogin() As Boolean
        Dim sSQL As String
        Dim adoxConn As SqlConnection
        Dim adoxCmd As SqlCommand
        Dim adoxReader As SqlDataReader
        Try
            adoxConn = New SqlConnection
            adoxConn.ConnectionString = Session("ConnectionString")
            adoxConn.Open()

            sSQL = "SELECT u.ID, u.LogoImage, u.AdImage, st.SecurityType, st.StartingPage "
            sSQL &= "FROM Users u "
            sSQL &= "   LEFT JOIN SecurityType st ON u.SecurityTypeID = st.ID "
            sSQL &= "WHERE u.UserName = '" & txtUsername.Text.Replace("'", "''") & "' AND "
            sSQL &= "   u.Password = '" & txtPassword.Text.Replace("'", "''") & "' "

            adoxCmd = New SqlCommand(sSQL, adoxConn)
            adoxReader = adoxCmd.ExecuteReader()

            If adoxReader.Read Then
				...
                <SET SESSION VARIABLES>
                ...
            Else
                lblFeedback.Text &= "Invalid Login - Try Again"
                CheckLogin = False
            End If
        Catch ex As Exception
            lblFeedback.Text &= "Problem accessing SQL Server.  " & ex.Message & "  "
        Finally
            If Not adoxReader Is Nothing Then adoxReader.Close()
            [b]If Not adoxConn Is Nothing Then adoxConn.Close()[/b]
        End Try
    End Function

    Private Sub UpdateLastLogin(ByVal iUserID As Integer)
        Dim sSQL As String
        Dim adoxConn As SqlConnection
        Dim adoxCmd As SqlCommand

        Try
            adoxConn = New SqlConnection
            adoxConn.ConnectionString = Session("ConnectionString")
            adoxConn.Open()

            sSQL = "UPDATE Users "
            sSQL &= "SET LastLogin = GETDATE() "
            sSQL += "WHERE ID = " & iUserID & " "

            adoxCmd = New SqlCommand(sSQL, adoxConn)
            adoxCmd.ExecuteNonQuery()
        Catch ex As Exception
			'Do nothing
        Finally
            [b]If Not adoxConn Is Nothing Then adoxConn.Close()[/b]
        End Try
    End Sub
End Class

 

[jonbatts]

It would appear as though my understanding of how SQL Server and ASP.NET interact is lacking. Apparently the Sleeping Process connected to the database will remain until aspnet_wp.exe kills it, or it times out. However, my application will continue to use that process when I open and close connections in my application. If I open five connections in order, closing each before opening the next one, I will end up with only one sleeping process. If I open five connections all at once then I will end up with 5 sleeping processes. Hope this is helpful for someone else.

 

[TipGiver]

I will change a little your UpdateLastLogin sub and you see if it works. If it is ok, do the same changes to the function.

    Private Sub UpdateLastLogin(ByVal iUserID As Integer)
        Dim sSQL As String = "UPDATE Users "
        Dim adoxConn As SqlConnection = Nothing
        Dim adoxCmd As SqlCommand = Nothing

        Try
            adoxConn = New SqlConnection
            adoxConn.ConnectionString = Session("ConnectionString")

            adoxConn.Open()

            sSQL &= "SET LastLogin = GETDATE() "
            sSQL &= "WHERE ID = " & iUserID & " "

            adoxCmd = New SqlCommand(sSQL, adoxConn)
            adoxCmd.ExecuteNonQuery()

            adoxConn.Close()
        Catch ex As Exception
            'Do nothing
        Finally
            adoxConn.dispose()
            adoxConn = Nothing
        End Try
    End Sub