OracleDataTable - editing data & adding rows with pseudocols

[mrjoltcola]

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]

1.

"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]

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]

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]

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

[mrjoltcola]

29669 was a different thread / issue than this.

[Shalex]

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