Solved

Inserting a new row in database with a Foriegn key

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

808 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