How to use the nextval in my query?

Question

displays the following error: ORA-01722: invalid number.

query result after running the code:

INSERT INTO USUARIOS (ID,MATRICULA,NOME,SENHA,NIVEL,MALETA,EMAIL) 
VALUES ('select id_usuarios.NEXTVAL from dual','TESTE','Frederico','TESTE','1','7000','daerro@erro.com.br')

protected void btn_incluir_Click(object sender, EventArgs e) {

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

        string seq = ("select id_usuarios.NEXTVAL from dual");
        OleDbCommand cmdo = new OleDbCommand(seq, cnx);
        cmdo.ExecuteNonQuery();

        //string sqltxt = "INSERT INTO USUARIOS (ID,MATRICULA,NOME,SENHA,NIVEL,MALETA,EMAIL) VALUES (ID_USUARIOS.NextVal,'MATRICULA','NOME','SENHA','NIVEL',MALETA,'EMAIL')";


        string sqltxt = string.Format(
@"INSERT INTO USUARIOS ( ID
      , MATRICULA
      , NOME
      , SENHA
      , NIVEL
      , MALETA
      , EMAIL
  ) VALUES ( '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')"
            , seq, txt_matricula.Text, txt_nome.Text, txt_senha.Text
            , DropDownList_nivel.Text, txt_maleta.Text, txt_email.Text);


            OleDbCommand cmd = new OleDbCommand(sqltxt, cnx);

            cmd.ExecuteNonQuery();

            txt_matricula.Text = "";
            txt_nome.Text = "";
            txt_senha.Text = "";
            DropDownList_nivel.Text = "";
            txt_maleta.Text = "";
            txt_email.Text = "";
            carregaUsuarios();            

    }

formating data type? example : ID what's data type in table USUARIOS? — محمد النعيمي, Sep 15 '14 at 17:58
ID = INTEGER. I want a solution to be able to get the last ID in the USUARIOS table, when I fill out the form to add the grid. — user2254936, Sep 15 '14 at 18:02
he is not running the first select to retrieve the ID, it is passing the id field to query. Can not a solution to retrieve the last ID from my table to pass the insert query. — user2254936, Sep 15 '14 at 18:17
in which "@" utilizade ? have otherwise given passing the values ? — user2254936, Sep 16 '14 at 19:31

score 1 · Accepted Answer · edited May 23 '17 at 12:02

First of all, the following statement shall raise an error.

INSERT INTO USUARIOS (ID
        , MATRICULA
        , NOME
        , SENHA
        , NIVEL
        , MALETA
        , EMAIL
    ) VALUES ( 'select id_usuarios.NEXTVAL from dual'
        , 'TESTE'
        , 'Frederico'
        , 'TESTE'
        , '1'
        , '7000'
        , 'daerro@erro.com.br'
    )

The major error in this insert statement is that you try to insert a string into a number field. ID as an integer will never accept 'select id_usuarios.NEXTVAL FROM dual' because you actually try to insert the 'select id_usuarios.NEXTVAL FROM dual' as being the actual value.

Should you wish to insert the sequence next value into your ID field, try the following:

INSERT INTO USUARIOS (ID
        , MATRICULA
        , NOME
        , SENHA
        , NIVEL
        , MALETA
        , EMAIL
    ) ( SELECT id_usuarios.NEXTVAL 
                , 'TESTE'
                , 'Frederico'
                , 'TESTE'
                , 1
                , 7000
                , 'daerro@erro.com.br'
            FROM DUAL
    )

Second. both your NIVEL and MALETA fields seem to be numbers as well, so remove the apostrophes!

Third, it is best to let Oracle handle its sequence itself. The best practice for autoincremental fields using Oracle is to write a trigger on insert which will actually select the next sequence value and put it right where it belongs. Plus, notice that if you want to insert a selected value, then you must use an INSERT SELECT statemement, not an INSERT VALUE statement.

Fourth, using string.Format method is really, really, really not a good idea at all as it will let room for SQL Injection. Instead, use have to use command parameters. In addition to it, try to use Using Blocks as they shall dispose any no more required resources when out of scope.

using (var cnx = new OleDbConnection(connectionString)) {
    var sql = 
@"insert into usuarios(matricula, nome, sehna, nivel, maleta, email) 
      values (@matricula, @nome, @sehna, @nivel, @maleta, @email)";

    using (var cmd = new OleDbCommand(sql, cnx)) {
        cnx.Open();

        cmd.Parameters.AddWithValue("@matricula", txt_matricula.Text);
        cmd.Parameters.AddWithValue("@nome", txt_nome.Text);
        cmd.Parameters.AddWithValue("@sehna", txt_senha.Text);
        cmd.Parameters.AddWithValue("@nivel", int.Parse(DropDownList_nivel.Text));
        cmd.Parameters.AddWithValue("@maleta", int.Parse(txt_maleta.Text));
        cmd.Parameters.AddWithValue("@email", txt_email.Text);

        try { cmd.ExecuteNonQuery(); }
        catch { }
        finally { if(cnx.State == ConnectionState.Open) cnx.Close(); }
    }
}

Fifth, it is important to call the right method for the job.

DbCommand.ExecuteNonQuery

Method used for DELETE, INSERT and UPDATE statements ONLY.

DbCommand.ExecuteReader

Method used for SELECT statements with multiple rows and columns.

DbCommand.ExecuteScalar

Method used when the expected result or the only important value shall be located in the first column of the first row. Any other rows and columns than the first shall be ignored.

See my answers to these related questions:

EDIT

I tried giving a hand before a select to retrieve the ID (select id_usuarios.NEXTVAL from dual ) and then retrieve ID already passed the insert

Here's how I'd go about it.

First, create the trigger on your table.

create or replace trigger increment_usuarios_id
        before insert on usuarios
        for each row
    begin
        if :new.id is null then
            select id_usuarios.nextval into :new.id from dual;
        end if;
    end;

Then, when inserting into the usuarios data table, the ID column shall get fed automatically by the increment_usuarios_id trigger.

Hence one is now able to insert without taking any special care on the identity.

insert into usuarios (
        matricula
        , nome
        , sehna
        , nivel
        , maleta
        , email
    ) values (
        'TESTE'
        , 'Frederico'
        , 'TESTE'
        , 1
        , 7000
        , 'daerror@error.com.br'
    );

commit;

You then shall see your new user into the table as expected. If this works, then take the same insert statement and make it the value of your sql variable within your C# code, not forgetting to replace values with the actual control values from your GUI.

is now falling in CATCH and with the following error: {"One or more errors occurred during processing of command \ r \ Nora-00936:. phrase not found"} — user2254936, Sep 16 '14 at 11:34
Try running the statement directly in your RDBMS client, be it Toad for Oracle or SQL Builder or whatsoever. If this works with no error, then copy and paste your statement to your command text. — Will Marcouiller, Sep 16 '14 at 13:55
Have you written your `trigger on insert` to increment your sequence? — Will Marcouiller, Sep 16 '14 at 13:56
Then if `ID` is defiend as the primary key, you can't haven no `ID` specified. The `insert` herewith provided is a sample based on the use of a `trigger on insert` so that the `ID` column gets assigned automatically by the trigger which responsibility stands within setting the `ID` and inserting the provided values from the command. — Will Marcouiller, Sep 16 '14 at 16:41
I tried giving a hand before a select to retrieve the ID (select id_usuarios.NEXTVAL from dual ) and then retrieve ID already passed the insert . More unsuccessfully went wrong ! — user2254936, Sep 16 '14 at 17:53
when I run the following query in toad , it works correctly. insert into usuarios ( matricula , nome , sehna , nivel , maleta , email ) values ( 'TESTE' , 'Frederico' , 'TESTE' , 1 , 7000 , 'daerror@error.com.br' ); — user2254936, Sep 16 '14 at 19:28
in which "@" utilizade ? have otherwise given passing the values ? — user2254936, Sep 16 '14 at 19:32
The @ sign in front of a string in C# means that the string should be interpreted as-is, with no escape sequences and such, etc. For further details on `string`, please see: http://msdn.microsoft.com/en-ca/library/ms228362.aspx — Will Marcouiller, Sep 16 '14 at 19:41

score 0 · Answer 2 · edited May 23 '17 at 12:09

0

look this :link

         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),
           ...
       };


       cmd.ExecuteNonQuery();
  }

Replace actual hardcoded parameters in your OleDbCommand with placeholders (prefixed with @), b) Add instances of OleDbParameter to the DbCommand.Parameters property. Parameter names must match placeholder names.

edited May 23 '17 at 12:09

Community

1
1

answered Sep 15 '14 at 18:15

محمد النعيمي

570
2
9
21

now produced the following error: Specified cast is not valid. Should I convert the string to cmdo? – user2254936 Sep 15 '14 at 19:08
id_usuarios.NEXTVAL , what's data Type ? – محمد النعيمي Sep 15 '14 at 19:23
"ID" in my database is integer, I'm using Oracle. – user2254936 Sep 16 '14 at 11:32
displays the following error: {"Specified cast is not valid."} – user2254936 Sep 16 '14 at 11:40
You need to pass string to varchar2, and numeric to number's. – Will Marcouiller Sep 16 '14 at 13:53
help there guys, please! – user2254936 Sep 16 '14 at 16:18
What is the value id ? MessageBox.Show(id.ToString());and give me this value – محمد النعيمي Sep 16 '14 at 16:21
What are the other fields Data Type is number ? – محمد النعيمي Sep 16 '14 at 16:25
ID = INTEGER, MATRICULA = VARCHAR2, NOME = VARCHAR2, SENHA = VARCHAR2, NIVEL = VARCHAR2, MALETA = INTEGER, EMAIL = CHAR – user2254936 Sep 16 '14 at 16:40

score 0 · Answer 3 · edited Sep 16 '14 at 14:13

Will Marcouiller: I see no error in syntax.

using (var cnx = new OleDbConnection(new AdministradorDAO().conexao))
{
    var sql = @"insert into usuarios(matricula, nome, senha, nivel, maleta, email) values (@matricula, @nome, @senha, @nivel, @maleta, @email)";

    using (var cmd = new OleDbCommand(sql, cnx))
    {
        cnx.Open();

        cmd.Parameters.AddWithValue("@matricula", txt_matricula.Text);
        cmd.Parameters.AddWithValue("@nome", txt_nome.Text);
        cmd.Parameters.AddWithValue("@senha", txt_senha.Text);
        cmd.Parameters.AddWithValue("@nivel", int.Parse(DropDownList_nivel.Text));
        cmd.Parameters.AddWithValue("@maleta", int.Parse(txt_maleta.Text));
        cmd.Parameters.AddWithValue("@email", txt_email.Text);

        try { cmd.ExecuteNonQuery(); }
        catch { }
        finally { if (cnx.State == ConnectionState.Open) cnx.Close(); }

    }
}

when I run the query directly in the toad , works correctly . insert into actweb.usuarios (matricula, nome, senha, nivel, maleta, email) values ('TESTE', 'Frederico', 'TESTE', 1, 7000, 'daerror@error.com.br'); — user2254936, Sep 16 '14 at 20:48

How to use the nextval in my query?

3 Answers3