How do I retrieve the scope_identity() with the tableadapter?

[needin4mation]

Hi, I have generated my queries and selected the refresh the table
option. This creates a select after the insert to get the
scope_identity() so that I can use that key value in another table,
i.e. foreign keys. I have verified the existence of the select in the
DAL .xsd file.

I simply do not know how to get that value, the scope_identity() value.
I know that it is selecting the value for me, but I have no idea how
to get that selected value after my insert. Am I supposed to get it in
my codefile somehow? Is there a way to use it in the DAL? Not sure
what the proper practice is here.

I have seen several places where stored procedures were turned to.
This does not make sense, totally, to me, but more like a workaround.
It seems that if I have an option to generate the sql to get the ID
field, then there must be a way to access the data. I don't mind
coding, but I do not want a sproc (no offense to sproc advocates. I
just want to see how to do this dynamically with Visual Studio 2005.)

Thank you for any help.

 

[needin4mation]

Is the only way to use the scalar value that is returned by breaking
out of the DAL and using code behind to grab the value?

 

[needin4mation]

I found that I could simply enter more SQL in the DAL to accomplish
this like so:

After my initial insert in the database:

INSERT INTO table2 ([fk_employee_id]) SELECT EmployeeID FROM Employees
WHERE (EmployeeID = SCOPE_IDENTITY())

I put this in the xsd after my INSERT. To cascade deletes, I add an
additional delete to the delete command, etc.