When using begintrans, i am getting a foreign key error on insertion

[sureshvijayan]

Hello,

I have a master table and details table and set primary key foreign key relationship with master and details table

Now from vb.net, i use begin trans and start inserting data,

I have two insert statements, one to master and another to detail

the detail table insertion of row depends on the master table row updated just above.

When inserting the detail table row, it pops up with a foreign key error

if i wont use begin trans, all data insert properly to master and detail table.

how can we overcome this issue.

Database : SQl Server

thankzzzzzzzz

[Kasracer]

Transactions should not have an affect, AFAIK, on inserts. You'll need to post some code so we can see what you're doing to help otherwise it's just random guesses.

[alex_read]

Are you calling the insert to the master table before the insert to the detail table? Let's just get that one out the way, I'd imagine so but always best to check.

Anyhow, if so - what about passing master and detail info to an SQL Server stored procedure?

You can wrap the insert to the master table in one transaction, then check the @@ERROR and @@IDENTITY system values to see if this was successful.

If so then you can perform the insert into the detail table without any worries (and the '@IDENTITY' variable will contain the ID of the recently inserted master table record if you need this for the second insert).

If the master table insert wasn't executed successfully, you could then choose to rollback that first transaction and not execute the update to the detail table then...