CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    10

    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

  2. #2
    Join Date
    Dec 2001
    Posts
    6,332

    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?

  3. #3
    Join Date
    Jul 2009
    Posts
    10

    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

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Primary Key Conflict

    That's how it's supposed to work. You don't have to supply a value to auto-number fields.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Jul 2009
    Posts
    10

    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

  6. #6
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  7. #7
    Join Date
    Jul 2009
    Posts
    10

    Smile 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.

  8. #8
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Primary Key Conflict

    No, but MSDN can: http://msdn.microsoft.com/en-us/library/ms190243.aspx

    I just searced for your Exec query
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  9. #9
    Join Date
    Jul 2009
    Posts
    10

    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
  •  





Click Here to Expand Forum to Full Width

Featured