How to get ID field from inserted table in a join using BetterADO

From: Sydney Lotterby (sydney_at_infosearch.com)
Date: 06/29/04 

Date: Tue, 29 Jun 2004 11:27:11 -0500

(D5,MSsql2K, BetterADO)

In the query below (also see DDL for tables below that), ARMFproc is the
'Unique table' and I need to retrieve the procID for the inserted ARMFproc
record inserted from this joined query. In another thread titled "Re:
Technique question - Inserts for joined tables" in this .ado forum I was
told that:

> >1) This is a _joined_ query, so the AfterPost event is for the DataSet
of
> >the joined query. So, will DataSet.FieldByName('procID').asInteger
actually
> >give me the new ID field?
> It should.

Well, it doesn't!
Query Analyzer says the value should be 2000142
but in the AfterPost for the joined query, i = 2000074. This value is the
highest procID in the hit list of the original query.

Can anyone help?

----------------------------------------------
procedure TData2.qLUtravAfterPost(DataSet: TDataSet);
var i,p,seq: integer;
    s: string;
begin
  with Data do
  begin
    i := DataSet.FieldByName('procID').AsInteger;
...

SELECT scat.scat,prc.SourceType,scat.scatID,prc.Seq,prc.procID,
 prc.stepID,prc.stepX as Step,step.comments1,step.comments2,
 step.comments3,step.stepID,1 as Status
FROM tcaMetal.dbo.ARMFscat scat,
 tcaMetal.dbo.ARMFproc prc,
 tcaMetal.dbo.ARMFstep step
WHERE scat.scatID = 1980
AND scat.scatID=prc.SourceID
AND prc.SourceType = 'S'
AND step.stepID=prc.stepID
ORDER BY scat.scatID,prc.seq,step.step

CREATE TABLE [dbo].[ARMFPROC] (
 [SourceType] [char] (1) NULL ,
 [SourceID] [int] NULL ,
 [Seq] [int] NULL ,
 [stepID] [int] NULL ,
 [StepX] [int] NULL ,
 [procID] [int] IDENTITY (1, 1) NOT NULL ,
 [Rate] [money] NULL ,
 [Minutes] [float] NULL ,
 [Flat] [char] (1) NULL ,
 [Comments] [text] NULL ,
 [Status] [char] (1) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[ARMFSCAT] (
 [Scat] [varchar] (10) NULL ,
 [StatusFlg] [char] (1) NULL ,
 [Alternate] [char] (2) NULL ,
 [LongName1] [varchar] (40) NULL ,
 [LongName2] [varchar] (40) NULL ,
 [LongName3] [varchar] (40) NULL ,
 [ShortName] [varchar] (20) NULL ,
 [PercentRate] [float] NULL ,
 [ProRate] [char] (1) NULL ,
 [Taxable] [char] (1) NULL ,
 [InvPrintFlg] [char] (1) NULL ,
 [GLAccount] [char] (10) NULL ,
 [CertificationID] [int] NULL ,
 [Percent_1] [int] NULL ,
 [Percent_2] [int] NULL ,
 [Percent_3] [int] NULL ,
 [Percent_4] [int] NULL ,
 [Percent_5] [int] NULL ,
 [Percent_6] [int] NULL ,
 [Percent_7] [int] NULL ,
 [Percent_8] [int] NULL ,
 [Percent_9] [int] NULL ,
 [Percent_10] [int] NULL ,
 [Filler0] [char] (9) NULL ,
 [scatID] [int] IDENTITY (1, 1) NOT NULL ,
 [NextProcessSeq] [int] NULL ,
 [LastUpdateNo] [int] NULL ,
 [plinID] [int] NULL ,
 [Comments] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[ARMFSTEP] (
 [stepID] [int] IDENTITY (1, 1) NOT NULL ,
 [Step] [int] NULL ,
 [StatusFlg] [char] (1) NULL ,
 [DateLastChanged] [datetime] NULL ,
 [Comments1] [text] NULL ,
 [Comments2] [text] NULL ,
 [Comments3] [text] NULL ,
 [st1_commID] [int] NULL ,
 [st1_CommentLines] [int] NULL ,
 [st2_commID] [int] NULL ,
 [st2_CommentLines] [int] NULL ,
 [st3_commID] [int] NULL ,
 [st3_CommentLines] [int] NULL ,
 [quipID] [int] NULL ,
 [Rate] [money] NULL ,
 [Minutes] [int] NULL ,
 [Flat] [char] (1) NULL ,
 [stepIDX] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 CREATE INDEX [SourceIdx] ON [dbo].[ARMFPROC]([SourceType], [SourceID]) ON
[PRIMARY]
GO

 CREATE INDEX [StepIdx] ON [dbo].[ARMFPROC]([StepX]) ON [PRIMARY]
GO

 CREATE INDEX [IX_ARMFPROC] ON [dbo].[ARMFPROC]([stepID]) ON [PRIMARY]
GO

 CREATE INDEX [IX_ARMFPROC_1] ON [dbo].[ARMFPROC]([stepID]) ON [PRIMARY]
GO

 CREATE INDEX [scatIdx] ON [dbo].[ARMFSCAT]([Scat]) ON [PRIMARY]
GO

 CREATE INDEX [LongNameIdx] ON [dbo].[ARMFSCAT]([LongName1], [LongName2],
[LongName3]) ON [PRIMARY]
GO

 CREATE INDEX [CertIdx] ON [dbo].[ARMFSCAT]([CertificationID]) ON [PRIMARY]
GO

 CREATE INDEX [StepIdx] ON [dbo].[ARMFSTEP]([Step]) ON [PRIMARY]
GO

Relevant Pages

  • Return Hierarchical Data From One Table (with a JOIN to a second table)
    ... I would appreciate help in designing an efficient query that will retrieve ... [SequenceInRank] ... - specifies which row is the logical parent of the ...
    (microsoft.public.sqlserver.programming)
  • [GIT PULL] post-2.6.17-rc1 fixes
    ... Wait for join to finish before freeing mcast struct ... int ret; ... Otherwise it is a query ID that can be used to cancel ...
    (Linux-Kernel)
  • Re: Need assistance with query
    ... Using that query, you can't do that. ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • Rewrite Query for tuning.
    ... The Query returns rows. ... CONSTRAINT PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.programming)
  • Re: NULLs: theoretical problems?
    ... create view C(x int, y int) ... as select blah as x, ... the same supertype, int), the query is completely valid, and returns neither ... Don't you mean subordinate clauses in the WHERE clause? ...
    (comp.databases.theory)