Re: Transactions and foreign keys
- From: "Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 24 Jul 2006 12:30:31 -0400
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
On Thu, 20 Jul 2006 12:35:02 -0700, ScottW
<ScottW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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!
- Prev by Date: Re: Connection Pooling
- Next by Date: ANN: DBISAM ADO.Net Data Provider 2.1.0 is now available!
- Previous by thread: Select distinct ?
- Next by thread: Re: Transactions and foreign keys
- Index(es):
Relevant Pages
|
