Update CLOB using a textbox in C#

Question

I've been all over the web with this one and I am surprised how I can't seem to find any way to do what I looking for.

I am using the Oracle.DataAccess.Client library in my C# project, not the deprecated System.Data.OracleClient.

I have a very simple table:

CREATE TABLE testing (
      ID NUMBER(10),
      COMMENTS CLOB,
      DATECREATED   DATE DEFAULT (SYSDATE) NOT NULL,
      DATEMODIFIED  DATE
);

INSERT INTO testing (ID, COMMENTS) VALUES(1, 'this is a test');

The above obviously works just fine. The problem is, this being a comment field it will be changed. In my C# program I would like to allow users to save comments. For this example I am removing anything complex.

I have a button on a form and a textbox called "comments".

using (OracleConnection connection = new OracleConnection(<VALID CONN STRING GOES HERE>)) {
    connection.Open();
    using (OracleCommand command = new OracleCommand()) {
         command.Connection = connection;
         command.CommandText = "UPDATE testing SET COMMENTS = :COMMENTS, DATEMODIFIED = sysdate WHERE ID = :ID";
         command.CommandType = CommandType.Text;
         command.Parameters.Add("ID", OracleDbType.Int32, ParameterDirection.Input).Value = 1;
         command.Parameters.Add("COMMENTS", OracleDbType.Clob, ParameterDirection.Input).Value = comments.Text;
         command.ExecuteNonQuery();
    }
}

So basically this code works as long as I don't attempt to write the Clob. If I save the date modified is created as expected. However when I save with the Clob, nothing happens. No error message from oracle, no exception, nothing.

If I forgo parameterization and do it the wrong way:

command.CommandText = "UPDATE testing SET COMMENTS = " + comments.Text + ", DATEMODIFIED = sysdate";

There is no problem. It seems the parameterization is the issue here.

Answer 1

It turns out I was correct in that parameterization was the issue, however I can say it was a very basic mistake. Anyway the statement was actually running perfectly fine but the parameters were being added out of order.

Note how in the following code ID is added last, as it is the last parameter (where clause.)

using (OracleConnection connection = new OracleConnection(<VALID CONN STRING GOES HERE>)) {
    connection.Open();
    using (OracleCommand command = new OracleCommand()) {
         command.Connection = connection;
         command.CommandText = "UPDATE testing SET COMMENTS = :COMMENTS, DATEMODIFIED = sysdate WHERE ID = :ID";
         command.CommandType = CommandType.Text;
         command.Parameters.Add("COMMENTS", OracleDbType.Clob, ParameterDirection.Input).Value = comments.Text;
         command.Parameters.Add("ID", OracleDbType.Int32, ParameterDirection.Input).Value = 1;
         command.ExecuteNonQuery();
    }
}