SqlTransction, Insert Parent + child -> Foreign Key errors

[LFCfan]

I have the following (in semi-pseudo code)

using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        int newid = InsertParent(transaction, ...);                        
                        InsertChild(newid, transaction, ...);
                        transaction.Commit();
                    }
                    catch (System.Data.SqlClient.SqlException exp)
                    {
                        transaction.Rollback();
                        throw exp;
                    }
                    finally
                    {
                        transaction.Dispose();
                    }

It inserts a new Parent record, gets the new id of this record, and then inserts the child record (which has a foreign key on the id of the parent)

I get a foreign key error when running this code ("The INSERT statement conflicted with the FOREIGN KEY constraint...")

I thought that so long as I wrapped the queries within a transaction, that the insert of the child record would know that the Parent table contains a record with an id of value newid, even though the first insert hasn't been yet committed

Is it not possible to do such an insert like this, or am I fundamentally wrong somewhere?

Many thanks for any pointers

~LFCfan

 

[jmeckley]

are you sure you are getting the correct id back?

2 other pointers.
1. no need to explicitly dispose of the transaction in the finally block. the using block will do this automatically.
2. don't

throw exp;

just

throw;

what you are currently doing will swallow the full stack trace.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[LFCfan]

Thanks Jason (I'm still prototyping, sort of, hence things like throw exp!)

Yes, the right id is being returned.
I am also sure that the foreign key causing the issue is the parentid one (I should have clarified this before)



~LFCfan

 

[LFCfan]

Ah - unsurprisingly, SqlTransaction works exactly as one would expect, but while I was getting the right id back, I was then doing some other stuff in the business logic that was causing the problem



~LFCfan