Cannot convert Oracle.DataAccess.Client.OracleParameterCollection to Oracle.DataAccess.Client.OracleParameter

Question

I am using an sql command to store a command text and some parameters:

myOracleCommand = New OracleCommand

With myOracleCommand

    .CommandType = CommandType.Text
    .CommandText = "MY QUERY"
    .Parameters.Add(New OracleParameter("Test1", OracleDbType.Varchar2, "05", ParameterDirection.Input))
    .Parameters.Add(New OracleParameter("Test2", OracleDbType.Varchar2,   DBNull.Value, ParameterDirection.Input))
    .Parameters.Add(New OracleParameter("Test3", OracleDbType.Int32, DBNull.Value, ParameterDirection.Input))
    .CommandTimeout = 60

End With  

' But after running this :

ExecuteReader("T3B", myOracleCommand.CommandText.ToString, Text, myOracleCommand.Parameters) 

Then I am getting this message error :

Cannot convert a value of type ' Oracle.DataAccess.Client.OracleParameterCollection ' to ' Table 1 dimension ( s) Oracle.DataAccess.Client.OracleParameter '

What should I do to fix the problem?

Answer 1

I believe, I know the answer for your issue. In this line

ExecuteReader("T3B", myOracleCommand.CommandText.ToString, Text, myOracleCommand.Parameters) 

you use myOracleCommand.Parameters, which is of type OracleParameterCollection, wile ExecuteReader expects array of OracleParameter.

What you need to do is convert this collection to an array and pass that array. And for that OracleParameterCollection has method CopyTo. So, it is trivial

Dim a(myOracleCommand.Parameters.Count - 1) As OracleParameter
myOracleCommand.Parameters.CopyTo(a, 0)
ExecuteReader("T3B", myOracleCommand.CommandText, Text, a) 

Note: ToString is not needed in CommandText.ToString() because command text is already a string