Oracle Primary Key Question

ChileHead

New member
Joined
Feb 1, 2010
Messages
1
Programming Experience
10+
I am having some difficulty coming up with a clean design for inserting new records into an oracle database using VS 2008, Datasets and the TableAdapterManager. The Primary Key of the Oracle table is ID and is defined as a number. My original intention was to create a Before Insert trigger on the table that would replace a Null Id before insert with the sequence.nextval. However I could not seem to get past the AllowDBNull = false, even though I would set it to true via the DataSet designer. I also tried to remove the ID field from the Insert Statement, but was still getting null issues.

I ended up adding code to the TableNewRow event to get the ID from the oracle sequence, but was wondering if there was something that I was missing which was causing the allow null errors during the Validate or EndEdit functions, even when I tried setting AllowDBNull = True.

Any help or advice would be greatly appreciated.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
If your local datatable defines the column as a primary key it will never allow nulls, and it is not because of the AllowDBNull property

Instead, set the AutoIncrement = true AutoIncrementSeed = -1 AutoIncrementStep = -1 and all your rows will get an autonumber e.g. -1, -2, -3 etc

Do your trigger in oracle, and set the column using the sequence.nextval. Ensure when you make the tableadapter for this table that "Refresh the Dataset" is ticked (in advanced button on one of the screens) and your row will have the ID oracle creates after it is saved

Row:
-1, John, Smith

(Save to oracle)

Row:
2078, John, Smith


Any datatables that have a datarelation with this table, will update their foregn keys too, if the relation is a foreign key type in the dataset


Also note that you can alter the INSERT statement of the tableadapter instead of making a trigger:

INSERT INTO TABLE(pk, col) VALUES(sequence.nextval, :col) RETURNING pk INTO :pk

VS might whine it doesnt like the syntax, but make pk an inputoutput direction type and the value will come back.

I'd use the trigger route
 
Top Bottom