OracleDataTable - editing data & adding rows with pseudocols

OracleDataTable - editing data & adding rows with pseudocols

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

Re: OracleDataTable - editing data & adding rows with pseudocols

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

Postby 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

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

Postby Shalex » Thu 09 Jun 2011 15:40

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

Postby mrjoltcola » Thu 09 Jun 2011 15:46

29669 was a different thread / issue than this.
mrjoltcola
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby 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();
}
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle