Problems with getting identity after inserting by TMSQuery

[Alexander_73]

SDAC latest 4.35.1.16 version. MS SQL Server 2005.

Table1:

Code: Select all

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AA_MailTemplates](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL CONSTRAINT [DF_AA_MailTemplates_UserID]  DEFAULT ((-1)),
	[FormID] [int] NOT NULL,
	[Title] [varchar](100) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL,
 CONSTRAINT [PK_AA_MailTemplates] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Table 2:

Code: Select all

CREATE TABLE [dbo].[AA_MailTemplatesUserSettings](
	[MailTemplateID] [int] NOT NULL,
	[PersonID] [int] NOT NULL,
	[ShowMAPI] [bit] NOT NULL CONSTRAINT [DF_AA_MailTemplatesUserSettings_ShowMAPI]  DEFAULT ((1)),
	[SendByRS] [bit] NOT NULL CONSTRAINT [DF_AA_MailTemplatesUserSettings_SendByRS]  DEFAULT ((0)),
	[ShowEditor] [bit] NOT NULL CONSTRAINT [DF_AA_MailTemplatesUserSettings_ShowEditor]  DEFAULT ((1)),
 CONSTRAINT [PK_AA_MailTemplatesUserSettings] PRIMARY KEY CLUSTERED 
(
	[MailTemplateID] ASC,
	[PersonID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Table AA_MailTemplatesUserSettings is foreign to AA_MailTemplates:

Code: Select all

ALTER TABLE [dbo].[AA_MailTemplatesUserSettings]  WITH CHECK ADD  CONSTRAINT [FK_AA_MailTemplatesUserSettings_AA_MailTemplates] FOREIGN KEY([MailTemplateID])
REFERENCES [dbo].[AA_MailTemplates] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE

2 objects TMSQuery in program - (select * from aa_mailtemplates) and (select * from aa_mailtemplatesusersettings). All sqls generated, insertsql for AA_MailTemplates with

Code: Select all

SET :ID = SCOPE_IDENTITY()

.
UpdatingTableName used. No master-detail.
At inserting of record through first TMSQuery any of 3 ways specified in the SDAC Help and on the forum does not help to get value of field ID from AA_MailTemplates - it always = 0 until the Query (though record shows in DBGrid) that causes a problem with an insert in the second table will be closed and opened. Only removal of dependence (FK_AA_MailTemplatesUserSettings_AA_MailTemplates) between tables helps with the getting of ID.
Please pay attention to this problem.

[Antaeus]

Enable the ReturnParams option of TMSQuery and place the next line into the BeforeUpdateExecute event handler:

Code: Select all

  Params.ParamByName('ID').ParamType := ptInputOutput;

[Alexander_73]

I tried this variant (it too is described in Help). No effect.

[Antaeus]

Please send me a complete small sample at sdac*crlab*com to demonstrate the problem. Also specify the exact version of your IDE.