Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Inserting a new row in database with a Foriegn key

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
sdelvisco
Asked:
sdelvisco
  • 2
1 Solution
 
HillwaaaCommented:
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
 
sdelviscoAuthor Commented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now