Click here to Skip to main content
15,936,903 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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
Updated 8-Feb-16 5:37am
v3
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.
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

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...
 
Share this answer
 
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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900