?
Solved

Inserting a new row in database with a Foriegn key

Posted on 2006-11-28
3
Medium Priority
?
239 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 1500 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month12 days, 13 hours left to enroll

777 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