Retrieving multiple datatables from a tableadapter insert query

13 Jun 2006 12:08 AM

Stephen

Hello,

I have in my database a trigger on a particular table that writes entries to
an audit table upon inserts & updates, and returns a set of AuditId's.

When I insert into this table, I need to retrieve both the set of AuditId's
and the scope_identity() of the newly-inserted row - but these are returned
in the form of two distinct result sets.

From all I see in the dataset GUI, and programmatically (being able to fill
a datatable with results but not a dataset), I can't tell if it's possible.

Any ideas? Thanks a lot!

13 Jun 2006 1:16 PM

W.G. Ryan - MVP

Stephen, can you post the query that's generating two tables?
Show quote

"Stephen" <Step***@discussions.microsoft.com> wrote in message
news:82E35EF1-861F-41C5-B44F-4D319F908323@microsoft.com...
> Hello,
>
> I have in my database a trigger on a particular table that writes entries
> to
> an audit table upon inserts & updates, and returns a set of AuditId's.
>
> When I insert into this table, I need to retrieve both the set of
> AuditId's
> and the scope_identity() of the newly-inserted row - but these are
> returned
> in the form of two distinct result sets.
>
> From all I see in the dataset GUI, and programmatically (being able to
> fill
> a datatable with results but not a dataset), I can't tell if it's
> possible.
>
> Any ideas? Thanks a lot!
>

13 Jun 2006 2:44 PM

Stephen

Sure! The query goes like this:

--Main Query--

insert into MyTable ( myname ) values ( 'Stephen' );
select scope_identity() as myid;

--The on insert trigger--

select * from MyTable

And this returns the newly-inserted identity as a temporary relation, as
well as the entire MyTable, so I end up with two distinct sets of results.
The tableadapter is not trapping both - it only traps the trigger results!

Thanks for your help,

Stephen

Show quote

"W.G. Ryan - MVP" wrote:

> Stephen, can you post the query that's generating two tables?
> "Stephen" <Step***@discussions.microsoft.com> wrote in message
> news:82E35EF1-861F-41C5-B44F-4D319F908323@microsoft.com...
> > Hello,
> >
> > I have in my database a trigger on a particular table that writes entries
> > to
> > an audit table upon inserts & updates, and returns a set of AuditId's.
> >
> > When I insert into this table, I need to retrieve both the set of
> > AuditId's
> > and the scope_identity() of the newly-inserted row - but these are
> > returned
> > in the form of two distinct result sets.
> >
> > From all I see in the dataset GUI, and programmatically (being able to
> > fill
> > a datatable with results but not a dataset), I can't tell if it's
> > possible.
> >
> > Any ideas? Thanks a lot!
> >
>
>
>