The INSERT statement conflicted with the FOREIGN KEY constraint

[ksenthilbabu]

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

[sknake]

Please wrap your posts in

tags in the future.

Can you post the raw SQL you're using here? I created your tables and was able to insert OK.

[code=sql]
Insert Into tblCompany (CompanyName, Amount) Values ('Sample', 2500)
Declare @id int
Set @id = Cast(SCOPE_IDENTITY() as int)

Insert Into tblDetail (RefId, Amount) Values (@id, 2500)

[ksenthilbabu]

Hey sknake,
Thanks for your Reply,

I Found that the adoMaster!RefID is returning the value not from the specified table but it is returning then max of IDENTITY Value from the Database.

For Example
Table Name: tblCompany has Primary(identity) Column RefID with last Value of 10
Table Name: tblDetail has Primary(identity) Column DetailID with Last Value 20
Table Name: tblValue has primary(identity) Column ValueID with Last Value 50

After Updating tblCompany with a new record adoMaster!RefID is returning Value 51 which is the next identity Value of tblValue Table instead of returning 11 Which is the next identity Value of tblCompany.

I found this when i Inserted SQL statement after adomaster.update
SQL Statement Inserted :

adoID.Open "Select Max(RefID) from tblCompany", adoConn, adOpenForwardOnly, adLockReadOnly
iRefID = adoID(0).Value

Here adoID(0).Value is returning 11 that has to be return by adomaster!RefID after Updating with new record.

Please guide me