Solved

Inserting a new row in database with a Foriegn key

Posted on 2006-11-28
3
237 Views
Last Modified: 2010-04-23
I am attempting to Insert new records in a database table that had the first column as a foriegn key to another table. The BadgeId field of the BadgeUserFieldTable is a foriegn key associated with the Id field of the PersonTable. When I test my code, I get a SQL failure:

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'userFieldTable_PersonTable_FK'. The conflict occurred in database 'SecurePerfect', table 'PersonTable', column 'Id'.

My code for the Command string is :        

strInsertBadgeUser = "INSERT INTO BadgeUserField (BadgeId, Modified) VALUES (" & rowPosition & ", " & BUF_Modified & ")"

I call the command as follows:

'UserFieldTable Insert
            Dim cbTransferUserFieldData As New OleDbCommandBuilder
            Dim InsertUserFieldComm As New OleDbCommand
            cbTransferUserFieldData.DataAdapter = UserFieldTableDA
            InsertUserFieldComm.CommandText = strInsertUserField
            UserFieldTableDA.InsertCommand = InsertUserFieldComm

            UserFieldTableDA.Update(UserFieldDS)
            UserFieldTableDA.InsertCommand.Connection = New OleDbConnection
            UserFieldTableDA.InsertCommand.Connection = DestConn
            InsertUserFieldComm.ExecuteNonQuery()

My code inserts a row in the PersonTable before it reaches the Insert command for this particular table. I have tried leaving the PersonId column out, but it is not a nullable column, so that throws an error.

I need to know how to insert a row of data into this table without the error. I have errors on other tables that have the same type of foriegn key on a field. But, if I can fix this one, the same fix will work for the other tables.
0
Comment
Question by:sdelvisco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 18033015
Hi sdelvisco,

There is a similar issue posted:

The accepted answer for which would indicate that either your foreign key constraint on the BadgeUserField table is relating to the 'PersonTable' table, or you have a trigger on the BadgeUserField table which is doing something that violates your constraint.

Can you tell us what the constraint does?

Cheers,
Hillwaaa
0
 
LVL 16

Accepted Solution

by:
Hillwaaa earned 500 total points
ID: 18033117
0
 

Author Comment

by:sdelvisco
ID: 18034823
Hey, thanks for the fast response. Your answer pointed me in the correct direction, so I accepted it.

I ended up removing the foriegn key constraints for all of the tables I was inserting into, inserting the new rows, and then creating the same foriegn key constraints after the inserting is done. I re-created them just so the database would be back to origional state, since it is a third-party DB.

Seems like everything is working fine.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question