Messed up constraint - INSERT fails

[duanecwilson]

Can anyone help me with this?

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Transactions.In_Out_Status". The conflict occurred in database "ComputerInventoryDCW", table "dbo.InOutStatus", column 'InOutID'. (#547)
[Microsoft][SQL Native Client][SQL Server]. The statement has been terminated. (#3621)

I upgraded an Access 2007 database to SQL Server. I get this sometimes (but not always) on a bound form when opening it, going to design mode, or even when clicking on one of the bound comboboxes. I haven't even tried to go to another record at this point. It's not supposed to even try an INSERT.

Nevertheless, when I close out the error and continue to do the data entry on the form, the record goes into the database.

Will it work if I break the relationships and create a new table and then create the relationships again? These are only lookups.

Duane Wilson

 

[gmmastros]

It's not supposed to even try an INSERT.

It must be inserting or else you would not get the error (just stating the obvious). I suggest that you run SQL Server Profiler to see what database activity is happening at the time you open your form. This should allow you to capture the insert so you can hopefully track it back to your app and determine what is causing it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[duanecwilson]

Thank you. I never ran the profiler before. I just started it but don't know what to look for. And is it possible to limit it to just the database you are using?

Also, do you have any idea how I could fix the tables assuming that there is a problem with the constraints?

Duane Wilson

 

[gmmastros]

I remember when I transitioned from access to SQL (some time around 2003 or 2004). I had the same mindset, which is basically distrustful of the database engine. My particular "hang-up" was centered around indexes. In my opinion, Access would corrupt indexes frequently, so I thought SQL would be the same way.

After a while, SQL Server earned my trust. No longer do I worry about corrupt indexes or "fixing tables" or "problems with constraints". The database will do what you tell it.

In your particular case, you really need to determine why an insert is happening. Profiler is the best way to do it.

When you start a trace, you will be presented with a Trace Properties window. Click the Events Selection tab and configure it to suit your needs.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[starsky51]

The simple answer is that the InOutID you're trying to insert into the table does not appear in the InOutStatus table.

 

[duanecwilson]

The simple answer is that the InOutID you're trying to insert into the table does not appear in the InOutStatus table."

The funny thing is that it is in the InOutStatus table. Reading from the SQL Management Screen:

dbo.InOutStatus (Columns):
InOutID (PK, int, not null)
InOut

dbo.Transactions (Columns):
TransactionID (PK, int, not null)
InOutID (FK, int, null)
EmpID (FK, int, null)
etc.

I have a screen shot of the error. This also happens in the Employee table on the SupervisorID field.

Both of these columns I think I remember tampering with or renaming or something but I can't remember exactly what I did. Is there a way to rebuild the tables and relationships easily?

Duane Wilson