[dba-VB] closing your connections

I'm not sure, I am closing and disposing my objects as I posted in my first
post...  but if I monitor the sql server I notice that the connection is
still active.(in awaiting sleep status but connected)  :-/

-Francisco
http://bit.ly/sqlthis   | Tsql and More...
http://db.tt/JeXURAx | Drop Box, Storage in the Cloud (free)




On Wed, Jun 8, 2011 at 10:22 AM, David McAfee <davidmcafee at gmail.com> wrote:

> This is what I do with SQLCE when I need to modify the SDF, which shouldn't
> differ too much from SQL Server:
>
>        Dim strCEconn As String = "Data Source = " & strPath & strFileName
>        Dim CEconn As SqlServerCe.SqlCeConnection = New
> SqlServerCe.SqlCeConnection(strCEconn)
>        CEconn.Open()
>        Dim CEcmd As SqlServerCe.SqlCeCommand = CEconn.CreateCommand
>
>        CEcmd.CommandText = "CREATE TABLE InvoiceOrderJunct([InvNo]
> [nvarchar](8) NULL, [OrderID] [int], [AMNo] [nvarchar](10) NULL)"
>        CEcmd.ExecuteNonQuery()
>
>        CEcmd.Dispose()
>        CEconn.Close()
>        CEconn.Dispose()
>
>
> I had to do this in one of my webservice functions, when we had weird
> issues:
>
>
>   <WebMethod()> _
> Public Function InsertInvHdr(ByVal strInput As String, ByVal dsIn As
> DataSet) As String
>
>        Dim errNum As Integer
>        Dim errMsg As String
>        Dim myConnection As SqlConnection
>        Dim myCommand As SqlCommand
>        Dim row As DataRow
>
>        Call fUnSecureString(strInput)
>
>        Try
>            'Following parameters are determined from strInput hash saved as
> global variables
>            myConnection = New SqlConnection("server=" & ServerName &
> ";uid=" & strUid & ";pwd=" & strPw& ";database=" & strDBname)
>            myConnection.Open()
>            myCommand = New SqlCommand
>            myCommand.Connection = myConnection
>
>            For Each row In dsIn.Tables(0).Rows
>                myCommand.CommandText = "EXEC stpInsertIntoInvHdr '" &
> row("ICNo").ToString() & "', '" & row("StoreNo").ToString() & "', '" &
> row("CustNo").ToString()
>                myCommand.ExecuteNonQuery()
>            Next
>
>            Return "InvHdr data inserted"
>            'myConnection.Close()
>
> '******************************************************************
>            ' Added the 2 IF statements below on 8/4/2010 to see if it
> clears out sleeping processes
>            If (Not (myCommand) Is Nothing) Then
>                myCommand.Dispose()
>                myCommand = Nothing
>            End If
>            If (Not (myConnection) Is Nothing) Then
>                myConnection.Close()
>            End If
>            '********************************************************
>        Catch ex As Exception
>            errNum = Err.Number
>            errMsg = ex.Message
>            Return "SQL/WS Error: WS.InsertInvHdr - " & errNum & ":" &
> errMsg
>            'Finally
>            '   myConnection.Close()
>        End Try
>    End Function
>
>
>
> On Wed, Jun 8, 2011 at 8:47 AM, Francisco Tapia <fhtapia at gmail.com> wrote:
>
> > so I have a vb.net app (visual studio 2008) and I am making some sproc
> > calls
> > like so:
> >
> >
> >  conn = New SqlConnection(My.MySettings.Default.dbConn)
> >            cmd = New SqlCommand("dbo.stp_PCO", conn)
> >            cmd.CommandType = CommandType.StoredProcedure
> >            conn.Open()
> >            da.SelectCommand = cmd
> >            da.Fill(dtCTable)
> >
> > I've also tried closing my connections after usage by using the following
> > closing statments
> >
> > dtCtable.Dispose()
> > dtClockTable = nothing
> > cmd.Dispose()
> > cmd = nothing
> > da.Dispose()
> > da = nothing
> > conn.close()
> > conn.Dispose()
> > conn = nothing
> >
> > but when I check my sql server I still see sleeping entires in the
> activity
> > monitor suggesting that a connection is still active.  How do I sever the
> > connection completely?  I know a connection is alive and well because I
> > noticed that if I completely close the application the connection goes
> from
> > sleeping to gone.  I want my connections to close once the data has been
> > retrieved to allow the server to reuse that memory if needed.
> >
> > in vb 6 it used to be so simple with just ado :(
> >
> > your help is of course appreciated!
> >
> >
> > -Francisco
> > _______________________________________________
> > dba-VB mailing list
> > dba-VB at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-vb
> > http://www.databaseadvisors.com
> >
> >
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>
>