DBConcurrencyException due to a problem when retrieving data

[vnaranjo]

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.

[Shalex]

Please add the "Trim Fixed Char=false;" entry to your connection string. For more information, please refer to our documentation.

[vnaranjo]

Thanks. It worked as expected.

[vnaranjo]

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?

[Shalex]

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).