OracleDataTable updates rows I did not change

OracleDataTable updates rows I did not change

Postby 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
mrjoltcola
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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

Postby 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?
mrjoltcola
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby Shalex » Thu 09 Jun 2011 15:41

We have answered you by e-mail (in ticket 29669).
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle