CODEWITHSUNDEEP
asp.netvb.netoracle10g
trelston
trelston

Reputation: 643

"System.ArgumentException: Invalid operation. The connection is closed ". I keep getting this error

I get this error on the server. I cannot replicate this on my development machine.

I get it when i call ExecuteReader, ExecuteScalar or when i try to fill a dataset.

I use an oracle database.

This, i think, increases when the load on the server increases. Im not sure.

i need help fixing this. Please let me know if you need anymore details.

The code for ExecuteScalar would be as follows

Public Function ExecuteScalar1(ByVal sExecuteString As String, ByVal sConnectString     As     String) As String
        Dim OrclCmd As New OracleCommand
        Try
            If OpenConnection(sConnectString) Then
                OrclCmd.CommandText = sExecuteString
                OrclCmd.CommandType = CommandType.Text
                OrclCmd.Connection = OrclConn
                ExecuteScalar_ = Convert.ToString(OrclCmd.ExecuteScalar())
                If ExecuteScalar_ Is System.DBNull.Value Then
                    ExecuteScalar_ = ""
                End If

            End If
        Catch ex As Exception
            Err.Raise(Err.Number, Err.Source, Err.Description)
        Finally
            Call CloseConnection()
        End Try
    End Function

Upvotes: 0

Views: 2178

Answers (1)

Jaymz
Jaymz

Reputation: 6348

What does your OpenConnection method do?

I'm assuming it opens a database connection on a completely independant OracleCommand object. I would suggest the simplest solution here would be to pass your OracleCommand object in ByRef to the OpenConnection method, allowing you to associate a connection and open it within the method.

Obviously, this will require you to change the OpenConnection method to take both a ConnectionString parameter, as well as an OracleCommand object by reference, the signature would be:

Public Sub OpenConnection(ByVal sConnectionString As String, ByRef orclCommand As OracleCommand)

This will allow you to work with the OracleCommand object in both methods, with them both referencing the same object - therefore allowing you to call .Open() on the connection, and have the connection open in both methods.

Re-reading your code...

You appear to have an object called OrclConn, which you assign to OrclCmd.Connection.

My psychic debugging tells me this is a Static object declared outside of this Function. If so, there's your problem - when multiple users are accessing this code, the OrclConn object can have it's connection closed by another user by the time the command is executed. Typical race condition on a shared object.

The solution would be to use a connection object local to the function:

Public Function ExecuteScalar1(ByVal sExecuteString As String, ByVal sConnectString As String) As String
    Dim OrclCmd As New OracleCommand
    Dim OrclConn As New OracleConnection
    Try
        OrclConn.ConnectionString = sConnectString
        OrclConn.Open() 
        'Add any connection initialisation here

        OrclCmd.CommandText = sExecuteString
        OrclCmd.CommandType = CommandType.Text
        OrclCmd.Connection = OrclConn

        ExecuteScalar_ = Convert.ToString(OrclCmd.ExecuteScalar())

        If ExecuteScalar_ Is System.DBNull.Value Then
            ExecuteScalar_ = ""
        End If
    Catch ex As Exception
        Err.Raise(Err.Number, Err.Source, Err.Description)
    Finally
        If OrclConn.State <> ConnectionState.Closed Then ' Can't remember if this is correct
            OrclConn.Close()                             ' Just be sure to call this
        End If
    End Try
End Function

Upvotes: 1

Related Questions