Search code examples
asp.netoracleoledbdetailsview

retrieve the autonumber in sqldatasource after the insert operation

In my asp.net application,I want to use the detailsview to show/insert/update data in the db.

After read the docs:

http://msdn.microsoft.com/en-us/library/fkzs2t3h%28v=VS.85%29

IT works well in sqlserver, but when I use oracle using the "oledb" provider,error occurs(I have replaced the named parameter like '@xxx' to '?').

It seems that the error is caused by this command:

InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                         SELECT @EmpID = SCOPE_IDENTITY()"

The command first insert the new data to the db ,then retrieve the 'autonumber' .

In oracle, it does not work, so I fix it like this:

InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                         SELECT @EmpID = seq_employees.currval() from dual"

But it still does not work.

Any idea?

Solution

  • See this question:

    Best practices: .NET: How to return PK against an oracle database?

    Basically, you use the RETURNING key INTO param, and set up an output parameter for param.