OracleDataTable - editing data & adding rows with pseudocols

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 - editing data & adding rows with pseudocols

Post by mrjoltcola » Fri 03 Jun 2011 03:10

If I use SELECT ROWID, EMP.* FROM EMP as the source for my OracleDataTable, I am not able to add rows. I get a message: "Column 'ROWID' does not allow nulls. Do you want to correct the value?"

Now I don't understand why OracleDataTable would try to insert / update ROWID at all.

Is there something I am missing, an option to disable this, or an ability to customize which columns are inserted?

I have used another 3rd party data control for Oracle that does not do this, but correctly handles ROWID. The reason we select rowid in some cases are to allow updates to tables without a primary key. I am curious how dotConnect does the same as I have actually seen OracleDataTable update multiple rows when I only changed one row.

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Re: OracleDataTable - editing data & adding rows with pseudocols

Post by Shalex » Mon 06 Jun 2011 12:38

1.
mrjoltcola wrote:"Column 'ROWID' does not allow nulls. Do you want to correct the value?"
Please open Columns Collection Editor via the Columns property of OracleDataTable and set ROWID's AllowDBNull property to True. To make columns collection be available, set OracleDataTable.Active to True.
2. Also pay attention to the Command Generator tab of OracleDataTable Editor: if you want to return the value of ROWID to OracleDataTable after each INSERT, press the "Get Table Fields" button in this editor, select the "With refresh SQL" option, select "Refreshing" attribute for ROWID, and press the "Generate Commands" button.

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

Post by mrjoltcola » Mon 06 Jun 2011 15:01

All of my DataTables are dynamic (created and configured by code at runtime). I'll try to figure out how to do what you described dynamically.

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

Post by mrjoltcola » Tue 07 Jun 2011 19:49

I was able to add the AllowDBNull and ReadOnly attributes to the ROWID Column.

But I cannot make the refresh work from code at runtime.

I tried
dataTable.RefreshingFieldds = "ROWID"
and
dataTable.RefreshingFieldds = "T.ROWID"

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 09 Jun 2011 15:40

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

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

Post by mrjoltcola » Thu 09 Jun 2011 15:46

29669 was a different thread / issue than this.

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 16 Jun 2011 14:59

Please try implementing the refresh using the RETURNING clause of your INSERT command:

Code: Select all

private void Form1_Load(object sender, EventArgs e) {
dt = new OracleDataTable("SELECT ROWID, T.* FROM T", "server=orcl1120;uid=***;pwd=***;");
dt.Active = true;
dt.Columns["ROWID"].AllowDBNull = true;
dt.Columns["ROWID"].ReadOnly = true;

dt.InsertCommand = dt.Connection.CreateCommand();
dt.InsertCommand.CommandText = "INSERT INTO T (NUM, VAL) VALUES (:NUM, :VAL) RETURNING ROWID INTO :Parameter1";
dt.InsertCommand.Parameters.Add("VAL", OracleDbType.Integer).SourceColumn = "VAL";
dt.InsertCommand.Parameters.Add("NUM", OracleDbType.Integer).SourceColumn = "NUM";
dt.InsertCommand.Parameters.Add("Parameter1", OracleDbType.RowId).SourceColumn = "ROWID";
dt.InsertCommand.Parameters["Parameter1"].Direction = ParameterDirection.Output;

dt.UpdateCommand = dt.Connection.CreateCommand();
dt.UpdateCommand.CommandText = "UPDATE T SET NUM = :NUM, VAL = :VAL WHERE ROWID = :Parameter1";
dt.UpdateCommand.Parameters.Add("VAL", OracleDbType.Integer).SourceColumn = "VAL";
dt.UpdateCommand.Parameters.Add("NUM", OracleDbType.Integer).SourceColumn = "NUM";
dt.UpdateCommand.Parameters.Add("Parameter1", OracleDbType.RowId).SourceColumn = "ROWID";

dataGridView1.DataSource = dt;
}

private void button1_Click(object sender, EventArgs e) {
dt.Update();
}

Post Reply