ADO 记录集在创建 16 次后开始建立新连接

Question

这可能是我遇到过的最困惑的错误。

这是一些会产生错误的VB代码。我对我不必创建新的记录集这一事实不感兴趣，这是故意触发错误的。

Public Module ConnectionSetup
Public ActiveTDConnection As ADODB.Connection
Public Sub ConnectToTD(ByVal userName As String, ByVal Password As String, ByVal dsn As String)
    ActiveTDConnection = New ADODB.Connection
    ActiveTDConnection.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=True;" & _
              "User ID=" & userName & ";Password=" & Password & ";" & _
              "Data Source=" & dsn & ";Mode=Read;" & _
              "UID=" & userName & ";AUTHENTICATION=LDAP;Initial Catalog=(Default)"
    ActiveTDConnection.CommandTimeout = 0
    ActiveTDConnection.Open(ActiveTDConnection.ConnectionString)
End Sub
End Module
Module Module1
 Sub Main()
    ConnectionSetup.ConnectToTD("XXX", "XXX", "XXX")

    Dim DB_qry As String
    Dim sessionQry As String
    Dim i As Integer = 1
    Dim sessionRS As New ADODB.Recordset

    DB_qry = "SELECT TOP 1 * FROM dbc.databases"
    sessionQry = "SELECT COUNT(*) FROM dbc.SessionInfo WHERE userName = 'MyUserName'"

    Do While i <= 18
        Dim DB_rs As New ADODB.Recordset
        DB_rs.Open(DB_qry, ActiveTDConnection)
        sessionRS.Open(sessionQry, ActiveTDConnection)
        ' This starts going up by 1 on each loop, after 16.
        Debug.Print(sessionRS.Fields.Item(0).Value)
        Do While Not DB_rs.EOF
            MsgBox(i)
            i += 1
            DB_rs.MoveNext()
        Loop
        DB_rs.Close()
        DB_rs = Nothing
        sessionRS.Close()
     Loop
 End Sub

在第 16 次迭代之后，每个新打开的 recordSet 都会生成一个新连接。所以我的代码随机开始冒着最大化会话限制的风险。

1.） 为什么会这样？

2.）我该如何预防？

Answer 1

 Sub Main()
    ConnectionSetup.ConnectToTD("XXX", "XXX", "XXX")

    Dim DB_qry As String
    Dim sessionQry As String
    Dim i As Integer = 1
    Dim sessionRS As New ADODB.Recordset
    DB_qry = "SELECT SESSION"
    sessionQry = "SELECT COUNT(*) FROM dbc.SessionInfo WHERE userName = 'MyUserName'"

    Do While i <= 18
        Dim DB_rs As New ADODB.Recordset
        DB_rs.Open(DB_qry, ActiveTDConnection)
        sessionRS.Open(sessionQry, ActiveTDConnection)
        ' This starts going up by 1 on each loop, after 16.
        'Debug.Print(sessionRS.Fields.Item(0).Value)
        Do While Not DB_rs.EOF
            MsgBox("Sessions: " & sessionRS.Fields.Item(0).Value & " Cnt: " & i & " SID: " & DB_rs.Fields.Item(0).Value)
            i += 1
            DB_rs.MoveNext()
        Loop
        DB_rs.Close()
        DB_rs = Nothing
        sessionRS.Close()
     Loop
 End Sub