TMsStoredProc not working with Transactions?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
cfrazier007
Posts: 2
Joined: Tue 20 Jan 2009 15:22

TMsStoredProc not working with Transactions?

Post by cfrazier007 » Tue 20 Jan 2009 15:28

EMSError - "The statement has been terminated. The INSERT statement conflicted with the FOREIGN KEY constraint 'fkHSTCPD_HSTALL'.
The conflict occurred in database 'ds_WMS_dev', table 'dbo.HSTALL', column 'HSTALLID'."


I have 2 TMSStoredProcs that call SPs to insert records into a Parent and Child Table.

1st inserts into Parent table (HSTALL). It also returns the primary key (autoinc) of the table.

2nd inserts into child table (HSTCPD), using the HSTALLID from the 1st as a parameter.

This code works fine outside of a transaction. However, when I use transactions (SqlConnection.StartTransaction), I get the error above.

I am using Delphi 2007 and I updated to 4.70.0.44 this morning.

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 21 Jan 2009 12:59

I can not reproduce the problem.
Please send me a complete small sample to dmitryg*devart*com to demonstrate it, including a script to create and fill tables and stored procedures.

cfrazier007
Posts: 2
Joined: Tue 20 Jan 2009 15:22

Post by cfrazier007 » Wed 21 Jan 2009 16:20

I think I found the problem. We had an After Insert trigger on the main table. the Identity we were returning was not scoped (@@IDENTITY).

Thanks for the help.

Post Reply