The INSERT statement conflicted with the FOREIGN KEY constraint

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ksenthilbabu
    New Member
    • Jul 2009
    • 1

    #1

    The INSERT statement conflicted with the FOREIGN KEY constraint

    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
  • ck9663
    Recognized Expert Specialist
    • Jun 2007
    • 2878

    #2
    Display all the values you're inserting on your detail table. You might have RefID as NULL.

    --- CK

    Comment

    • pankajvtcse
      New Member
      • Jul 2009
      • 10

      #3
      First you have to check NULL values for primary key.

      And now my suggestion is that you need to create two DIFFRERENT TRANSACTIONS to insert/update in parent and child tabels.

      Not sure but when you create a transaction and insert/update any rows in parent tables, these are not inserted until you have not COMMIT the transaction.
      Because of that when you try to insert rows in child table it does'nt found the parent row.

      So,you can commit parent table transaction ,create new transaction for child table then commit child table transaction.

      OR simply if it will not affect your requirement REMOVE transaction from code.


      Hope this will work.

      HAPPY CODING...

      -Pankaj Tambe

      Comment

      Working...