CODEWITHSUNDEEP
c#sqloracle-database
Joon w K
Joon w K

Reputation: 847

ORA-01008: not all variables bound in C#

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

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

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

Related Questions