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
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
Comment