[dba-VB] Close DB Connection

Hi Shamil,

Thanks for that, but it didn't work. I've traced it down and finally found
the he offending code which is below. Can you see anywhere that I can set
the connection to nothing? Like I said, you can't set the connection to
nothing when it's within a Using Statement. Funnily enough, you can set the
OleDbDataReader to nothing though...

    Public Sub AppendTempDataToMainTbl(ByVal strUniColumnName As String)
        Dim sourceDbFullPath As String = App_Path() & "TempImport.mdb"
        Dim destionationDbFullPath As String = GetIniSetting("DBPath",
"SystemSettings")

        Try
            Using sourceConnection As New
OleDbConnection(GetSourceConnectionString)
                sourceConnection.Open()
                Dim sql1 As String = "SELECT * FROM tblTempImport WHERE
TempID > 10"
                Dim cmd1 As New OleDbCommand(sql1, sourceConnection)
                Dim rdr As OleDbDataReader = cmd1.ExecuteReader()

                Using destinationConnection As New
OleDbConnection(GetDestConnectionString)
                    destinationConnection.Open()

                    While rdr.Read()
                        Dim sql2 As String = String.Format("INSERT INTO
tblMainData (HeaderID, UnitID, RecDate, RecTime, RecDateTime, " & _
                                                           "Celsius) values
(@HeaderID, at UnitID, at RecDate, at RecTime, at RecDateTime, at Celsius)")
                        Dim cmd2 As New OleDbCommand(sql2,
destinationConnection)

                        cmd2.Parameters.Add("@HeaderID",
OleDbType.Integer).Value = gHeaderID
                        cmd2.Parameters.Add("@UnitID",
OleDbType.Integer).Value = gUnitID
                        cmd2.Parameters.Add("@RecDate",
OleDbType.Date).Value = rdr("RecDate").ToString()
                        cmd2.Parameters.Add("@RecTime",
OleDbType.Date).Value = rdr("RecDate").ToString()
                        cmd2.Parameters.Add("@RecDateTime",
OleDbType.Date).Value = rdr("RecDate").ToString()
                        cmd2.Parameters.Add("@Celsius",
OleDbType.Double).Value = rdr(strUniColumnName).ToString()
                        cmd2.ExecuteNonQuery()
                    End While
                    destinationConnection.Close()
                    destinationConnection.Dispose()
                    If destinationConnection.State.ToString IsNot Nothing
Then
                        destinationConnection.Close()
                        destinationConnection.Dispose()
                        'destinationConnection = Nothing
                    End If

                End Using

                rdr.Close()
                rdr = Nothing
                sourceConnection.Close()
                sourceConnection.Dispose()
                'sourceConnection = Nothing
                If rdr IsNot Nothing Then
                    rdr.Close()
                    rdr.Dispose()
                    rdr = Nothing
                End If
            End Using
        Finally

        End Try

    End Sub

Thnaks

Vlad


-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Wednesday, 1 December 2010 12:31 AM
To: 'Discussion concerning Visual Basic and related programming issues.'
Subject: Re: [dba-VB] Close DB Connection

Hello Vlad --

Non-closed OleDbDataReader can keep connection  objects alive (not collected
by GC) - you can use the following coding style to have OleDbDataReader
always closed and connection objects released automatically:

Public Shared Sub Run()
	Dim rdr As OleDbDataReader = Nothing

	Dim sourceDbFullPath As String = "c:\temp\sourceDb.mdb"
	Dim destionationDbFullPath As String = "c:\temp\targetDb.mdb"

	Try
		Using sourceConnection As New
OleDbConnection(MakeAccDdbConnectionStr(sourceDbFullPath))
			sourceConnection.Open()
			Dim sql1 As String = "select [SourceField] from
[SourceTable]"
			Dim cmd1 As New OleDbCommand(sql1, sourceConnection)
			rdr = cmd1.ExecuteReader()

			Using destinationConnection As New
OleDbConnection(MakeAccDdbConnectionStr(destionationDbFullPath))
				destinationConnection.Open()

				While rdr.Read()
					Dim sql2 As String =
String.Format("insert into [MyTable](MyField) values ('{0}')",
rdr("SourceField").ToString())
					Dim cmd2 As New OleDbCommand(sql2,
destinationConnection)
					cmd2.ExecuteNonQuery()
				End While
			End Using
			rdr.Close()
			rdr.Dispose()
			rdr = Nothing
		End Using
	Finally
		If rdr IsNot Nothing Then
			rdr.Close()
			rdr.Dispose()
		End If
	End Try
End Sub

.NET Memory Profiler (http://memprofiler.com/) is a great tool, which helped
me to solve all the memory leakage issues in a large VS2008 solution having
about 40 projects.


Thank you.

--
Shamil
 
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of ACTEBS
Sent: 30 ?????? 2010 ?. 16:07
To: 'dba-vb'
Subject: [dba-VB] Close DB Connection

Hi Everyone,

 

I've got this issue with my app where the app keeps it's connection to the
Access DB open even after the application has finished it's task. I can't
figure out for the life of me where I've missed closing it in the various
Functions and Sub Routines.

 

I've ensured that cn.Close and cn = Nothing, has been included in all the
code where the DB is opened. The only place I can't include cn = Nothing is
when the connection is encapsulated within a Using Statement where for some
reason this is not allowed. Could this be the problem? If so, how can I
release the connection?

 

Also, is there any way of finding out which bit of code is keeping the
connection open?

 

Many Thanks

 

Vlad

 

 



_______________________________________________
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