DBConcurrencyException due to a problem when retrieving data

DBConcurrencyException due to a problem when retrieving data

Postby vnaranjo » Thu 24 Mar 2011 18:24

Hi,

We're having a problem when we try to update a table that contains a Char(1) field. The problem is that when the data is retrieved, for a char(1) that contains an space in the database, the data retrieved is an empty string. We don't have any trouble with that, but the problem is that when we try to update the record, the record is not found by the where clause gererated by the OracleCommandBuilder, so we get an DBConcurrencyException, and of course the data is not saved.


Here are the steps to reproduce the issue.

1. Create the table and populate it.

Code: Select all
Create Table TestTable (TestTable_Key integer, TestTable_Trouble char(1), TestTable_UpdateColumn varchar(10), Primary Key (TestTable_Key));

Insert ALL
   INTO TestTable (TestTable_Key, TestTable_Trouble, TestTable_UpdateColumn) Values(1, ' ', 'desc1')
   INTO TestTable (TestTable_Key, TestTable_Trouble, TestTable_UpdateColumn) Values(2, '', 'desc2')
   INTO TestTable (TestTable_Key, TestTable_Trouble, TestTable_UpdateColumn) Values(3, NULL, 'desc3')
   INTO TestTable (TestTable_Key, TestTable_Trouble, TestTable_UpdateColumn) Values(4, 'S', 'desc4')
SELECT * FROM dual;


Use this code to retrieve the data and update it. You'll get an DBConcurrencyException in the adapter.Update line.

Code: Select all
public void TestTableProblem()
{
   //Create the Connection
   using (OracleConnection connection = new OracleConnection("Data Source=sdedev;User Id=System;Password=AfmPassword;Pooling=False;"))
   {
      //Open the conection
      connection.Open();

      //Create a Transaction
      OracleTransaction transaction = connection.BeginTransaction();

      //Create the required objects
      DataSet ds = new DataSet();
      OracleCommand command = new OracleCommand(@"SELECT TestTable_Key, TestTable_Trouble, TestTable_UpdateColumn
                        FROM TestTable
                        Where TestTable_Key = 1", connection);
      OracleDataAdapter adapter = new OracleDataAdapter(command);

      //Fill Table with the data
      adapter.Fill(ds);

      //Build the Update Command
      OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
      OracleCommand UpdateCommand = builder.GetUpdateCommand();
      adapter.UpdateCommand = UpdateCommand;

      //Extract the object
      //Value Contains an Empty String, but in the database is stored an space
      object value = ds.Tables[0].Rows[0]["TestTable_Trouble"];

      //Modify any column in the result
      ds.Tables[0].Rows[0]["TestTable_UpdateColumn"] = "something"; //Update any column
            
      //Update the Recordset
      adapter.Update(ds);

      //Roll Back the Transaction
      transaction.Rollback();
   }
}


You won't get the exception if you use any of the other values contained in the table. Only for the first item where the ' ' is stored in TestTable_Trouble column.
vnaranjo
 
Posts: 4
Joined: Fri 24 Sep 2010 00:46

Postby Shalex » Fri 25 Mar 2011 15:42

Please add the "Trim Fixed Char=false;" entry to your connection string. For more information, please refer to our documentation.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Thanks, It worked

Postby vnaranjo » Fri 25 Mar 2011 16:39

Thanks. It worked as expected.
vnaranjo
 
Posts: 4
Joined: Fri 24 Sep 2010 00:46

Postby vnaranjo » Mon 28 Mar 2011 20:15

We're using DotConector for Oracle version 5.70.170.0. We can't update to version 6.10. I read that the version we need to use to be able to change that property (Trim Fixed Char) is version 5.70.190. Is there any place where we can download version 5.70.190?
vnaranjo
 
Posts: 4
Joined: Fri 24 Sep 2010 00:46

Postby Shalex » Tue 29 Mar 2011 08:35

To download a particular registered version of dotConnect for Oracle, please use the credentials (URL, account name, and password) that were sent to you after the purchase of our product. If you have not received this information or want to check the status of your subscription, please contact our sales department (sales at devart*com).
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle