I’m having difficulty returning my ID using nextval, to later do the Insert in my table

Question

I’m having difficulty returning my ID using nextval, to later do the Insert in my table

     OleDbConnection cnx = new OleDbConnection(new AdministradorDAO().conexao);
     cnx.Open();

     string seq = ("select id_usuarios.nextval from dual");
     OleDbCommand cmdo = new OleDbCommand(seq, cnx);
     int id= (int)cmdo.ExecuteScalar(); 

     using (OleDbCommand cmd = cnx.CreateCommand())
     {
   // create command with placeholders
      cmd.CommandText = 
      "INSERT INTO USUARIOS"+
      "([ID], [MATRICULA],  [NOME], [SENHA], [NIVEL]),[MALETA],[EMAIL]) "+
      "VALUES(@id, @ma, @no, @se, @ni,@ma,@em)";

   // add named parameters
   cmd.Parameters.AddRange(new OleDbParameter[]
   {
       new OleDbParameter("@id", id),
       new OleDbParameter("@ma", txt_matricula.Text),
       new OleDbParameter("@no", txt_nome.Text),
       new OleDbParameter("@se", txt_senha.Text),
       new OleDbParameter("@ni", int.Parse(DropDownList_nivel.Text)),
       new OleDbParameter("@ma", int.Parse(txt_maleta.Text)),
       new OleDbParameter("@em", txt_email.Text),

   });

   cmd.ExecuteNonQuery();
}

Answer 1

Instead of passing the id as a parameter you could put directly into your Insert statement.

cmd.CommandText = 
  "INSERT INTO USUARIOS"+
  "([ID], [MATRICULA],  [NOME], [SENHA], [NIVEL]),[MALETA],[EMAIL]) "+
  "VALUES(id_usuarios.nextval, @ma, @no, @se, @ni,@ma,@em)";

Answer 2

The post is old but maybe the solution can still be useful for someone, follows:

To load the value of Sequence using Oracle and Oledb the way you did this correct in general, what may be happening is the return of Executescalar not be allowing casting, missed you say the exception on the Executescalar line. Finally how it should be done:

Loading the next ID

OleDbConnection cnx = new OleDbConnection("Provider = OraOLEDB.Oracle; Data Source = server001; User Id = XPTO_APLICACAO; Password=pas123");
cnx.Open();
string seq = ("select sq_atividade.nextval from dual");
OleDbCommand cmdo = new OleDbCommand(seq, cnx);
decimal id = (decimal)cmdo.ExecuteScalar();

What needs to be verified for this to work:

• Connection to the bank has to be OK.
• The name must exist and be visible to the user. As already commented.
• Return as decimal. In my case this was done because in the database the field referring to my key that is incremented by Quence is decimal. If you want to know what kind to do: object tipo = cmdo.ExecuteScalar().GetType();. In the case if you are not firing conversion exception then leave the (int) same.

Mounting the INSERT command

Note that the difference here is the use of "two dots" in marking the parameters, works the same way. Doing "@" triggers error of ORA-00936: Missing Expression. in the above example, and if you use question "?", the error is ORA-00917: Missing comma. It can also cause an error if the SQL tokens are pasted with other characters, for example if there are no spaces between "values" and parentheses, it triggers the error ORA-00911: invalid Character.

Whereas the previous step is ok, parameter mounting can be done in two ways:

First Form - Direct

cmd.Parameters.AddRange(new OleDbParameter[]
{
    new OleDbParameter(":v_ativ_cd_codigo", id),
    new OleDbParameter(":v_ativ_tx_descricao", "Atividade 3"),
    new OleDbParameter(":v_ativ_in_ativo", "S"),
    new OleDbParameter(":area_cd_codigo", 1),
});

Second Form - Creation of separate objects

OleDbParameter Parameter1 = new OleDbParameter();
Parameter1.OleDbType = OleDbType.Integer;
Parameter1.ParameterName = ":v_ativ_cd_codigo";
Parameter1.Value = id;

OleDbParameter Parameter2 = new OleDbParameter();
Parameter2.OleDbType = OleDbType.VarChar;
Parameter2.ParameterName = ":v_ativ_tx_descricao";
Parameter2.Value = "Atividade 2";

OleDbParameter Parameter3 = new OleDbParameter();
Parameter3.OleDbType = OleDbType.VarChar;
Parameter3.ParameterName = ":v_ativ_in_ativo";
Parameter3.Value = "S";

OleDbParameter Parameter4 = new OleDbParameter();
Parameter4.OleDbType = OleDbType.Integer;
Parameter4.ParameterName = ":area_cd_codigo";
Parameter4.Value = 1;

Both ways it works, I managed to register without problems, done this just call the cmd.ExecuteNonQuery();