Timeout expired. The timeout period elapsed prior to obtaining a connection...

[gchq]

Hi

In the process of moving a WinApp from .NET 2.0 and and a WebService from WSE 3.0 on W2K Server to WCF and Server 20078 R2 I am now getting timeout errors...

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

Here is an example

WCF Code

 Public Function InsertDataHOA(ByVal strSQL As String, ByVal LineNo As String, ByVal HOAID As String) As Boolean Implements IService1.InsertDataHOA
        Dim vDataConnect As DataConn = Nothing
        vDataConnect = New DataConn
        vDataConnect.HOA_ID = HOAID
        Try
            vDataConnect.EstablishFactoryConnection()
            vDataConnect.TransactionHandler(HASoftwareDataConn.TransactionType.Open)
            vDataConnect.ExecuteNonQuery(Data.CommandType.Text, strSQL)
            vDataConnect.TransactionHandler(HASoftwareDataConn.TransactionType.Commit)
        Catch ex As Exception
            vDataConnect.TransactionHandler(HASoftwareDataConn.TransactionType.Rollback)
            EmailError(ex.Message, LineNo)
            vDataConnect = Nothing
            Return False
            Exit Function
        End Try
        vDataConnect.CloseFactoryConnection()
        vDataConnect = Nothing
        Return True
    End Function

Application code:-

Public Function CustomerOpeningBalances(ByVal DT As DataTable) As Boolean
        If CheckBulkUpdate() = False Then
            Return False
        End If

        Dim vAmount As Double = 0.0
        Dim vType As String = ""
        For Each row As DataRow In DT.Rows
            vService = New ServiceReference1.Service1Client
            vAmount = row("Amount")
            Dim vDate As Date = row("OB_Date")
            row("OB_Date") = SaveAsUTC(vDate)
            If vAmount < 0 Then
                vType = "Credit"
                Dim vAmountText As String = CType(vAmount, Double)
                vAmountText = vAmountText.Replace("-", "")
                vAmount = CType(vAmountText, String)
            Else
                vType = "Debit"
            End If
            strSQL = "INSERT INTO A_Sales_Ledger (" & vType & ", S_Description, Document_Date, Customer_ID, Type) VALUES ("
            strSQL += "'" & vAmount & "', "
            strSQL += "'Opening Balance', "
            strSQL += "'" & Format(row("OB_Date"), "yyyy-MM-dd") & "', "
            strSQL += "'" & row("CustomerID") & "', "
            strSQL += "'OB', )"
            If vService.InsertDataHOA(strSQL, "1208 Accounting " & strSQL, Form1.vCurrentHOA) = False Then
                Return False
            End If

            'Make Nominal Ledger and control account transactions

            strSQL = "INSERT INTO A_Control (Control_ID, C_Description, " & vType & ") VALUES ("
            strSQL += "'1100', "
            strSQL += "'Opening Balance', "
            strSQL += "'" & vAmount & "')"
            If vService.InsertDataHOA(strSQL, "1233 Accounting " & strSQL, Form1.vCurrentHOA) = False Then
                Return False
            End If

            strSQL = "INSERT INTO A_Nominal (Type, Ref, Details, " & vType & ", Nominal_Code, Item_Date) VALUES ("
            strSQL += "'OB', "
            strSQL += "'Opening Bal', "
            strSQL += "'Opening balance', "
            strSQL += "'" & vAmount & "', "
            strSQL += "'1100', "
            strSQL += "'" & Format(row("OB_Date"), "yyyy-MM-dd") & "')"
            If vService.InsertDataHOA(strSQL, "1243 Accounting " & strSQL, Form1.vCurrentHOA) = False Then
                Return False
            End If

            Dim vRev As String = ""
            If vType = "Debit" Then
                vRev = "Credit"
            Else
                vRev = "Debit"
            End If
            strSQL = "INSERT INTO A_Nominal (Type, Ref, Details, " & vRev & ", Nominal_Code, Item_Date) VALUES ("
            strSQL += "'OB', "
            strSQL += "'Opening Bal', "
            strSQL += "'Opening balance', "
            strSQL += "'" & vAmount & "', "
            strSQL += "'9997', "
            strSQL += "'" & Format(row("OB_Date"), "yyyy-MM-dd") & "')"
            If vService.InsertDataHOA(strSQL, "1260 Accounting " & strSQL, Form1.vCurrentHOA) = False Then
                Return False
            End If
            vService = Nothing
        Next

        If Not A_Sales_Ledger Is Nothing Then
            AccountingDS.Tables.Remove(A_Sales_Ledger)
            A_Sales_Ledger = Nothing
        End If
        If Not A_Control Is Nothing Then
            AccountingDS.Tables.Remove(A_Control)
            A_Control = Nothing
        End If
        If Not A_Nominal Is Nothing Then
            AccountingDS.Tables.Remove(A_Nominal)
            A_Nominal = Nothing
        End If
        ReturnNewDataSet("A_Sales_Ledger")
        ReturnNewDataSet("A_Control")
        ReturnNewDataSet("A_Nominal")
        Return True
    End Function

Any ideas?

Thanks