The INSERT statement conflicted with the FOREIGN KEY constraint
 

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

Re: The INSERT statement conflicted with the FOREIGN KEY constraint
 

Please wrap your posts in [code] tags in the future.

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

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)

Re: The INSERT statement conflicted with the FOREIGN KEY constraint
 

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