How to use sequence number in insert command

[mindgame]

how can i use sequence number in insert comand so any time i click insert button a sequence number will fill up the id column
i am using vb.net and oracle
lets say i have a table with 2 colomn,
id integer
name string
what will be the insert comand?
please help. thanks

 

[davy_g]

Hi,

You should first know the name of sequence. If your table is test a good name for the sequence can be seq_test.
To know the next value you use seq_text.nextval, e.g.:

insert into test values (seq_text.nextval, "test");

 

[cjard]

Following on from davy's advice, You'd set the column to be autoincrement, with a seed and step of -1. When you run the insert command, the database will ignore whatever ID has been passed by the client, and calculate its own. I've seen best results with this when using stored procedures, but i'm given to understand it works for straight queries too

 

[davy_g]

At my work we use Oracle and in the programs (not written in vb .net) we use straight queries (just like mine above) to insert data and that works well.

Following on from davy's advice, You'd set the column to be autoincrement, with a seed and step of -1. When you run the insert command, the database will ignore whatever ID has been passed by the client, and calculate its own. I've seen best results with this when using stored procedures, but i'm given to understand it works for straight queries too

 

[cjard]

I was more getting at having that ID as a member of a datatable row, maybe even as a primary key, and submitting one value, but when the database calcs the proper value, the row is automatically updated with the new value.. I dont know how or if this occurs with straight queries; access users report that it does

 

[davy_g]

Can you give me an example of that? I'm not sure what you mean.

 

[cjard]

Er.. I guess.. It wont be exact, but you'll know what I mean:

> DESCR myTable
id, number
val, varchar

> CREATE SEQUENCE mySeq MIN 1 MAX 99999 START 1 INCBY 1

> PROCEDURE myTable_INSERT(inout_id IN OUT number, in_val IN VARCHAR) IS BEGIN
  IF inout_id < 0 THEN
    SELECT mySeq.NextVal INTO inout_id FROM DUAL;
  ENDIF;

  INSERT INTO myTable(id, val) VALUES(inout_id, in_val);

END myTable_INSERT;


In VB.NET:

myDataSet.MyTable.AddMyTableRow(-1, "Hello World")
Debug.Print(myDataSet.MyTable.Rows(0).id) ' --> prints -1

myDataSet.MyTable.AddMyTableRow(-3, "Goodbye World")
myDataSet.MyTable.AddMyTableRow(-247, "Hello Mum")

myTableAdapter.Update(myDataSet.MyTable)


myDataSet.MyTable now contains:
1, Hellow World
2, Goodbye World
3, Hello Mum


mySeq.CurrVal = 3
mySeq.NextVal = 4

 

[davy_g]

I've never worked with procedures before so this does not sound formiliar sorry

 

[cjard]

OK, well.. When you have worked with some procedures, then I guess it will sound familiar ?