Hey All,

I am using MSSQL -2005 with VB6.
I have created a master table tblCompany and detail Table tblDetail having foreign key relationship.
When i try to insert a value within a TRANSACTION I am getting Error No. -2147217873 at Line No. 0 (The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblDetail_tblCompany". The conflict occurred in database "DBTest", table "dbo.tblCompany", column RefID.) .
please help me to solve this problem.


CREATE TABLE [dbo].[tblCompany](
[RefID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Amount] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblCompany_Amount] DEFAULT ((0)),
CONSTRAINT [PK_tblCompany_1] PRIMARY KEY CLUSTERED
(
[RefID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tblDetail](
[DetailID] [int] IDENTITY(1,1) NOT NULL,
[RefID] [int] NULL,
[Amount] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblDetail_Amount] DEFAULT ((0)),
CONSTRAINT [PK_tblDetail] PRIMARY KEY CLUSTERED
(
[DetailID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tblDetail] WITH CHECK ADD CONSTRAINT [FK_tblDetail_tblCompany] FOREIGN KEY([RefID])
REFERENCES [dbo].[tblCompany] ([RefID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblDetail] CHECK CONSTRAINT [FK_tblDetail_tblCompany]



CODE :

Dim intID as Integer

adoconn.BeginTrans
with adoRsMaster
.Open "Select * from tblCompany Where 1=2",adoconn,adOpenDynamic,adLockOptimistic
addnew
!CompanyName="Sample"
!Amount =2500
.update
intID = !RefID
end with
with adorsDetail
.Open "Select * from tblDetail Where 1=2",adoconn,adOpenDynamic,adLockOptimistic
.addnew
!RefID = intID
!Amount = 2500
.update
end with
adoconn.commitTrans

It means exactly what it says....
When you have the FK, you have to follow the rules...
Before inserting into child table it has to exist in parent table...
Make sure you have the record in tblcompany table before inserting into tbldetail table...

Hey Mr. Mohammedu,

Thanks for your reply.

I am only inserting into the detail table (tblDetail) after getting the Primay key value from the Primary table (tblCompany).

Primary Key tABLE : tblCompany
Detail Table : tblDetail


The above code is working fine with MSACCESS, MYSQL and ORACLE. but it is not working with MSSQL2005.

I found after analysing that adoMaster!RefID is generating @@IDENTITY Value instead of to return @@SCOPE_IDENTITY.

Please help me