Inserting a new row in database with a Foriegn key
Posted on 2006-11-28
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:
Dim cbTransferUserFieldData As New OleDbCommandBuilder
Dim InsertUserFieldComm As New OleDbCommand
cbTransferUserFieldData.DataAdapter = UserFieldTableDA
InsertUserFieldComm.CommandText = strInsertUserField
UserFieldTableDA.InsertCommand = InsertUserFieldComm
UserFieldTableDA.InsertCommand.Connection = New OleDbConnection
UserFieldTableDA.InsertCommand.Connection = DestConn
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.