The INSERT statement conflicted with the FOREIGN KEY constraint

Question

0.00/5 (No votes)

See more:

Greetings,
I have a problem with visual studio, I'm trying to insert values into a table with a foreing key, but I keep getting this error

The INSERT statement conflicted with the FOREIGN KEY constraint"FK__sales__id_book_1920BFC". THe conflict ocurred in database "....Bookstore.mdf", table"dbo.INVENTARY", colum 'id_book'.
The statement has been terminated.

I've searched for about 2 hours now and all the answers I found said the same "you need to have the table with the Key that the FK is trying to access to" and "you need to have at least 1 record in the parent table which has the key colum".

I do have them, and even so I'm getting that error.

Here's my code:

Public Sub AddMember(id_book As Integer, member As Boolean, id_member As Integer, date_sale As Date, time_sale As Date)
    Try
        Dim strInsert As String = "INSERT INTO SALES (id_book,member,id_member,date_sale,time_sale) Values " & _
            "(@id_book,@member,@id_member,@date_sale,@time_sale)"
        SQLCon.Open()
        SQLCmd = New SqlCommand(strInsert, SQLCon)
        SQLCmd.Parameters.AddWithValue("id_book", id_book)
        SQLCmd.Parameters.AddWithValue("member", member)
        SQLCmd.Parameters.AddWithValue("id_member", id_member)
        SQLCmd.Parameters.AddWithValue("date_sale", date_sale)
        SQLCmd.Parameters.AddWithValue("time_sale", time_sale)
        SQLCmd.ExecuteNonQuery()
        SQLCon.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Any other ideas?

Posted 28-Oct-14 18:38pm

Anzeis

Add a Solution

Comments

George Jonsson 29-Oct-14 1:05am

The problem you have is not in Visual Studio, it is in the database.
You probably try to insert a new row in the table INVENTARY (maybe should be INVENTORY) and the value for in the column indexed by the name FK__sales__id_book_1920BFC does not have a match in the referenced table.
You should show the structure of the two tables and and some sample data, in order to get further help.

Bernhard Hiller 29-Oct-14 3:21am

Are you sure you connect to the "correct" database from your application? Perhaps a different database file is fooling you...

3 solutions

Solution 1

Hello ,
The error clear says that You're trying to insert a value into a column that has a FK constraint on it that doesn't match any values in the lookup table.So , check "id_book" column of which the table references before insert the data .

thanks

Posted 28-Oct-14 19:02pm

Animesh Datta

Comments

Anzeis 29-Oct-14 1:29am

I'm trying to insert the value '1' into id_book of the table SALES,
the table INVENTORY has only 1 record, and the id_book for it '1'.

I have tried it using SQL Server to run this query and it worked perfectly:
INSERT INTO SALES (id_book,member,date_sale,time_sale)
Values (1,0,'01/01/2014','09:09:09 AM')

I didn't get any error when I ran that query,
but I keep getting that error when I try it on Visual Studio.

Animesh Datta 29-Oct-14 1:53am

There is no problem in visual studio .Try passing 1 instead of "id_column". I think the problem is in your column value .First Check that the "id_book" is 1 from your code behind .

Anzeis 29-Oct-14 2:10am

I already checked, it is.

I also tried something more now, I deleted the table SALES and created it again with no FK this time, and tried to debug it again, it keeps showing me the same message:

The INSERT statement conflicted with the FOREIGN KEY constraint"FK__sales__id_book_1920BFC". THe conflict ocurred in database "....Bookstore.mdf", table"dbo.INVENTARY", colum 'id_book'.
The statement has been terminated.

I checked on Visual Studio, on the DataSet, to see if the constraint was still there, but it wasn't, there are no constraints left and it keeps showing me that error.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2014-10-29T16:25:00

Solution 3

Read this to understand the root cause: SQL FOREIGN KEY Constraint[^]

Posted 29-Oct-14 16:25pm

Peter Leow

Anzeis · Accepted Answer · 2014-10-29T16:04:00

Solution 2

I gave up and dropped the database and made a new one, then I removed the DataSet from my application on Visual Studio and created it again.

Posted 29-Oct-14 16:04pm

Anzeis

The INSERT statement conflicted with the FOREIGN KEY constraint

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

The INSERT statement conflicted with the FOREIGN KEY constraint

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us