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;
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();
}
}