The INSERT statement conflicted with the FOREIGN K

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

The INSERT statement conflicted with the FOREIGN K

Author

Topic

ksenthilbabu
Starting Member

4 Posts

Posted - 2009-07-03 : 05:59:00

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

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-03 : 06:35:14

What is the SQL code that you are running, and do you understand the principle of a foreign key

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-03 : 06:40:33

There is no point in front of the first AddNew method.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

ksenthilbabu
Starting Member

4 Posts

Posted - 2009-07-06 : 03:23:56

Hey Mr NeilG,

Thanks of your reply,

The Foreign Key concept is that only when Primary Key column has values then that value can be used for updating the detail table. think i am right.

I Tried with T-SQL it is working file.


	DECLARE @RefID INT
	BEGIN TRAN T1;
INSERT INTO [TestDB].[dbo].[tblCompany]([CompanyName]          ,[Amount])VALUES (@CName ,@Amt)    
	SELECT @RefID =SCOPE_IDENTITY() 
	BEGIN TRAN T2;
	INSERT INTO [TestDB].[dbo].[tblDetail]([RefID])
     VALUES (@RefID)
	COMMIT TRAN T2;
	COMMIT TRAN T1;

The same is done from from front End using Visual Basic 6.0. But it is not working. Here i am converting MYSQL Database to MSSQL2005 Database. In MySQL Database, MSAccess and also in Oracle the Front End code is working Fine without any Problem, But in onl MSSQL2005 it is generating errors.

After analysing with MSSQL2005 i found that adoMaster!RefID in the Front End Coding(VB6) is returning @@Identity Value and not the @SCOPE_IDENTITY Value of the corresponding Table within that SCOPE.

Please help me.

ksenthilbabu
Starting Member

4 Posts

Posted - 2009-07-06 : 03:26:18

Hey Peso,

I AM NOT ABLE TO UNDERSTAND YOUR REPLY.

quote:
There is no point in front of the first AddNew method.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources