[RESOLVED] Connection will not close in .net

[Mikey]

As I watch in MySql Admin connections to server, I see the connection get made but it never disconnects. Is this different from VB6?

Thanks

 Private Sub WorkerThread1_EnterEvent(ByVal sender As System.Object, ByVal e As AxVBVoiceLib._WorkerThreadEvents_EnterEvent) Handles WorkerThread1.EnterEvent
        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
        conn = New ADODB.Connection
        rs = New ADODB.Recordset
        conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=ahmsys;UID=root;PWD=abc;"
        conn.Open()
        rs.Open("SELECT * FROM tenants WHERE ten_pin = '" & intPinNo & "'", conn)
        If rs.EOF Then
            lblValue.Text = "Invalid Pin Number"
        Else
            lblValue.Text = "Found Pin Number"
        End If
        rs.Close()
        rs = Nothing 
        conn.Close()
        conn = Nothing
    End Sub

[Shaggy Hiker]

I would guess that what you are seeing is an artifact of connection pooling. Your code is closing the connection, but to maximize efficiency, the app may quietly maintain the connection so that if you open the connection again soon afterwards, the overhead of the connection is reduced. I haven't studied it much, but re-establishing connections in .NET is faster because of connection pooling, and it may be what you are seeing. The speed comes from maintaining the connection (unless it gets kicked off).

[Mikey]

So there is no way to disconnect? If I don't disconnect the connection times out and the next timei go to the database it's locked out.

[Shaggy Hiker]

I haven't dealt with it, so I have no good answer for you. Look into connection pooling and you might find an answer. Otherwise, somebody who is familiar with that will probably wander by.

[Mikey]

Ok. Thanks. I'll hang around a bit.

[mendhak]

You should move over to ADO.NET instead of classic ADO. When you use ADO.NET, you'll be allowed to close the connection as well as Dispose it so that the connection can be returned to the connection pool.

[Mikey]

Good idea. I was just looking into it. Heck, something else to learn.

Thanks everyone.