July 30, 2009 at 4:33 am
EDIT: Don't waste your time reading this, the problem was definitely between seat and keyboard!
Hi folks,
I hope it's okay to post this question here, my apologies if not.
I wonder if anyone can point me in the right direction on this issue:
Let's say I have an Order. An Order record can have one or more Item records as its child records.
If I insert a new Order record into the DB, I must also insert its child Items. If spInsertItem fails for any of the items, I want the whole thing rolled back, so of course I have to do the INSERTs as part of a single transaction.
Basic structure of tables:
Orders(Orderid int)
Items(Orderid int (FK), Itemid int)
I have the following C# code (in semi-pseudo code format here):
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
int newOrderid = InsertOrder(connection, transaction, ...);
InsertItems(newOrderid, connection, transaction, ...);
transaction.Commit();
}
catch (System.Data.SqlClient.SqlException exp)
{
transaction.Rollback();
throw exp;
}
finally
{
transaction.Dispose();
}
}
The problem is that I get errors: "The INSERT statement conflicted with the FOREIGN KEY constraint..." when the code attempts to insert the first Item
I guess the new Orderid has not been committed to the DB yet, which is why the FK error is returned - but I expected that as the statements are within the same transaction, the database would "know" that the new Orderid exists in an uncommitted state in the Orders table, and allow the Items insert to continue
I have checked that the FK in question is indeed the Orderid one, and not some other FK in the Items table.
I have stepped through the code, and have checked that the same transaction is allocated to the SqlCommand executed in each case.
I am at a loss as to what else to look for, and Google has proved unfruitful.
Any ideas?
Many thanks
July 30, 2009 at 11:11 am
July 30, 2009 at 2:40 pm
I would like to make a formal request to get my time back.
July 30, 2009 at 4:09 pm
How about showing us what you did to fix the problem? Someone else may have a similar problem and seeing what you did may help.
July 31, 2009 at 1:35 am
Lynn Pettis (7/30/2009)
How about showing us what you did to fix the problem? Someone else may have a similar problem and seeing what you did may help.
Normally I would, but in this case it's so entangled in the business logic it wouldn't make sense to anyone apart from me and one other member of my team.
I was baffled as I was passing the right id as a foreign key, and thus thought that I was misunderstanding how SqlTransaction was meant to work.
Turns out there was some misthought logic in how I was saving my child business object
I hope that makes sense!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply