Click here to Skip to main content
15,891,993 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have error Invalid operation. The connection is closed. when i use function ExecuteData.

Please advise. How should I do?

VB
Private Shared Conn As New OracleConnection
    Private Shared Trans As OracleTransaction
 
    Private Shared Sub OpenConnect(ByVal strConnectionString As String)
        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConnectionString
            .Open()
        End With
    End Sub
 
    Private Shared Sub CloseConnect()
        Conn.Close()
        Conn.Dispose()
    End Sub
 
    Public Overloads Shared Function ExecuteData(ByVal strCommandText As String, _
                                             ByVal ParameterCollection As OracleParameterCollection, _
                                             Optional ByVal strConnectionString As String = "") As ExecuteReturn
        If Conn.State = ConnectionState.Closed Then OpenConnect(strConnectionString)
        Dim oraCommand As New OracleCommand
        oraCommand.CommandText = strCommandText
        oraCommand.Connection = Conn
        If Not Trans Is Nothing Then
            oraCommand.Transaction = Trans
        End If
        Dim oraParam As OracleParameterCollection = ParameterCollection
        For Each param As OracleParameter In ParameterCollection
            oraCommand.Parameters.Add(param.ParameterName, param.OracleType).Value = param.Value
        Next
        Dim effect As New ExecuteReturn
        Try
            effect.intRowEffected = oraCommand.ExecuteNonQuery()
            If oraCommand.Transaction Is Nothing Then
                CloseConnect()
            End If
            Return effect
        Catch ex As Exception
            effect.intRowEffected = -1
            effect.strErrorMessage = ex.Message
            CloseConnect()
            Return effect
        End Try
    End Function
Posted

1 solution

The state may not be Closed or Open (it is a false dichotomy). Use If Conn.State != ConnectionState.Open instead
 
Share this answer
 
Comments
Toom TH 6-Jul-15 5:50am    
Andy ,you mean I change "If .State = ConnectionState.Open Then .Close()" to "If .State != ConnectionState.Open Then .Close()" on function OpenConnect.

thank you .
Andy Lanng 6-Jul-15 5:54am    
No, it's opening the connection that not working. replace this line: If Conn.State = ConnectionState.Closed Then OpenConnect(strConnectionString)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900