ODP.NET

I don't get the expected results when I run embedded parameterized SQL in c# if my where clause uses the primary key as a parameter. It does work if I use a date range or anything else. When I debug this and grab the SQL string and execute it in Toad it works fine. Any ideas why this won't work when I use the primary key as the parameter? I am using VS2008 and the latest version of the ODP.

The first code block works the second does not. The CLM_ID is of type number(20) in the db, but it has integers so I convert the number in the code to OracleDbType.Int16.

Any ideas.

This Works
/* Build the query */
string sql = "SELECT * FROM (SELECT C.CLM_ID " +
" FROM CLAIM.CLM C " +
" JOIN CLAIM.CUST CUST ON CUST.CUST_ID = C.CUST_ID " +
" where C.UTC_CREATED_ON >= to_date(:pStartDate)" +
" and C.UTC_CREATED_ON < to_date(:pEndDate) )" +
" WHERE ROWNUM < 200 ";
/* Add parameters. */
OracleCommand cmd = new OracleCommand(sql, objConn);
cmd.Parameters.Add(new OracleParameter(":pStartDate", OracleDbType.Date));
cmd.Parameters[":pStartDate"].Value = startDate;
cmd.Parameters.Add(new OracleParameter(":pEndDate", OracleDbType.Date));
cmd.Parameters[":pEndDate"].Value = endDate;

cmd.CommandType = CommandType.Text;
objConn.Open();
OracleDataReader dr = cmd.ExecuteReader(); // C#
dr.Read();

//if there are results put it in a data table.
DataTable dt = new DataTable();
dt.Load(dr, LoadOption.OverwriteChanges);
return dt;



This does not.

string sql = "SELECT * FROM (SELECT C.CLM_ID " +
" FROM CLAIM.CLM C " +
" JOIN CLAIM.CUST CUST ON CUST.CUST_ID = C.CUST_ID " +
" where to_number(C.CLM_ID) = :pClaimId )" +
" WHERE ROWNUM < 200 ";

OracleCommand cmd = new OracleCommand(sql, objConn);
cmd.Parameters.Add(new OracleParameter(":pClaimId",OracleDbType.Int16));
cmd.Parameters[":pClaimId"].Value = int.Parse(claimId);

cmd.CommandType = CommandType.Text;
objConn.Open();
OracleDataReader dr = cmd.ExecuteReader(); // C#
dr.Read();
//if there are results put it in a data table.
DataTable dt = new DataTable();
dt.Load(dr, LoadOption.OverwriteChanges);
return dt;