I am having difficulty returning my ID using nextval, to later insert it into my table

Question

I am having difficulty returning my ID using nextval, to later insert it into my table

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I'm having trouble returning my ID using nextval, and then inserting it into 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();
}

c# oracle query oledb

asked by anonymous 16.09.2014 / 14:21

2 answers

How to use the cURL library for c ++ correctly? PHP edit image

score 1 · Answer 1

Instead of passing the id as a parameter you could put directly in 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)";

score 0 · Answer 2

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

In order to load the sequence value using Oracle and OleDb the way you did this in general, what may be happening is the return of the ExecuteScalar not allowing the casting, you were missing the exception in the line of ExecuteScalar. Anyway 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 you need to check to make this work:

Connection to the bank must be OK.
The sequence 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 the sequence is decimal. If you want to know type do: object tipo = cmdo.ExecuteScalar().GetType(); . Do not know if it is not triggering conversion exception then leaves the (int) itself.

Assembling the INSERT command

Notice that the difference here is the use of "colon" in parameter marking, it works the same way. If you do the "@" issue ORA-00936: missing expression error in the example above, and if you use "?", The error is ORA-00917: missing comma in>. It can also cause an error if the SQL tokens are pasted with other characters, for example if there are no spaces between the values and parentheses, triggers the ORA-00911: invalid character error. >

Considering that the previous step is ok, the assembly of parameters 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 - Creating 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;

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