CODEWITHSUNDEEP
sqlvb.netoledb
Bottopia
Bottopia

Reputation: 11

Connection to database not closing in VB.NET

I have the function below. Sometimes after using it I need to delete the db and re-create it but this function keeps using the database (so it cannot be deleted) even though I ask it to drop the database and close all connections. Any help would greatly be appreciated.

Public Function alreadyindatabase(ByVal url As String) As Boolean
    url = url.Replace("'", "''")
    Dim connetionString As String
    Dim oledbCnn As OleDbConnection
    Dim oledbCmd As OleDbCommand
    Dim sql As String

    connetionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & datafile
    sql = "Select * from visitedurl Where [Field1]='" + url + "'"

    oledbCnn = New OleDbConnection(connetionString)
    Try
        oledbCnn.Open()
        oledbCmd = New OleDbCommand(sql, oledbCnn)
        Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()

        If oledbReader.Read Then
            'MsgBox("Found")
            Return True
        Else
            'MsgBox("Not found")
            Return False
        End If
        'While oledbReader.Read
        ' MsgBox(oledbReader.Item(0))
        'End While
        SqlConnection.ClearAllPools()
        sql = "DROP DATABASE [" & datafile & "]"
        oledbCmd = New OleDbCommand(sql)
        oledbCmd.ExecuteNonQuery()

        oledbCmd.Cancel()
        oledbReader.Close()
        oledbCmd.Connection.Close()
        oledbCnn.Close()
        oledbCmd.Dispose()
        oledbCnn.Dispose()


    Catch ex As Exception

    End Try


End Function

Upvotes: 1

Views: 7169

Answers (4)

Markus
Markus

Reputation: 22436

Your connection will not be closed in case of an error. You can wrap the relevant sections in a Using-block (basically adds a Try-Finally automatically) and by that also shorten your code.:

Public Function alreadyindatabase(ByVal url As String) As Boolean
    url = url.Replace("'", "''")
    Dim connetionString As String
    Dim sql As String

    connetionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & datafile
    sql = "Select * from visitedurl Where [Field1]='" + url + "'"

    Using oledbCnn = New OleDbConnection(connetionString)
        oledbCnn.Open()
        Using oledbCmd = New OleDbCommand(sql, oledbCnn)
            Using oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()
            If oledbReader.Read Then
                Return True
            Else
                Return False
            End If
        End Using
        SqlConnection.ClearAllPools()
        sql = "DROP DATABASE [" & datafile & "]"
        Using oledbCmd = New OleDbCommand(sql)
            oledbCmd.ExecuteNonQuery()
            oledbCmd.Cancel()
        End Using
    End Using
End Function

Upvotes: 0

user3094804
user3094804

Reputation: 1

You are closing the connection twice:

oledbCmd.Cancel()
oledbReader.Close()
oledbCmd.Connection.Close()  '<--------
oledbCnn.Close()             '<--------
oledbCmd.Dispose() 
oledbCnn.Dispose()

Maybe, it is generating an exception that prevents the connection Dispose call.

Try to comment de first connection close attempt (oledbCmd.Connection.Close())

Regards

Upvotes: 0

Sam Jenkins
Sam Jenkins

Reputation: 1314

You should have a finally section to your try catch and close the connection within there. Otherwise if an exception occurs the connection doesn't get closed.

If you have a look at this answer (https://stackoverflow.com/a/8557227/465404) it will show you how.

So you might want to have it similar to this...

Public Function alreadyindatabase(ByVal url As String) As Boolean
    url = url.Replace("'", "''")
    Dim connetionString As String
    Dim oledbCnn As OleDbConnection
    Dim oledbCmd As OleDbCommand
    Dim sql As String

    connetionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & datafile
    sql = "Select * from visitedurl Where [Field1]='" + url + "'"

    oledbCnn = New OleDbConnection(connetionString)
    Try
        oledbCnn.Open()
        oledbCmd = New OleDbCommand(sql, oledbCnn)
        Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()

        If oledbReader.Read Then
            'MsgBox("Found")
            Return True
        Else
            'MsgBox("Not found")
            Return False
        End If
        'While oledbReader.Read
        ' MsgBox(oledbReader.Item(0))
        'End While
        SqlConnection.ClearAllPools()
        sql = "DROP DATABASE [" & datafile & "]"
        oledbCmd = New OleDbCommand(sql)
        oledbCmd.ExecuteNonQuery()

        oledbCmd.Cancel()
        oledbReader.Close()
        oledbCmd.Connection.Close()
        oledbCnn.Close()
        oledbCmd.Dispose()
        oledbCnn.Dispose()


    Catch ex As Exception
        MsgBox(ex.ToString)
    Finally
         If oledbCnn.State = ConnectionState.Open Then oledbCnn.Close()
    End Try


End Function

Upvotes: 3

Tobberoth
Tobberoth

Reputation: 9527

Remove the try catch. You're probably getting an exception, then ignoring it, which means the connection never got to close and you never found out. Alternatively, you can add a finally clause where you close the connection, since it will run no matter what.

Upvotes: 0

Related Questions