Search code examples
c#sqloracle-databaseora-01400

Add new row in a databound form with a Oracle Sequence as the primary key

I am connecting C# with Oracle 11g. I have a DataTable which i fill using an Oracle Data Adapter.

OracleDataAdapter da;
DataTable dt = new DataTable();

da = new OracleDataAdapter("SELECT * FROM Author", con);
da.Fill(dt);

I have few text boxes that I have databound to various rows in the data table.

txtAuthorID.DataBindings.Add("Text", dt, "AUTHORID");
txtFirstName.DataBindings.Add("Text", dt, "FIRSTNAME");
txtLastName.DataBindings.Add("Text", dt, "LASTNAME");
txtAddress.DataBindings.Add("Text", dt, "ADDRESS");
txtTelephone.DataBindings.Add("Text", dt, "TELEPHONE");
txtEmailAddress.DataBindings.Add("Text", dt, "EMAIL");

I also have a DataGridView below the Text Boxes, showing the contents of the DataTable.

dgvAuthor.DataSource = dt;

Now when I want to add a new row, i do

bm.AddNew();

where bm is defined in Form_Load as

BindingManagerBase bm;
bm = this.BindingContext[dt];

And when the save button is clicked after all the information is entered and validated, i do

this.BindingContext[dt].EndCurrentEdit();

try
{
da.Update(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

However the problem comes where when I usually enter a row to the database (using SQL Plus) , I use a my_pk_sequence.nextval for the primary key. But how do i specify that when i add a new row in this method?

I catch this exception ORA-01400: cannot insert NULL into ("SYSMAN".AUTHOR.AUTHORID") which is obvious because nothing was specified for the primary key. How do get around this? Thanx a lot in advance :)

Solution

  • One solution is to create trigger that will automatically generate value on insert.
    Something like this:

    CREATE OR REPLACE TRIGGER trigger_name 
   BEFORE INSERT ON SYSMAN.AUTHOR.AUTHORID 
   REFERENCING 
      OLD AS OLD 
      NEW AS NEW 
   FOR EACH ROW      
   BEGIN
       SELECT my_sequence.NEXTVAL
       INTO :NEW.AUTHORID
       FROM DUAL;
   END;

    Edit:

    Here is quote from Wikipedia on DUAL table:

    The DUAL table is a special one-row table present by default in all Oracle database installations.

    Since DUAL table is important in ORACLE, here are some resources where you can learn about it: