-
July 3rd, 2009, 08:37 AM
#1
Primary Key Conflict
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.
Code:
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 = adoRsMaster!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
After analysis i found that "intID = adoRsMaster!RefID" is returning a different value after calling update which is not generated in MSSQL 2005, When Compared with
Code:
adoID.Open "Select Max(RefID) from tblCompany", adoConn, adOpenForwardOnly, adLockReadOnly
iRefID = adoID(0).Value
if i call the above code before updating Detail for getting RefID of tblCompany table.
Please help me to solve this problem
Last edited by WizBang; July 3rd, 2009 at 12:09 PM.
Reason: Added [code] tags
-
July 3rd, 2009, 12:24 PM
#2
Re: Primary Key Conflict
Is the RefID field supposed to auto-increment? Can you elaborate a bit more?
Suppose you open the recordset on the RefID field, ascending or descending, and use MoveFirst or MoveLast to retrieve the RefID value before AddNew?
Please remember to rate the posts and threads that you find useful.
How can something be both new and improved at the same time?
-
July 4th, 2009, 02:08 AM
#3
Re: Primary Key Conflict
Hey WizBang,
Thanks for your help.
Column RefID if the tblCompany is a Primary Key, AutoIncrement(Starting with 1 , Increment with 1).
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 MSSQL 2005 Database.
For Example
Table Name: tblCompany has Primary(identity), AutoIncrement(Starting with 1 , Increment with 1) Column RefID with last Value of 10
Table Name: tblDetail has Primary(identity),AutoIncrement(Starting with 1 , Increment with 1) Column DetailID with Last Value 20
Table Name: tblValue has primary(identity),AutoIncrement(Starting with 1 , Increment with 1) 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
-
July 4th, 2009, 12:04 PM
#4
Re: Primary Key Conflict
That's how it's supposed to work. You don't have to supply a value to auto-number fields.
-
July 6th, 2009, 01:50 AM
#5
Re: Primary Key Conflict
Hey Dglienna,
Thanks for your reply,
With reference to your reply, i am not supplying a value to Auto-Number field. What i need is to fetch the auto-number Value just generated when a table is updated with a new records.
By doing continuous research, i Found that adoMaster!RefID is not returning the auto-number Generated from that table on which i update with new records, but it is retriving some other value after updating with new record.
IN ADO recordset!PrimaryColumn must return a value from the table on which it is been updated.
Please help me,
with thanks
k.senthil babu
-
July 6th, 2009, 10:59 AM
#6
Re: Primary Key Conflict
You could just find the highest number, and use that. I thought I knew a trick to get the key, but couldn't find it.
-
July 6th, 2009, 01:14 PM
#7
Re: Primary Key Conflict
Hey Dglienna,
Again i Thank for your reply,
I'm converting MysqL Database to MSSQL Database.
I Found the solution for the Problem atlast..........
Today,What i did was, i just converted the mysql database to MSDE Database and then Upsized to MSSQL 2005. In that i Found a New Property being added to the MSSQL2005 Database when UPSIZED using Ms Access.
What is found is
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblCompany'
is added to the create syntax when viewed through at the last as
CREATE TABLE [dbo].[tblCompany](
[RefID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Amount] [decimal](18, 2) NULL CONSTRAINT [DF_dbo_tblCompany_Amount] DEFAULT ((0)),
CONSTRAINT [PK_dbo_tblCompany] PRIMARY KEY CLUSTERED
(
[RefID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblCompany'
Now i am able to fetch the autonumber generated when updated with New Record.
I am not able to understand this code
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblCompany'
which is adding to the create syntax can you explain what actually is this.
-
July 6th, 2009, 01:18 PM
#8
Re: Primary Key Conflict
No, but MSDN can: http://msdn.microsoft.com/en-us/library/ms190243.aspx
I just searced for your Exec query
-
July 6th, 2009, 01:28 PM
#9
Re: Primary Key Conflict
Hey dglienna,
Again i thanks for your reply.
Iam just looking at MSDN using your link.
thanks a lot...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|