sjoshi
Thu Aug 31 11:17:36 CDT 2006
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= :plant_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
Valkyrie-MT wrote:
> 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" wrote:
>
> > 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= :plant_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
> >
> >