Oracle.DataAccess.Client.OracleException ORA-01008: not all variables bound at Oracle.DataAccess.Client

Question

I've having this error and I can't explain it:

Oracle.DataAccess.Client.OracleException ORA-01008: not all variables bound at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader() at gestao.GestaoDefault.GeraSqlDataSourceDropDownDisciplinasSelectCommand(Int32 selectValue) in c:\inetpub\MiniSites\inscricoes\gestao\Default.aspx.cs:line 501

My query:

SELECT DISTINCT
       cse.t_tbdiscip.cd_discip,
       cse.t_tbdiscip.ds_discip
  FROM cse.t_tbdiscip, cse.t_turma, cse.t_plandisc
 WHERE cse.t_plandisc.cd_curso = :CD_CURSO
   AND cse.t_turma.cd_curso = :CD_CURSO
   AND cse.t_plandisc.cd_activa = 'S'
   AND cse.t_turma.estado = 'S'
   AND cse.t_tbdiscip.cd_discip = cse.t_plandisc.cd_discip
   AND cse.t_tbdiscip.cd_discip = cse.t_turma.cd_discip
   AND cse.t_turma.cd_a_s_cur IS NOT NULL
   AND cse.t_turma.cd_lectivo = :CD_LECTIVO
 ORDER BY cse.t_tbdiscip.ds_discip

As you can see I have to vars to the query. My code:

OracleConnection oracleSqlConnection = new OracleConnection(ConOracleString);
try
{
    // Just to see if any value is passed to the query
    LiteralMensagens.Text += String.Format("Curso : " + curso + "<br /><br />");
    LiteralMensagens.Text += String.Format("Lectivo : " + lectivo + "<br /><br />");
    LiteralMensagens.Text += String.Format("query : " + query + "<br /><br />");

    oracleSqlConnection.Open();
    OracleCommand cmdOracle = new OracleCommand(query, oracleSqlConnection);
    cmdOracle.CommandType = CommandType.Text;

    cmdOracle.Parameters.Clear();
    cmdOracle.Parameters.Add(":CD_CURSO", OracleDbType.Decimal).Value = curso;
    cmdOracle.Parameters.Add(":CD_LECTIVO", OracleDbType.Varchar2).Value = lectivo;

    OracleDataReader oracleDataReader = cmdOracle.ExecuteReader();
    while (oracleDataReader.Read())
    {
        LiteralMensagens.Text += string.Format((String)oracleDataReader["DS_DISCIP"]);
    }
}
catch (Exception ex)
{

From my code I bound my vars.

From the Oracle DB:

CD_LECTIVO- VARCHAR2(7)

CD_CURSO- NUMBER(4)

I've checked here and I think I'm using the correct types http://msdn.microsoft.com/en-us/library/yk72thhd.aspx

What can be wrong?

Community · Accepted Answer · 2017-05-23 10:27:58Z

4

Problem solved... Added this:

cmd.BindByName = true;

Found this reading this: similar issue on stackoverflow

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Jan 13, 2014 at 9:02

António Godinho

711 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mauro Cerutti · Accepted Answer · 2014-01-10 15:57:26Z

0

You should change you code like this (omitting the ':' from the parameter name):

// ...
cmdOracle.Parameters.Add("CD_CURSO", OracleDbType.Decimal).Value = curso;
cmdOracle.Parameters.Add("CD_LECTIVO", OracleDbType.Varchar2).Value = lectivo;
//...

answered Jan 10, 2014 at 15:57

Mauro Cerutti

6944 silver badges9 bronze badges

Comments

António Godinho · Accepted Answer · 2014-01-13 08:46:16Z

Still not working now something really strange... I'm bounding 2 vars, if I comment one of them and insert the value on the query (doesn't mather witch one) it works perfectly... The cmdOracle.Parameters.Add is not accepting more than 1 value... Does this makes sense to anyone?

This works replacing ":CURSO" with a var on the query string:

String query = String.Format("SELECT DISTINCT CSE.T_TBDISCIP.CD_DISCIP, CSE.T_TBDISCIP.DS_DISCIP " +
"FROM CSE.T_TBDISCIP, CSE.T_TURMA, CSE.T_PLANDISC " +
"WHERE (CSE.T_PLANDISC.CD_CURSO = " + curso + ") " +
"AND (CSE.T_TURMA.CD_CURSO = " + curso + ") " +
"AND (CSE.T_PLANDISC.CD_ACTIVA='S') " +
"AND (CSE.T_TURMA.ESTADO='S') " +
"AND (CSE.T_TBDISCIP.CD_DISCIP=CSE.T_PLANDISC.CD_DISCIP) " +
"AND (CSE.T_TBDISCIP.CD_DISCIP=CSE.T_TURMA.CD_DISCIP) " +
"AND (CSE.T_TURMA.CD_A_S_CUR IS NOT NULL) " +
"AND (CSE.T_TURMA.CD_LECTIVO = :CD_LECTIVO) " +
"ORDER BY CSE.T_TBDISCIP.DS_DISCIP");

Collectives™ on Stack Overflow

Oracle.DataAccess.Client.OracleException ORA-01008: not all variables bound at Oracle.DataAccess.Client

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related