2

It should work but it doesn't.

I have referred others but couldn't find the reason.

 OracleCommand cmd = con.CreateCommand();           
 var query = $@"UPDATE Customer SET ContactName = :ct WHERE CustomerID = :id";
 cmd.CommandText = query;
 cmd.Parameters.Clear();
 cmd.Parameters.Add(new OracleParameter(":id", OracleDbType.Varchar2, "bbb1", System.Data.ParameterDirection.Input));
 cmd.Parameters.Add(new OracleParameter(":ct", OracleDbType.Varchar2, "Joon", System.Data.ParameterDirection.Input));
 var rst = cmd.ExecuteNonQuery();

Thanks in advance.

Joon

Joon w K
  • 777
  • 1
  • 6
  • 27
  • 2
    I don't speak C#. Anyway: what does it mean, "it doesn't work"? What happens when you run that code? Any error? If so, which one? If you don't see the result while querying the CUSTOMER table, perhaps you need to **COMMIT**. – Littlefoot Mar 19 '18 at 08:20
  • Where is your `oracleconnection` code? – D-Shih Mar 19 '18 at 08:20
  • Possible duplicate of [How to execute a update statement using Oracle ODP.Net in C#](https://stackoverflow.com/questions/5765962/how-to-execute-a-update-statement-using-oracle-odp-net-in-c-sharp) – SyMo7amed Mar 19 '18 at 08:21
  • connection is open and no error found. but table not updated. – Joon w K Mar 19 '18 at 08:22
  • that's the one i have referred but it doesn't have AddWithValue. – Joon w K Mar 19 '18 at 08:26
  • @JoonwK, `AddWithValue` is available only on stone-age [deprecated](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/oracle-and-adonet) `System.Data.OracleClient` – Wernfried Domscheit Mar 19 '18 at 08:58
  • Try `cmd.Parameters.Add("id", OracleDbType.Varchar2, ParameterDirection.Input).Value = "bbb1";` – Wernfried Domscheit Mar 19 '18 at 09:02
  • @WernfriedDomscheit, Thanks. I found no method taking the arguments as you recommended. I tried cmd.Parameters.Add("ct", OracleDbType.Varchar2, "Joon", System.Data.ParameterDirection.Input); instead. But it didn't make any change as well. It's weird. – Joon w K Mar 20 '18 at 02:02

2 Answers2

7

I found why it didn't update table.

To make it work I added parameters in the order of the query parameter and found it works. But I still do not understand why the order of adding parameters is so important to make it work.But the thing clear is that it is working when I make it like this:

 OracleCommand cmd = con.CreateCommand();           
 var query = $@"UPDATE Customer SET ContactName = :ct WHERE CustomerID = :id";
 cmd.CommandText = query;
 cmd.Parameters.Clear();

 cmd.Parameters.Add(new OracleParameter(":ct", OracleDbType.Varchar2, "Joon", System.Data.ParameterDirection.Input));
 cmd.Parameters.Add(new OracleParameter(":id", OracleDbType.Varchar2, "bbb1", System.Data.ParameterDirection.Input));
 var rst = cmd.ExecuteNonQuery();

Thanks everybody who paid attention on it.

Joon

Joon w K
  • 777
  • 1
  • 6
  • 27
  • 1
    I didn't know about the order of the query parameters. It seems like a bug. Thank U – Rey Mar 17 '20 at 14:49
0

In order to avoid the order declaration, you can use BindByName:

 OracleCommand cmd = con.CreateCommand();           
 cmd.BindByName = true; // Just add this
 ...
Kino101
  • 765
  • 8
  • 18