Question Returning Id from Oracle Insert where Id is populated by trigger

[Rodimus]

Hi,

I found a great way to have an Oracle trigger populating a tables primary id, and get the id back to my vb code for child tables & display (see OBTAIN NEW ORACLE SEQUENCE NUMBER UPON INSERT - ASP.NET Forums).

> sql = String.Format("Insert Into TABLE(columns) VALUES ('{0}')returning id into :newid", value_variable)

which works fine where I create an sql command an execute as a non-query.

However, I'd like to add the 'return id into' statement into the INSERT command of a datasource datatable that I'm binding my edit controls to. I've tried adding the additional sql into the query and adding a parameter, however it never seems to get populated.

Does anyone have any suggestions?

Thanks in advance

 

[jmcilhinney]

I've never done it in Oracle but presumably it's the essentially same as SQL Server with a bit of a syntax adjustment. When you're saving the contents of a DataTable using a DataAdapter you don't want the ID returned via a parameter. You want it to be put back into the DataTable. To do that you need to execute a query after the INSERT, e.g.

INSERT INTO Person (FirstName, LastName) VALUES (@FirstName, @LastName); SELECT @@IDENTITY AS ID

That will populate the ID field of the DataRow with the ID that was generated. If you want to get other auto-generated values too you can change the query to:

SELECT * FROM Person WHERE ID = @@IDENTITY

Note that this question really has nothing to do with WinForms so I've moved it to the Oracle forum. Please post to the most appropriate forum in future.

 

[cjard]

However, I'd like to add the 'return id into' statement into the INSERT command of a datasource datatable that I'm binding my edit controls to. I've tried adding the additional sql into the query and adding a parameter, however it never seems to get populated.

Does anyone have any suggestions?

You cannot add anything toa query in a datatable because datatables don't contain queries, however, I understand what you mean..

When you make a strongly typed dataset in the dataset designer you opt for a new tableadapter and enter a query:

SELECT * FROM table

The designer creates an INSERT command for you, which you can edit.
Add the RETURNING <columns> INTO <variables> clause to the end. Note that you don't have to have a trigger do this, your insert command can also take the form:
INSERT INTO table (id, phoneNumber) VALUES(sequence.nextval, cleanPhoneNumberFunctionph)) RETURNING id, phoneNumber INTO :id, h
Whatever your query does to alter or create values can be put back into the parameters, but here's the kick:

Now go into the Parameters property collection and alter the :id and h parameters so that their DIRECTION is INPUTOUTPUT

-
If youre doing this with a dataadapter, I have never used a dataadapter but I imagine that the necessary is identical:
After altering your insert command, ensure that you change the relevant parameters to have an InputOutput direction.

DataAdapter (a tableadapter is a fancy dataadapter) basically traverses a datatable, loads the values out of it into parameters, and runs the query.
I would expect the dataadapter to read the updated values of all InputOutput parameters back out of the parameter collection and store them back in the row, before marking the row as unchanged - this is how my tableadapters do it and I'd expect DataAdapter to be no different (because it's the same code)

 

[cjard]

To do that you need to execute a query after the INSERT, e.g.

INSERT INTO Person (FirstName, LastName) VALUES (@FirstName, @LastName); SELECT @@IDENTITY AS ID

Oracle would explode at the presence of the semicolon - the returning clase is the way to go. It can also be used in UPDATEs affecting a single row, or multiple rows so long as a bulk collect method is specified