Page 1 of 1
OracleDataTable - editing data & adding rows with pseudocols
Posted: Fri 03 Jun 2011 03:10
by 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.
Re: OracleDataTable - editing data & adding rows with pseudocols
Posted: Mon 06 Jun 2011 12:38
by Shalex
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.
Posted: Mon 06 Jun 2011 15:01
by 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.
Posted: Tue 07 Jun 2011 19:49
by 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"
Posted: Thu 09 Jun 2011 15:40
by Shalex
We have answered you by e-mail (in ticket 29669).
Posted: Thu 09 Jun 2011 15:46
by mrjoltcola
29669 was a different thread / issue than this.
Posted: Thu 16 Jun 2011 14:59
by 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();
}