How do I query a database using ADO from inside a class if the connection to the database was opened outside the class.

I have a Visual Basic 2008 project and have got to the banging-head-on-desk stage.

* The main form Form1 declares and opens a connection to an SQL database.
* Multiple copies of a class are created to deal with multiple incoming socket connections.
* Inside these classes I need to perform queries to the SQL database using this already-open database connection.

The code does not throw immediate errors when running, it just skips out of the class with no error.  Very odd.  I stepped through the code and saw it try to do the query and skip the rest of the code.

I have tried to strip out what I can and leave the important stuff.  Help of any kind would really be appreciated.

Ooh... after leaving it running for a bit longer, an error just popped up...

ContextSwitchDeadlock was detected

The CLR has been unable to transition from COM context 0x39efe8 to COM context 0x39f158 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

Line that caused the error: RS1.Open("SELECT * FROM tbl WHERE unique_id = 123456789", Form1.CN1, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)
Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

Public Class Form1
    Public Shared CN1 As ADODB.Connection
    Public Shared CM1 As ADODB.Command
    Public Shared RS1 As ADODB.Recordset
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CN1 = New ADODB.Connection
        CN1.CursorLocation = ADODB.CursorLocationEnum.adUseServer
        CN1.Open("Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=sqldb;" & "Server=ss\sql;Integrated Security=SSPI")
        listener.Start()
        While True
            Dim client As New connection(listener.AcceptTcpClient)
        End While
    End Sub
End Class
 
Public Class connection
    Public Sub New(ByVal client As TcpClient)
        ReDim data(_client.ReceiveBufferSize)
        _client.GetStream.BeginRead(data, 0, _
           CInt(_client.ReceiveBufferSize), _
           AddressOf ReceiveMessage, Nothing)
    End Sub
 
    Public Sub ReceiveMessage(ByVal ar As IAsyncResult)
        Dim RS1 = New ADODB.Recordset
        RS1.Open("SELECT * FROM tbl WHERE unique_id = 123456789", Form1.CN1, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)
        ' do stuff with the data here
        RS1.Close()
        RS1 = Nothing
        MsgBox("would be nice if it actually got this far!")
    End Sub
End Class