Problems with using ODP.NET provider

[sjoshi]

Hello All

I'm using this query

Select Distinct(prj.Name) as PrjName, prj.oid as PrjOid From
SITE61.PRJMGTProjectRoot prj,
SITE61.CORERELATIONDEST ic, SITE61.PRJMGTDatabase pd
Where prj.oid=ic.oidTarget
And ic.oid=pd.oid
And pd.oid= lant_oid


and getting a data-reader like this:

Dim cmdSQL As New OracleCommand
Dim conSQL As New OracleConnection
Dim drSQL As OracleDataReader

conSQL.ConnectionString = oRequest.ConnSetting
conSQL.Open()
cmdSQL.Connection = conSQL
cmdSQL.CommandText = oRequest.Command
cmdSQL.CommandType = oRequest.CommandType
cmdSQL.BindByName = True

AddParameters(cmdSQL, oRequest.Parameters) 'Add the prm with name
plant_oid and

'a RAW value. So far so good

'I then get an exception when I try to do the ExecuteReader
'Invalid parameter binding
'Parameter name: plant_oid

return cmdSQL.ExecuteReader(CommandBehavior.CloseConnection)

Any ideas what might be wrong here ?

thanks
Sunit

 

[Guest]

What does AddParameters do?

Strange thing about ODB...you have to add in the parameters in the correct
order and use the correct parameter names

 

[Guest]

Can you show how you are creating your parameters? How does
oRequest.Parameters get populated? You don't have to bind in order when
BindByName = true. Also, you might want to try posting this in the ODP.NET
forum here:

http://forums.oracle.com/forums/forum.jspa?forumID=146&start=0

-Valkyrie-MT

 

[sjoshi]

This is what I get in the log file:

[Logging-DEBUG]-8/31/2006 11:16:21 AM
[GetDataReader]
Select Distinct(prj.Name) as PrjName, prj.oid as PrjOid From
SP3DTRAIN_SITE.PRJMGTProjectRoot prj,
SP3DTRAIN_SITE.CORERELATIONDEST ic, SP3DTRAIN_SITE.PRJMGTDatabase pd
Where prj.oid=ic.oidTarget And ic.oid=pd.oid And pd.oid= lant_oid
[Exception]
Invalid parameter binding
Parameter name: plant_oid

In code I'm doing this.........!!!

This is the line that calls the Request object to add the parameter:

oRequest.AddParameter(param, DAO.DBDataType.DBGuid, plantOID,
ParameterDirection.Input);

Which adds it to an ArrayList in Request class

public void AddParameter( string paramName, DBDataType paramType,
object paramValue, ParameterDirection paramDirection)
{
Parameter buildParameter=null; //a generic parameter.
buildParameter = new Parameter(paramName, paramType,
paramValue, paramDirection);
m_colParameters.Add(buildParameter);
}

then in the main code I'm calling GetDataReader(sql, oConnection,
oRequest);
where the oConnection has the correct data-factory (in this case
Oracle) set.

So in OracleConcreteFactory I do this:

public override DAODataReader ExecuteDataReader(DAORequest request)
{
OracleCommand cmdSQL = new OracleCommand();
OracleConnection conSQL = new OracleConnection();
OracleDataReader drSQL = null;
DAODataReader rdr = new DAOConcDataReader(); //
try
{
if (request.ConnSetting == null) throw new
ArgumentException("Connecting string needs to be set");
conSQL.ConnectionString = request.ConnSetting;
conSQL.Open();
cmdSQL.Connection = conSQL;
cmdSQL.CommandText = request.Command;
cmdSQL.CommandType = request.CommandType;
AddParameters(cmdSQL, request.Parameters);
rdr.ReturnedDataReader =
cmdSQL.ExecuteReader(CommandBehavior.CloseConnection);
return rdr; //this is where it fails
}
catch { throw; }
finally { }
}

private void AddParameters(IDbCommand cmd, ArrayList paramList)
{
OracleParameter sqlParm = null;
foreach (DAORequest.Parameter prm in paramList)
{
sqlParm = new OracleParameter();
sqlParm.ParameterName = prm.ParamName;
sqlParm.OracleDbType = GetDBType(prm.ParamType);
sqlParm.Direction = prm.ParamDirection;
sqlParm.Value = prm.ParamValue;
cmd.Parameters.Add(sqlParm);
}
}

private OracleDbType GetDBType(DBDataType paramType)
{
if (paramType == DBDataType.DBString)
return OracleDbType.Varchar2;
else if (paramType == DBDataType.DBChar)
return OracleDbType.Char;
else if (paramType == DBDataType.DBInteger)
return OracleDbType.Int32;
else if (paramType == DBDataType.DBBit)
return OracleDbType.Single;
else if (paramType == DBDataType.DBDateTime)
return OracleDbType.Date;
else if (paramType == DBDataType.DBDecimal)
return OracleDbType.Decimal;
else if (paramType == DBDataType.DBMoney)
return OracleDbType.Double;
else if (paramType == DBDataType.DBImage)
return OracleDbType.Byte;
else if (paramType == DBDataType.DBGuid)
return OracleDbType.Raw;
else
throw new ArgumentException("OracelDBType value not found for " +
paramType.ToString());
}

thanks
Sunit

then when the