Error: the insert statement conflicted with the foreign key constraint

Question

1.00/5 (2 votes)

See more:

SQL-Server-2012

I AM TRYING TO INSERT DATA TO SQL DATABASE BUT I GETTING SOME ERROR
{
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Subject_Course". The conflict occurred in database "Course_Semester_Subject", table "dbo.Course", column 'Course_ID'.
}

VB.NET

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=ASHISH;Initial Catalog=Course_Semester_Subject;Integrated Security=True"
        con.Open()
        cmd.Connection = con


        If TextBox1.Text = "" Then
            MessageBox.Show("Please Enter Course ID")
        ElseIf TextBox2.Text = "" Then
            MessageBox.Show("Please Enter Course Name")
        ElseIf ComboBox1.Text = "" Then
            MessageBox.Show("Please Enter Duration")
        ElseIf TextBox3.Text = "" Then
            MessageBox.Show("Please Enter Semester ID")
        ElseIf TextBox4.Text = "" Then
            MessageBox.Show("Please Enter Semester Name")
        ElseIf TextBox5.Text = "" Then
            MessageBox.Show("Please Enter Subject")
        Else
            cmd.CommandText = (("INSERT INTO Course (Course_ID,Course_Name,Duration) VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & ComboBox1.Text & "')"))
            cmd.CommandText = (("INSERT INTO Semester (Course_ID,Semester_ID,Semester_Name) VALUES('" & TextBox1.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')"))
            cmd.CommandText = (("INSERT INTO Subject (Course_ID,Semester_ID,Subject) VALUES('" & TextBox1.Text & "','" & TextBox3.Text & "','" & TextBox5.Text & "')"))
            If cmd.ExecuteNonQuery() Then
                MessageBox.Show("Insertation Sucessful")
                ComboBox1.Text = ""
                TextBox1.Text = ""
                TextBox2.Text = ""
            Else
                MessageBox.Show("Error")

            End If
        End If
    End Sub
End Class

Posted 8-Feb-16 5:25am

User 12280876

Updated 8-Feb-16 5:37am

F-ES Sitecore

v3

Add a Solution

Comments

F-ES Sitecore 8-Feb-16 11:36am

You have two tables linked by a foreign key\table reference and are trying to insert a value that isn't valid. eg you are inserting 5 as Course_ID in Subject when there is no row in the Course table with an ID of 5.

We can't see your inputs or access your data so it's impossible to say anything more specific. Your code is also liable to SQL injection attacks, use paramterised queries instead.

Richard Deeming 8-Feb-16 12:09pm

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
SQL injection attack mechanics | Pluralsight [^]

Sergey Alexandrovich Kryukov 8-Feb-16 14:20pm

It could make a good formal answer. I already saw these links in some other post of you — they are good; I credited your comment in my answer.
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sergey Alexandrovich Kryukov · Answer 1 · 2016-02-08T08:20:00

Your approach is wrong from the very beginning. The query composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection.

This is how it works: http://xkcd.com/327.

Are you getting the idea? The string taken from a control can be anything, including… a fragment of SQL code.

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection.

With ADO.NET, use this: http://msdn.microsoft.com/en-us/library/ff648339.aspx.

Please see my past answers for some more detail:
EROR IN UPATE in com.ExecuteNonQuery();,
hi name is not displaying in name?.

See also the links provided by Richard Deeming in his comment to the question.

—SA

OriginalGriff · Answer 2 · 2016-02-08T05:45:00

Several things:
1) Don't do that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
2) Foreign key relationships exist to ensure that your database remains "safe" - that there are no "missing links" between tables which will mess up your application. For example, your can't create lines on an invoice unless both the products they reference exist, and the invoice itself exists to have a customer to send it to!
So try doing your INSERT operations in the other order: tables with Foreign Key values need to be INSERTED after the the rows they are trying to reference.
3) When you've done that, you need to execute the query in order to send the data to SQL - just doing a single ExecuteNonQuery will only submit the last one, not each of them!.
4) If you are doing multiple, interdependent, INSERTs you really need to use a Transaction around all of them together with a Try - Catch block to make sure that teh changes are only committed to the DB if they all succeed. If you don't your data will end up full of "partial" insert values which isn't good at all...

Error: the insert statement conflicted with the foreign key constraint

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0