Transactions and foreign keys

[Guest]

Hi All,

I've seen a similar post regarding this, with no suggestions other than a
third party tool.

What I am using is an OleDbConnection / Transaction, because it has to be
SQL Server and MS Access compatible. The code inserts a record in the parent
table, then loops through a bunch of child tables, inserting the related
records. In SQL Server, it throws an exception, stating it violates the
foreign key, in Access it all works fine.


Do the transactions not support this? It seems odd to me that the
transaction would not know about it's own insertions...

Any help would be great!

 

[Mary Chipman [MSFT]]

SQL Server handles explicit transactions differently than Access/Jet.
You need to perform the insert into the parent, retrieve the new PK (I
assume this is an identity column), and then use it to create the
child rows. This is best done in a stored procedure that handles all
of the insert operations in an explicit transaction so that they are
all inserted or all rolled back in case of any error.

--Mary

 

[Guest]

I am using the new key for all the following inserts. I don't think I can
really use stored procedures, because the data is all dynamic, and the tables
it is being copied from may or may not have data in them. I still don't
understand why the transaction can't keep track of the inserts... any other
ideas?

Thanks

 

[Mary Chipman [MSFT]]

No, no other ideas. You have to work with the features and
functionality found in the database engine you're using. There's no
way to do an end run around it other than writing your own code.

--Mary