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_t blCompany". 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_Gene ral_CP1_CI_AS NOT NULL,
[Amount] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblCompany_A mount] 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_Am ount] 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_tb lCompany] FOREIGN KEY([RefID])
REFERENCES [dbo].[tblCompany] ([RefID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblDetail] CHECK CONSTRAINT [FK_tblDetail_tb lCompany]



CODE :

Dim intID as Integer

adoconn.BeginTr ans
with adoRsMaster
.Open "Select * from tblCompany Where 1=2",adoconn,ad OpenDynamic,adL ockOptimistic
addnew
!CompanyName="S ample"
!Amount =2500
.update
intID = !RefID
end with
with adorsDetail
.Open "Select * from tblDetail Where 1=2",adoconn,ad OpenDynamic,adL ockOptimistic
.addnew
!RefID = intID
!Amount = 2500
.update
end with
adoconn.commitT rans

[ck9663]

Display all the values you're inserting on your detail table. You might have RefID as NULL.

--- CK

[pankajvtcse]

First you have to check NULL values for primary key.

And now my suggestion is that you need to create two DIFFRERENT TRANSACTIONS to insert/update in parent and child tabels.

Not sure but when you create a transaction and insert/update any rows in parent tables, these are not inserted until you have not COMMIT the transaction.
Because of that when you try to insert rows in child table it does'nt found the parent row.

So,you can commit parent table transaction ,create new transaction for child table then commit child table transaction.

OR simply if it will not affect your requirement REMOVE transaction from code.


Hope this will work.

HAPPY CODING...

-Pankaj Tambe