1 Reply Latest reply: Nov 4, 2006 12:44 PM by 527646 RSS

    Increment column

    542203
      Hi there ..

      past, I was use this way in form builder

      SELECT NVL(MAX(line_item_id),0) + 1
      INTO :ORDER_ITEMS.line_item_id
      FROM ORDER_ITEMS
      WHERE :ORDER_ITEMS.order_id = order_id


      Anybody have any idea how to apply same the code in visual studio 2005, or even using sequence please ..

      If you can help me i will be thankful ..
        • 1. Re: Increment column
          527646

          A. You need to create and define it on table/database as follows (assuming you already have a table):

          1. create/define a sequence for the column you wish to increment (autonumber)

          CREATE SEQUENCE SEQUENCE_NAME
          START WITH 1
          INCREMENT BY 1

          2. create a trigger that will automatically return/assign a value to the next sequence available.

          create or replace TRIGGER application_trigger
          BEFORE INSERT
          ON TABLENAME
          REFERENCING NEW AS NEW
          FOR EACH ROW
          BEGIN
          SELECT SEQUENCE_NAME.nextval INTO :NEW.COLUMN_NAME_AUTO_INCREMENT_ID FROM dual;
          END;


          B. If this is already set, regardless of which method you use (VB .NET, SQL INSERT,etc.) when inserting data, the trigger will automatically assign the auto increment for you.


          C. Going back to your question, under VB .NET (2005), insert the code under button_click and run the code.



          Dim oradb As String = "Data Source=(DESCRIPTION=" _
          + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVERNAME)(PORT=1521)))" _
          + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SID_NAME)));" _
          + "User Id=mylogin;Password=mypassword;"

          Dim conn As New OracleConnection(oradb)

          Try
          conn.Open()

          Dim cmd As New OracleCommand
          cmd.Connection = conn

          cmd.CommandText = "insert into TABLENAME (FIELD1,FIELD2,FIELD3) " _
          & " values('VALUE1','VALUE2',VALUE3)"
          cmd.ExecuteNonQuery()


          Catch ex As OracleException ' catches only Oracle errors
          Select Case ex.Number
          Case 1
          MessageBox.Show("Error attempting to insert duplicate data.")
          Case 12545
          MessageBox.Show("The database is unavailable.")
          Case Else
          MessageBox.Show("Database error: " + ex.Message.ToString())
          End Select

          Catch ex As Exception ' catches any error
          MessageBox.Show(ex.Message.ToString())
          Finally
          conn.Dispose()
          End Try



          HOPE THIS HELPS!!!