OracleDataTable updates rows I did not change

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mrjoltcola
Posts: 24
Joined: Tue 21 Sep 2010 07:17

OracleDataTable updates rows I did not change

Post by mrjoltcola » Fri 03 Jun 2011 03:35

I have simple test case, a table with no constraints or primary key.

create table t(num integer, val integer);

insert into t values(1, 23);
insert into t values(1, 23);
insert into t values(1, 23);
insert into t values(2, 23);
insert into t values(2, 23);

When I edit the 1st row in OracleDataTable and modify val column, then after commit, all rows with num = 1 are updated!

I also have this as my query, so the table should be able to uniquely identify a row:

SELECT ROWID, T.* FROM T

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 06 Jun 2011 14:27

We recommend you to create tables with primary keys.
If this is not possible in your scenario, please follow these steps:
1) open OracleDataTable Editor and put the following query in the Select Text tab
"SELECT ROWID, T.* FROM T"
2) navigate to the Command Generator tab and press the Generate Commands button;
3) go to the Update Commands tab, select the Update radio-button, press the Edit button, and replace the existing command text with the following one
UPDATE ALEXSH.T SET NUM = :NUM, VAL = :VAL WHERE ROWID = :Parameter1
4) navigate to the Parameters tab in OracleCommand Editor, choose "Yes" as an answer to "Do you want to regenerate Parameters collection for command?" - you will get three parameters "NUM", "VAL", "Parameter1". You should modify only Parameter1 - select it in the Parameter Name window and assign the following values:
Data Type = RowId
Null Value - clear this checkbox
Source Column = ROWID
Press OK.
5) press the Preview button in OracleDataTable Editor and check the workability of this approach.

We will investigate the possibility to make the same in an easier way.

mrjoltcola
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Post by mrjoltcola » Mon 06 Jun 2011 15:17

Hi,

I am not using a design-time component, I'm creating the OracleDataTable at runtime. Do you have equivalent code for runtime for steps (2) and (4) below?

Thanks.

mrjoltcola
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Post by mrjoltcola » Tue 07 Jun 2011 19:45

I have ROWID in my query (step 1).

How do I do steps 2, 3 and 4 in code at runtime?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 09 Jun 2011 15:41

We have answered you by e-mail (in ticket 29669).

Post Reply