none
RE: Insert new master/details rows RRS feed

  • Question

  • Hi,
    I was reading the post: Insert new master/details rows(link:http://social.msdn.microsoft.com/Forums/en/adodotnetdataset/thread/4d10fe87-2de8-42a8-8ef9-b9d46c0fd28d) in this part of the forum and found it very useful. I followed the Answer provided by BinaryCoder and worked well in a master-detail scenario. My app worked well while updating, however i found that initial primary key key get negative values and it helps updating the table but is there anyway i could i get the issued primary key by database upon after updating. To see this i have to clear the dataset and refill it so that primary key is been refelected next time.

    In my case the primary key value is crucial. I cant say to users to unload and load the data back to dataset. was wondering any of you encountered this problem and how you over come this.

    Thanks & Regards,
    Hifni Shahzard Nazeer M.
    Saturday, August 15, 2009 10:07 AM

Answers

  • Hi Chris,
    appreciate for your post. I think calling the adapter.fill twice is quite cumbersome to the users and need a quick way to reflect the identity. I think I figured it out and would like to share it with all.

    Instead having to rely on an auto increment value, I have to rely on a value set provided via trigger. Have to admit myself that the resource below was very helpful indeed to the quest of solving this problem:

    http://www.eggheadcafe.com/forumarchives/NETFrameworkADONET/Nov2005/post24232248.asp
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/4d10fe87-2de8-42a8-8ef9-b9d46c0fd28d

    The environment I was working with was Windows XP Pro SP+3, Visual Studio 2005, C#.net, Oracle XE DB, Oracle 10g client, System.Data.OracleCleint and TableAdapters;

    To illustrate how I went on, I extracted the following tables and triggers from my project:

    Journal(journalid*, xsactdate, xsactdesc); --Master Table
    JournalAccount(Journalid*, accountid*, xsactvalue); --Detail Table
    PKEY(journal_PKEY);  --The Table which stores the primary key. 

    I had the following triggers written for the Journal & JournalAccount table respectively.

    DECLARE
       nJournal_PKey PKey.Journal_PKey%TYPE;
       nChkJournalID Journal.JournalID%TYPE;
    BEGIN
       -- this is to prevent posted Journals from being
       -- updated
       IF ( INSERTING ) THEN
          nChkJournalID := :new.JournalID;
       ELSE
          nChkJournalID := :old.JournalID;
       END IF;
    
       BEGIN
          SELECT JournalID INTO nJournal_PKey FROM AccountLedger WHERE JournalID = nChkJournalID and rownum = 1;
          
          IF ( SQL%FOUND ) THEN
             RAISE_APPLICATION_ERROR( -20000,
                                      'This transaction has been already being posted to the ledger '
                                      || 'insert/update/delete not allowed'
                	                );
          END IF;
       EXCEPTION
          WHEN NO_DATA_FOUND THEN NULL;
       END;
    
       IF ( INSERTING AND (:new.JournalID IS NULL) OR (:new.JournalID  < 0)) THEN
          SELECT Journal_PKey INTO nJournal_PKey FROM PKey FOR UPDATE NOWAIT;
          UPDATE PKey SET Journal_PKey = Journal_PKey + 1;
          :new.JournalID   := nJournal_PKey + 1;
       END IF;
       :new.EntryDate   := SYSDATE;
       :new.EntryUserID := USER;
    END;
    

    Having followed what David Sceppa and Jason Kresowaty alias BinaryCoder wrote in their postings, things were going ok for me, except for the fact that Identities aren’t reflected upon saving to the table.

    Since the TableAdapters created without the InsertCommand, I have to include it by myself.

    There were certain things which I need to modify, particularly Insert Query. Look into my Insert Query and you may see the Oracle Keyword “RETURNING”. The keyword is basically enables us to use it as an output parameter. I have inserted the temporary identity and getting back the value assigned via the trigger as an output parameter.
     
    The following describes the “RETURNING” clause of Oracle: http://www.myoracleguide.com/s/Returning.htm

    Finally have to amend the System.Data.DataRowVersion to System.Data.DataRowVersion.Proposed and the identity field (JournalID in my case) System.Data.ParameterDirection to System.Data.ParameterDirection.InputOutput.

    My JournalTableAdapter's InsertCommand Looked like this:
    this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand();
                this._adapter.InsertCommand.Connection = this.Connection;
                this._adapter.InsertCommand.CommandText = "INSERT INTO JOURNALACCOUNT\r\n                      (JOURNALID, ACCOUNTID, XSACTVAL" +
                    "UE, DISCOUNTEDPAYMENT)\r\nVALUES     (:JOURNALID, :ACCOUNTID, :XSACTVALUE, \'N\') RETURNING JOURNALID INTO :JOURNALID";
                this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("ACCOUNTID", System.Data.OracleClient.OracleType.Char, 15, System.Data.ParameterDirection.Input, "ACCOUNTID", System.Data.DataRowVersion.Current, false, null));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTVALUE", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.Input, "XSACTVALUE", System.Data.DataRowVersion.Current, false, null));
    

    and JournalAccountTableAdapter's InsertCommand Looked like this:

    this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand();
                this._adapter.InsertCommand.Connection = this.Connection;
                this._adapter.InsertCommand.CommandText = "INSERT INTO JKSBSCHEMA.JOURNAL(JOURNALID, XSACTDATE, XSACTDESC)\r\n VALUES (:JOURNALID, :XSACTDATE, :XSACTDESC) RETURNING JOURNALID INTO :JOURNALID";
                this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
                this._adapter.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.Both;
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDATE", System.Data.OracleClient.OracleType.DateTime, 7, System.Data.ParameterDirection.Input, "XSACTDATE", System.Data.DataRowVersion.Current, false, null));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDESC", System.Data.OracleClient.OracleType.VarChar, 40, System.Data.ParameterDirection.Input, "XSACTDESC", System.Data.DataRowVersion.Current, false, null));
    

    Now Upon clicking the Save button, I get the Identity value immediately rather than need to refill the dataset.

    Pls. feel free to respond on this, If I am not doing the ideal way.

    Thanks & Regards,


    Hifni Shahzard Nazeer M.
    Tuesday, August 18, 2009 1:33 PM

All replies

  • Typically when the adapter.fill is called the results are cleared and refilled. This would bring in those new rows with the correct Primary Keys. So in the post that you looked at I believe one of the steps is to fill after update is called.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, August 17, 2009 4:28 PM
  • Hi Chris,
    appreciate for your post. I think calling the adapter.fill twice is quite cumbersome to the users and need a quick way to reflect the identity. I think I figured it out and would like to share it with all.

    Instead having to rely on an auto increment value, I have to rely on a value set provided via trigger. Have to admit myself that the resource below was very helpful indeed to the quest of solving this problem:

    http://www.eggheadcafe.com/forumarchives/NETFrameworkADONET/Nov2005/post24232248.asp
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/4d10fe87-2de8-42a8-8ef9-b9d46c0fd28d

    The environment I was working with was Windows XP Pro SP+3, Visual Studio 2005, C#.net, Oracle XE DB, Oracle 10g client, System.Data.OracleCleint and TableAdapters;

    To illustrate how I went on, I extracted the following tables and triggers from my project:

    Journal(journalid*, xsactdate, xsactdesc); --Master Table
    JournalAccount(Journalid*, accountid*, xsactvalue); --Detail Table
    PKEY(journal_PKEY);  --The Table which stores the primary key. 

    I had the following triggers written for the Journal & JournalAccount table respectively.

    DECLARE
       nJournal_PKey PKey.Journal_PKey%TYPE;
       nChkJournalID Journal.JournalID%TYPE;
    BEGIN
       -- this is to prevent posted Journals from being
       -- updated
       IF ( INSERTING ) THEN
          nChkJournalID := :new.JournalID;
       ELSE
          nChkJournalID := :old.JournalID;
       END IF;
    
       BEGIN
          SELECT JournalID INTO nJournal_PKey FROM AccountLedger WHERE JournalID = nChkJournalID and rownum = 1;
          
          IF ( SQL%FOUND ) THEN
             RAISE_APPLICATION_ERROR( -20000,
                                      'This transaction has been already being posted to the ledger '
                                      || 'insert/update/delete not allowed'
                	                );
          END IF;
       EXCEPTION
          WHEN NO_DATA_FOUND THEN NULL;
       END;
    
       IF ( INSERTING AND (:new.JournalID IS NULL) OR (:new.JournalID  < 0)) THEN
          SELECT Journal_PKey INTO nJournal_PKey FROM PKey FOR UPDATE NOWAIT;
          UPDATE PKey SET Journal_PKey = Journal_PKey + 1;
          :new.JournalID   := nJournal_PKey + 1;
       END IF;
       :new.EntryDate   := SYSDATE;
       :new.EntryUserID := USER;
    END;
    

    Having followed what David Sceppa and Jason Kresowaty alias BinaryCoder wrote in their postings, things were going ok for me, except for the fact that Identities aren’t reflected upon saving to the table.

    Since the TableAdapters created without the InsertCommand, I have to include it by myself.

    There were certain things which I need to modify, particularly Insert Query. Look into my Insert Query and you may see the Oracle Keyword “RETURNING”. The keyword is basically enables us to use it as an output parameter. I have inserted the temporary identity and getting back the value assigned via the trigger as an output parameter.
     
    The following describes the “RETURNING” clause of Oracle: http://www.myoracleguide.com/s/Returning.htm

    Finally have to amend the System.Data.DataRowVersion to System.Data.DataRowVersion.Proposed and the identity field (JournalID in my case) System.Data.ParameterDirection to System.Data.ParameterDirection.InputOutput.

    My JournalTableAdapter's InsertCommand Looked like this:
    this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand();
                this._adapter.InsertCommand.Connection = this.Connection;
                this._adapter.InsertCommand.CommandText = "INSERT INTO JOURNALACCOUNT\r\n                      (JOURNALID, ACCOUNTID, XSACTVAL" +
                    "UE, DISCOUNTEDPAYMENT)\r\nVALUES     (:JOURNALID, :ACCOUNTID, :XSACTVALUE, \'N\') RETURNING JOURNALID INTO :JOURNALID";
                this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("ACCOUNTID", System.Data.OracleClient.OracleType.Char, 15, System.Data.ParameterDirection.Input, "ACCOUNTID", System.Data.DataRowVersion.Current, false, null));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTVALUE", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.Input, "XSACTVALUE", System.Data.DataRowVersion.Current, false, null));
    

    and JournalAccountTableAdapter's InsertCommand Looked like this:

    this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand();
                this._adapter.InsertCommand.Connection = this.Connection;
                this._adapter.InsertCommand.CommandText = "INSERT INTO JKSBSCHEMA.JOURNAL(JOURNALID, XSACTDATE, XSACTDESC)\r\n VALUES (:JOURNALID, :XSACTDATE, :XSACTDESC) RETURNING JOURNALID INTO :JOURNALID";
                this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
                this._adapter.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.Both;
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDATE", System.Data.OracleClient.OracleType.DateTime, 7, System.Data.ParameterDirection.Input, "XSACTDATE", System.Data.DataRowVersion.Current, false, null));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDESC", System.Data.OracleClient.OracleType.VarChar, 40, System.Data.ParameterDirection.Input, "XSACTDESC", System.Data.DataRowVersion.Current, false, null));
    

    Now Upon clicking the Save button, I get the Identity value immediately rather than need to refill the dataset.

    Pls. feel free to respond on this, If I am not doing the ideal way.

    Thanks & Regards,


    Hifni Shahzard Nazeer M.
    Tuesday, August 18, 2009 1:33 PM
  • Thanks for the comprehensive answer. It seems that autoincrementing values are particularly complex for people to deal with. I agree the solution I gave was not performant. I don't have much experience with oracle and returning value back however.

    I will take this post and tack it on the end of the previous post so that people can read your answer to this complex problem.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, August 18, 2009 4:57 PM