Reputation: 847
The code I wrote is as follows:
var query = @"SELECT t0.ACT_TYPE, t0.ST_DT,t0.AC_ST_DT FROM ACTIVITY t0 WHERE (t0.AC_ST_DT > :p0 OR t0.ST_DT > :p0) AND t0.ACT_TYPE = :p1";
OracleConnection connection = new OracleConnection(cs);
try
{
connection.Open();
OracleCommand cmd = new OracleCommand(query, connection);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
OracleParameter pp = new OracleParameter();
DateTime date = new DateTime(2016, 01, 01);
OracleParameter dp = new OracleParameter();
dp.OracleDbType = OracleDbType.Date;
dp.ParameterName = "p0";
dp.Value = date;
cmd.Parameters.Add(dp);
var sp = new OracleParameter();
sp.OracleDbType = OracleDbType.Varchar2;
sp.ParameterName = "p1";
sp.Value = "SC";
cmd.Parameters.Add(sp);
var dt = new DataTable();
oda.Fill(dt);
cmd.Dispose();
}
finally
{
connection.Close();
}
Error I got is like ORA-01008: not all variables bound
Can someone help me out on this problem.
Upvotes: 1
Views: 2777
Reputation: 522817
I don't think it's possible to reuse positional parameters in this way. Instead use three bona fide parameters and then bind them:
var query = @"SELECT t0.ACT_TYPE, t0.ST_DT,t0.AC_ST_DT FROM ACTIVITY t0 WHERE (t0.AC_ST_DT > :p0 OR t0.ST_DT > :p1) AND t0.ACT_TYPE = :p2";
OracleConnection connection = new OracleConnection(cs);
try
{
connection.Open();
OracleCommand cmd = new OracleCommand(query, connection);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
OracleParameter pp = new OracleParameter();
DateTime date = new DateTime(2016, 01, 01);
OracleParameter dp1 = new OracleParameter();
dp1.OracleDbType = OracleDbType.Date;
dp1.ParameterName = "p0";
dp1.Value = date;
cmd.Parameters.Add(dp1);
OracleParameter dp2 = new OracleParameter();
dp2.OracleDbType = OracleDbType.Date;
dp2.ParameterName = "p1";
dp2.Value = date;
cmd.Parameters.Add(dp2);
var sp = new OracleParameter();
sp.OracleDbType = OracleDbType.Varchar2;
sp.ParameterName = "p2";
sp.Value = "SC";
cmd.Parameters.Add(sp);
var dt = new DataTable();
oda.Fill(dt);
cmd.Dispose();
}
finally
{
connection.Close();
}
Upvotes: 1