DBConcurrencyException

[richard]

i recently encount a strange problem.

first , i successfully insert a record to db, but when i try to update this record, i always get a
DBConcurrencyException exception.

but when i change "using System.Data.OracleClient" to "using Oracle.DataAccess.Client", everything is OK.

To make things worse, i have encount the same problem before when i update another table, at that time, i fixed it by
change "using Oracle.DataAccess.Client" to "using System.Data.OracleClient",

the code is as the follow:

using System.Data;
using System.Data.OracleClient;
//using Oracle.DataAccess.Client;


private void button1_Click(object sender, System.EventArgs e)
{
OracleConnection conn = null;
try
{
conn = new OracleConnection("User Id=news;Password=news;Data Source=NEWS");
conn.Open();

String sql ="select * from action_rule where action_rule_id = 10000126 and branch = 2";
OracleDataAdapter adapter = new OracleDataAdapter(sql, conn);
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);

DataTable table = new DataTable();
adapter.Fill(table);


DataRow row = table.Rows[0];
row["expire_time"] = 70;
adapter.Update(table);
Console.WriteLine("OK");
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
}

}

besides, the follow code can work

private void button1_Click(object sender, System.EventArgs e)
{
OracleConnection conn = null;
try
{
conn = new OracleConnection("User Id=news;Password=news;Data Source=NEWS");
conn.Open();

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "update action_rule set expire_time = 50 where action_rule_id = 10000066";
cmd.ExecuteNonQuery();

Console.WriteLine("OK");
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
}

}

i have been tired at this problem for many days, anyone knows the reason? thanks advance.

 

[Miha Markic [MVP C#]]

Hi richard,

I would avoid using commmandbuilder (blackbox) and instead I would create
sql commands my self.
So I would have control on what is going on.

 

[Richard]

thanks for your suggestion.

do you mean that update database without using Adapter?
directly use such statement as "cmd.ExecuteNonQuery()"?

if i have to rewrite all my code in this way, there will be too much
work to do.

i found when updating a datarow containning some long and unicode text,
this exception often occured.

 

[Sushil Chordia]

Richard, This could be due to Optimistic concurrency violation. Is it
possible that there is some other process making an update to the resultset
after you have made the SELECT but before you sending the update?

By default, Commandbuilder builds commands using Optimistic concurrency. You
can add your own update commands by setting InsertCommand, DeleteCommand and
UpdateCommand property of the Adapter.

 

[Miha Markic [MVP C#]]

Hi Richard,

thanks for your suggestion.

do you mean that update database without using Adapter?
directly use such statement as "cmd.ExecuteNonQuery()"?

if i have to rewrite all my code in this way, there will be too much
work to do.

Check out this
http://www.rthand.com/DesktopModule...alias=RightHand&lang=en-US&ItemID=7&mid=10244
You might find them useful.

 

[David Sceppa]

Richard,

The concurrency exception occurs when the database reports that the
UPDATE or DELETE query executed by the DataAdapter affected no records.
The DataAdapter interprets this result as a failure where the original
values in the DataRow (used in the WHERE clause of the UPDATE or DELETE
query) are no longer in synch with the current values in the corresponding
row in the database.

The behavior you described commonly occurs when the database updates
the contents of the new row with identity, default or timestamp values,
causing the DataRow to be out of synch. This may be also data coersion
issue of some sort. As Sushil pointed out, this problem may also occur
when another process (or user) has updated the row.

Supplying the CREATE TABLE query should make it easier for others to
reproduce the behavior and hopefully get to the root of the problem.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.