Number for ID field on form relating to Oracle table

[SystemCoder]

Hello All,

I am using an Oracle 10g database with Visual Basic 2008.
I want to create a trigger in SQL that will create generate an id for some of my primary key fields.

In this example I will refer to the Return_Payment table.
Here is its construct:

return_paymentno number not null(PK)
fuel_payment number
excess_mileage_payment number
damage_payment number
total number not null

I am setting up an example project database for a car hire company.

I am have already created the following code to create a sequence and this works fine.


1.
====
create sequence seq_returnpaymentno;

2.
====
insert into return_payment (return_paymentno, fuel_payment,
excess_milage_payment, damage_payment, total)
values(seq_returnpaymentno.nextval, 0, 0, 0, 0,


Now when I load up and run the form for this particulat table, it show the following:


RETURN PAYMENTNO: 1
FUEL PAYMENT: 0
EXCESS MILAGE PAYMENT: 0
DAMAGE PAYMENT: 0
TOTAL: 0


This dummy record is showing up fine in the form. However when I go to add a new record using the Yellow add button on the BindingNavigator. All field go blank.

What I need here is a trigger that show the number 2 in the return_paymentno field, and all the other fields blank to allow entry of a new record.

If you have an answer for this that you have used elsewhere please let me know. Alternatively let me know if it can be done on the Visual Basic side.
Just to let you know my knowledge lies more with Oracle more so than Visual Basic.

Thanks in advance,
SystemCoder

 

[cjard]

It doesnt really work like that.. Try this instead:

On your dataset designer (you are using the dataset designer, arent you? hope so, otherwise youre going to make yourlife much harder work ) you need to set the id column to AutoIncrement = true, AutoIncrementStep = -1, AutoIncrementSeed = -1

Every time you make a new record in the table it will get -1, -2, -3, -4 etc

Now either:
Make an ORACLE TRIGGER on the table to set your PK value:
CREATE TRIGGER tbl_trigger AFTER INSERT ON tbl FOR EACH ROW BEGIN
SELECT seq.nextval INTO :new.id FROM DUAL;
END;

Or modify your query thus:
INSERT INTO tbl(id, fields) VALUESidparameter, therparameters) RETURNING id INTO :idparameter

Ensure idparameter is set to inputoutput direction


What willhappen:
The -1, -2, -3, -4 will be sent to db and overwritten with the sequence value. The sequence valu will show up in your app after you save.


-

If you absolutely must see the ID that will be inserted, you MUST make another query that is "SELECT seq.nextval FROM Dual " and put it in your tableadapter, you MUST run this before (or during the datatable row updating event in vb.net) and push that returned value into your datatable yourself (turn off the autoincrement), and you MUST accept the loss of the ID if the user chooses not to insert the record (unless you want to get into storing unused ids in a table that you then get the next one from preferentially)