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