0

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
2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by ksenthilbabu
0

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)
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.