Hi there,

I receive the following message when I run my function that executes an Oracle Stored Procedure:
The OracleParameter is already contained by another OracleParameterCollection

Does anyone know why this is happenning?

Below the code I used:

    Private Overloads Function Oracle_ExecuteProcedure(ByVal ProcedureName As String, ByVal Parms As OracleClient.OracleParameter()) As Integer
        Dim intRowsAffected As Integer
        Dim OracleParm As New OracleClient.OracleParameter

        If MyConnection.State <> ConnectionState.Open Then
            MyConnection.Open()
        End If

        'Configure the MySqlCommand object
        MySqlCommand = New System.Data.OracleClient.OracleCommand(ProcedureName, MyConnection)
        With MySqlCommand
            .CommandType = CommandType.StoredProcedure 'Set type to Stored Procedure  
            .CommandText = ProcedureName 'Specify procedure to run

            'Clear any previous parameters from the command object
            .Parameters.Clear()           

            'Loop through parmameter collection, if defined, adding parameters to the command object
            If Not (Parms Is Nothing) Then
                For Each OracleParm In Parms
                    MySqlCommand.Parameters.Add(OracleParm)
                Next
            End If
        End With

        'Execute the procedure
        intRowsAffected = MySqlCommand.ExecuteNonQuery()

        Return intRowsAffected 'Return the number of rows affected by procedure
    End Function

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
 Dim sFiles(2) as String
        Dim Parms1(1) As OracleClient.OracleParameter
        Dim RowsGenerated As Integer

 sFiles(0)="123.txt"
 sFiles(1)="abc.txt"

        Parms1(0) = New OracleClient.OracleParameter("FILENAME", sFiles(0))
        Parms1(1) = New OracleClient.OracleParameter("DATESTR", DATEI)
        RowsGenerated = Oracle_ExecuteProcedure("UPDATE_R151BTS", Parms1) 'This one is work fine

        Parms1(0) = New OracleClient.OracleParameter("FILENAME", sFiles(1))
        RowsGenerated = Oracle_ExecuteProcedure("UPDATE_R153", Parms1) 'This one generates the error
    End Sub

oh. . eff all that!

FORGET YOUR VBSUX!!! IT WAS WRONG!!!

=======================

Imports System
Imports System.Collections.Generic

Public Class ParamDictionary
    Inherits Dictionary(Of String, Object)
End Class

Public Class TheDoer
    Private Shared aConn As System.Data.OracleClient.OracleConnection

    Public Shared Sub JustDoIt(ByVal ADate As DateTime)
        Dim params As New ParamDictionary()
        params("FILENAME") = "123.txt"
        params("DATESTR") = ADate
        WhatToDo("UPDATE_R151BTS", params)
        params("FILENAME") = "abc.txt"
        WhatToDo("UPDATE_R153", params)
    End Sub

    Private Shared Function WhatToDo(ByVal procName As String, ByVal paramDic As ParamDictionary) as integer
        If aConn.State <> ConnectionState.Open Then
            aConn.Open()
        End If

        Dim aCmd = New System.Data.OracleClient.OracleCommand(procName, aConn)
        aCmd.CommandType = CommandType.StoredProcedure
        For Each s As String In paramDic.Keys
            aCmd.Parameters.AddWithValue(s, paramDic(s))
        Next
        Return aCmd.ExecuteNonQuery()
    End Function

End Class

[BAD ANSWER DELETED]

use this to move your params. .  yes its in the superior C# but I am sure you can figure it out:

private void SwapList(System.Collections.IList src, System.Collections.IList dest)
{
    dest.Clear();
    while (src.Count > 0)
    {
        object obj = src[0];
        src.Remove(obj);
        dest.Add(obj);   
    }
}

god, vb is a total dog! and forget the way you did things in vbsux. . . stay aray from arrays. . . use collections!

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
 Dim sFiles(2) as String
        Dim Parms1 As new OracleClient.OracleParameterCollection()
        Dim RowsGenerated As Integer

 sFiles(0)="123.txt"
 sFiles(1)="abc.txt"

        Parms1.AddWithValue("FILENAME", sFiles(0))
        Parms1.AddWithValue("DATESTR", DATEI)
        RowsGenerated = Oracle_ExecuteProcedure("UPDATE_R151BTS", Parms1) 'This one is work fine

        Parms1 =  new OracleClient.OracleParameterCollection()
        Parms1.AddWithValue("DATESTR", sFiles(1)))
        Parms1.AddWithValue("DATESTR", DATEI)
        RowsGenerated = Oracle_ExecuteProcedure("UPDATE_R153", Parms1)     End Sub

and your main function

Private Overloads Function Oracle_ExecuteProcedure(ByVal ProcedureName As String, ByVal Parms As OracleClient.OracleParameterCollection) As Integer

        If MyConnection.State <> ConnectionState.Open Then
            MyConnection.Open()
        End If

        MySqlCommand = New System.Data.OracleClient.OracleCommand(ProcedureName, MyConnection)
        With MySqlCommand
            .CommandType = CommandType.StoredProcedure    
            .CommandText = ProcedureName  
            SwapList(.Parameters, Params) Then
        End With

        return  MySqlCommand.ExecuteNonQuery()

    End Function

put this in your static class that contains your library functions

public Sub SwapList(ByVal src as System.Collection.IList, ByVal Dest as System.Collection.IList)  
            if src is nothing then return   
            while src.Count > 0   
                dim obj as object = src(0)   
                src.Remove(obj)   
                dest.Add(obj)   
            end  while     
end sub
                
              
           

oh. . eff all that!

FORGET YOUR VBSUX!!! IT WAS WRONG!!!

=======================

Imports System
Imports System.Collections.Generic

Public Class ParamDictionary
    Inherits Dictionary(Of String, Object)
End Class

Public Class TheDoer
    Private Shared aConn As System.Data.OracleClient.OracleConnection

    Public Shared Sub JustDoIt(ByVal ADate As DateTime)
        Dim params As New ParamDictionary()
        params("FILENAME") = "123.txt"
        params("DATESTR") = ADate
        WhatToDo("UPDATE_R151BTS", params)
        params("FILENAME") = "abc.txt"
        WhatToDo("UPDATE_R153", params)
    End Sub

    Private Shared Function WhatToDo(ByVal procName As String, ByVal paramDic As ParamDictionary) as integer
        If aConn.State <> ConnectionState.Open Then
            aConn.Open()
        End If

        Dim aCmd = New System.Data.OracleClient.OracleCommand(procName, aConn)
        aCmd.CommandType = CommandType.StoredProcedure
        For Each s As String In paramDic.Keys
            aCmd.Parameters.AddWithValue(s, paramDic(s))
        Next
        Return aCmd.ExecuteNonQuery()
    End Function

End Class

Forgot a line

Imports System
Imports System.Configuration
Imports System.Collections.Generic

Public Class ParamDictionary
    Inherits Dictionary(Of String, Object)
End Class

Public Class TheDoer

    Public Shared Sub JustDoIt(ByVal ADate As DateTime)
        Dim params As New ParamDictionary()
        params("FILENAME") = "123.txt"
        params("DATESTR") = ADate
        WhatToDo("UPDATE_R151BTS", params)
        params("FILENAME") = "abc.txt"
        WhatToDo("UPDATE_R153", params)
    End Sub

    Private Shared Function WhatToDo(ByVal procName As String, ByVal paramDic As ParamDictionary)
        Using aConn As New System.Data.OracleClient.OracleConnection(ConfigurationManager.ConnectionStrings(0).ConnectionString)
            aConn.Open()
            Using aCmd As New System.Data.OracleClient.OracleCommand(procName, aConn)
                aCmd.CommandType = CommandType.StoredProcedure
                For Each s As String In paramDic.Keys
                    aCmd.Parameters.AddWithValue(s, paramDic(s))
                Next
                Return aCmd.ExecuteNonQuery()
            End Using
        End Using
    End Function
End Class

What a weird problem - I just struck it myself.

Thanks for the hints!

I eventually used the code belowtomove (it's not really a swap) the Parameters between the two ParameterCollections.