Update command C# with Oracle

Question

I've a problem to update my database.

public void UpdateAto(AtoEntity atoEntity)
{
    OracleDataAdapter da = new OracleDataAdapter();

    string oradb = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=blabla.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test13)));;User Id=id13;Password=pwd13;";
    OracleConnection con = new OracleConnection(oradb);
    con.Open();

    OracleCommand cmd = new OracleCommand();
    cmd.Connection = con;

    cmd.CommandText = 
        "UPDATE SLAPTEOP SET XOCL ='" + atoEntity.OidOcl +
                         "',NOM='" + atoEntity.NameAto +
                         "',ETAT='-1" +
                         "',NBPAAN='11" +
                         "',DESCRIPTION='" + atoEntity.DescriptionAto +
                         "',APPORTEUR='" + atoEntity.ContributorAto +
                         "',AUTEUR_MISEAJOUR='" + atoEntity.AuthorUpdateAto +
                          "',DATE_MISEAJOUR=SYSDATE WHERE OID ='" + atoEntity.OidAto + "'";

    cmd.CommandType = CommandType.Text;

    cmd.ExecuteNonQuery();
    con.Dispose();
}

The request is good, it was written without parameters to make it simpler. It has been tested on the SGBD. Select and Insert Request works too.

When I execute cmd.ExecuteNonQuery() nothing happens in database. It turns in loop.

A try / catch block had been added but no errors or exceptions were caught.

This is very unclear. What does POST or GET have to do with this? Trap exceptions and log or debug them. The Exception details will tell you what is going wrong. And please use parameterized queries to prevent sql injection and syntax errors. — Crowcoder, Dec 28 '17 at 13:51
`cmd.ExecuteNonQuery` should return the number of Updated rows. May you warp the things into a try catch and [edit] your exception into your question? — Drag and Drop, Dec 28 '17 at 13:52
[related question](https://stackoverflow.com/questions/28487662/c-sharp-oracle-dataaccess-client-not-responding-executing-sql-update-stateme?noredirect=1&lq=1) — Drag and Drop, Dec 28 '17 at 13:56
May you make a simple Select to check if the connection string is ok? — Drag and Drop, Dec 28 '17 at 14:23
May you try `OracleCommand cmd = con.CreateCommand();` instead of `OracleCommand cmd = new OracleCommand();` — Drag and Drop, Dec 28 '17 at 14:26
yes, SELECT and INSERT works! I just have a problem with UPDATE.... — Nitneuq, Dec 28 '17 at 14:27
The ExecuteNonQuery returns 0 if no rows are updated or >0 if the command has modified some rows. Please check the return value of ExecuteNonQuery. If it is zero then the WHERE clause failed to find a record to update. Meaning that the value you use for Oid is wrong (Are you sure that this OID is a string?) — Steve, Dec 28 '17 at 14:28
J'ai bien compris nitneuq, la question est est ce que ça marche sur cette object commande pas si ça marche dans une autre partie du code. On sait jamais un probleme de copier coller ça arrive. (French version to tone down) — Drag and Drop, Dec 28 '17 at 14:31
i can not see the value of a because the program does not stop on ExecuteNonQuery .. — Nitneuq, Dec 28 '17 at 14:35
C'est la première fois que je fais un update sur cet objet insert OK, select OK, update CRASH. alors que la requête SQL est correcte — Nitneuq, Dec 28 '17 at 14:37

score 0 · Answer 1 · answered Dec 28 '17 at 14:08

0

You should use bind variables. Check number of updated rows like this:

cmd.CommandType = CommandType.Text;
cmd.CommandText = 
   "UPDATE SLAPTEOP SET XOCL = :xocl, 
       NOM = :mom, ETAT = -1, 
       NBPAAN = 11, DESCRIPTION = :desc, 
       APPORTEUR = :apporteur, AUTEUR_MISEAJOUR = :auteur, 
       DATE_MISEAJOUR = SYSDATE
    WHERE OID = :oid";

cmd.Parameters.Add("xocl", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.OidOcl;
cmd.Parameters.Add("mom", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.NameAto;
cmd.Parameters.Add("desc", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.DescriptionAto;
cmd.Parameters.Add("apporteur", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.ContributorAto;
cmd.Parameters.Add("auteur", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.AuthorUpdateAto;
cmd.Parameters.Add("oid", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.OidAto;

var r = cmd.ExecuteNonQuery();

Console.WriteLine("{0} rows updated", r);

answered Dec 28 '17 at 14:08

Wernfried Domscheit

54,457
9
76
110

Well, good but do you need ParameterDirection.Input? Isn't it the default? – Steve Dec 28 '17 at 14:09
1

@Steve, yes you are right (see [OracleParameter(string, OracleDbType)](https://docs.oracle.com/database/121/ODPNT/OracleParameterClass.htm#i1011248)), I am just used to write it. – Wernfried Domscheit Dec 28 '17 at 14:16
e.Message = "ORA-01745: nom de variable hôte/attachée (bind) non valide" I don't understand anything anymore – Nitneuq Dec 28 '17 at 14:25
Please run `cmd.CommandText = "ALTER SESSION SET NLS_LANGUAGE = american"); cmd.ExecuteNonQuery();` in order to get english error message. – Wernfried Domscheit Dec 28 '17 at 14:28
ORA-01745: invalid host/bind variable name – Nitneuq Dec 28 '17 at 14:30
Most likely you did a typo, verify your code carefully. – Wernfried Domscheit Dec 28 '17 at 14:33
Typo could be at `:mom`, As the column name is "nom". you could have miss it when you Copy past your parameters. – Drag and Drop Dec 28 '17 at 14:40

score 0 · Answer 2 · answered Dec 28 '17 at 14:43

Drag and Drop I don't see the problem...

   cmd.CommandType = CommandType.Text;
            cmd.CommandText = 
                "UPDATE SLAPTEOP SET XOCL = :xocl,"+ 
            "NOM = :nom, ETAT = -1,"+
            "NBPAAN = 11, DESCRIPTION = :desc,"+ 
            "APPORTEUR = :apporteur, AUTEUR_MISEAJOUR = :auteur,"+
            "DATE_MISEAJOUR = SYSDATE"+
            "WHERE OID = :oid";

            cmd.Parameters.Add("xocl", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.OidOcl;
            cmd.Parameters.Add("nom", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.NameAto;
            cmd.Parameters.Add("desc", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.DescriptionAto;
            cmd.Parameters.Add("apporteur", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.ContributorAto;
            cmd.Parameters.Add("auteur", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.AuthorUpdateAto;
            cmd.Parameters.Add("oid", OracleDbType.Varchar2, ParameterDirection.Input).Value = atoEntity.OidAto;

Run `Debug.WriteLine(cmd.CommandText);` - then you will see it! — Wernfried Domscheit, Dec 28 '17 at 14:45
`DATE_MISEAJOUR=SYSDATEWHERE` => `DATE_MISEAJOUR=SYSDATE WHERE` — Drag and Drop, Dec 28 '17 at 14:52
"UPDATE SLAPTEOP SET XOCL = :xocl,NOM = :nom, ETAT = '-1',NBPAAN = '11', DESCRIPTION = :desc,APPORTEUR = :apporteur, AUTEUR_MISEAJOUR = :auteur,DATE_MISEAJOUR = SYSDATE WHERE OID = :oid" don't works.. — Nitneuq, Dec 28 '17 at 15:00

Update command C# with Oracle

2 Answers2