Solved

Inserting a new row in database with a Foriegn key

Posted on 2006-11-28
3
235 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now