Insert using parameters in the C # application for the Oracle database error: ORA-01036: invalid variable name / number [closed]

0

I noticed that you have a lot of questions about this error but none solved my case the connection works normally, there is the method Insert ():

      public bool Insert(ArrayList arrAlunos)
      {
        vsql = "insert into ALUNOS_("
             + "UF"
             + ", IDADE"
             + ", TELEFONE"
             + ", NOME_PAI"
             + ", ENDERECO"
             + ", EMAIL"
             + ", NOME"
             + ", CIDADE"
             + ", NOME_MAE"
               + ") values ("
                        + "@UF"
                        + ", @IDADE"
                        + ", @TELEFONE"
                        + ", @NOME_PAI"
                        + ", @ENDERECO"
                        + ", @EMAIL"
                        + ", @NOME"
                        + ", @CIDADE"
                        + ", @NOME_MAE"
                        + ")";

        if (this.conectar())
        {
            try
            {
                //vsql = "INSERT INTO ALUNOS_ (UF, IDADE, TELEFONE, NOME_PAI, ENDERECO, EMAIL, NOME, CIDADE, NOME_MAE) values ('sp', 0,'','','','','','','')";
                OracleCommand objCmd = new OracleCommand(vsql, objCon);

                objCmd.Parameters.Add(new OracleParameter("@UF",       arrAlunos[0]));
                objCmd.Parameters.Add(new OracleParameter("@IDADE",    arrAlunos[1]));
                objCmd.Parameters.Add(new OracleParameter("@TELEFONE", arrAlunos[2]));
                objCmd.Parameters.Add(new OracleParameter("@NOME_PAI", arrAlunos[3]));
                objCmd.Parameters.Add(new OracleParameter("@ENDERECO", arrAlunos[4]));
                objCmd.Parameters.Add(new OracleParameter("@EMAIL",    arrAlunos[5]));
                objCmd.Parameters.Add(new OracleParameter("@NOME",     arrAlunos[6]));
                objCmd.Parameters.Add(new OracleParameter("@CIDADE",   arrAlunos[7]));
                objCmd.Parameters.Add(new OracleParameter("@NOME_MAE", arrAlunos[8]));

                objCmd.ExecuteNonQuery();                                        

                return true;
            }
            catch (OracleException erro)
            {
                throw erro;
            }
            finally
            {
                this.desconectar();
            }
        }
        else
        {
            return false;
        }

Here is where I pass the values to the array to test the insert

     public void testDB()
    {
        sisDBAdm obj = new sisDBAdm();
       // Dictionary<String, Alunos> dicAluno = new Dictionary<String, Alunos>();
       ArrayList arr = new ArrayList();
        arr.Add("SP");
        arr.Add(24);
        arr.Add("3432 2222");
        arr.Add("Jose");
        arr.Add("Catarina Martins");
        arr.Add("[email protected]");
        arr.Add("Bruno Viny");
        arr.Add("Votuporanga");
        arr.Add("MAria de Lourdes");

        if (obj.Insert(arr))
        {
            MessageBox.Show("Inserido com sucesso");
        }
        else
        {
            MessageBox.Show("Não inserido");
        }
    }

SOLVED: I just changed the @ by: in the parameters, it looks like this:

    public bool Insert(ArrayList arrAlunos)
    {
        vsql = "insert into ALUNOS_ ("
             + "UF"
             + ", IDADE"
             + ", TELEFONE"
             + ", NOME_PAI"
             + ", ENDERECO"
             + ", EMAIL"
             + ", NOME"
             + ", CIDADE"
             + ", NOME_MAE"
               + ") values ("
                        + ":UF"
                        + ", :IDADE"
                        + ", :TELEFONE"
                        + ", :NOME_PAI"
                        + ", :ENDERECO"
                        + ", :EMAIL"
                        + ", :NOME"
                        + ", :CIDADE"
                        + ", :NOME_MAE"
                        + ")";

        if (this.conectar())
        {
            try
            {
                //vsql = "INSERT INTO ALUNOS_ (UF, IDADE, TELEFONE, NOME_PAI, ENDERECO, EMAIL, NOME, CIDADE, NOME_MAE) values ('sp', 0,'','','','','','','')";
                OracleCommand objCmd = new OracleCommand(vsql, objCon);

                objCmd.Parameters.Add(new OracleParameter(":UF",       arrAlunos[0]));
                objCmd.Parameters.Add(new OracleParameter(":IDADE",    arrAlunos[1]));
                objCmd.Parameters.Add(new OracleParameter(":TELEFONE", arrAlunos[2]));
                objCmd.Parameters.Add(new OracleParameter(":NOME_PAI", arrAlunos[3]));
                objCmd.Parameters.Add(new OracleParameter(":ENDERECO", arrAlunos[4]));
                objCmd.Parameters.Add(new OracleParameter(":EMAIL",    arrAlunos[5]));
                objCmd.Parameters.Add(new OracleParameter(":NOME",     arrAlunos[6]));
                objCmd.Parameters.Add(new OracleParameter(":CIDADE",   arrAlunos[7]));
                objCmd.Parameters.Add(new OracleParameter(":NOME_MAE", arrAlunos[8]));

                objCmd.ExecuteNonQuery();                                        

                return true;
            }
            catch (OracleException erro)
            {
                throw erro;
            }
            finally
            {
                this.desconectar();
            }
        }
        else
        {
            return false;
        }
    
asked by anonymous 31.01.2018 / 17:46

1 answer

0

This information should be very useful:

This information was taken from the official documentation from Microsoft

    
05.02.2018 / 15:49