Transactions and foreign keys

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
Mon Jul 24 11:30:31 CDT 2006

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@discussions.microsoft.com> 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!

ScottW
Mon Jul 24 11:47:02 CDT 2006

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]" wrote:

> 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@discussions.microsoft.com> 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!
>

Mary
Tue Jul 25 20:16:12 CDT 2006

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

On Mon, 24 Jul 2006 09:47:02 -0700, ScottW
<ScottW@discussions.microsoft.com> wrote:

>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]" wrote:
>
>> 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@discussions.microsoft.com> 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!
>>