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

  • Thread starter Thread starter needin4mation
  • Start date Start date
N

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.
 
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?
 
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.
 
Back
Top