ASP.Net Error: The INSERT statement conflicted with the FOREIGN KEY constraint

Last Reply 3 months ago By pandeyism

Posted 3 months ago

i have two table one table is Department(DeptID,DeptName and second table is Subdepartment (Subdeptid,SubDeptName,Deptid)

i have made link of foreign key of Deptid with subdepartment, now i am facing error.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SubDepartment_SubDepartment". The conflict occurred in database "AttendanceManagement", table "dbo.Department", column

        protected void Page_Load(object sender, EventArgs e)
        {
            {
                con = new SqlConnection("Data Source=IT;Integrated Security=SSPI;Initial Catalog=AttendanceManagement");
                using (SqlCommand cmd = new SqlCommand("SELECT DeptID, DeptName FROM Department"))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    DropDownList1.DataSource = cmd.ExecuteReader();
                    DropDownList1.DataTextField = "DeptName";
                    DropDownList1.DataValueField = "DeptID";
                    DropDownList1.DataBind();
                    con.Close();
                }
            }
            DropDownList1.Items.Insert(0, new ListItem("--Select Customer--", "0"));
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string DeptName = txtsubdept.Text;
            string DeptID = DropDownList1.SelectedItem.Text;

            string query = "INSERT INTO SubDepartment VALUES(@DeptName,@DeptID)";
            con = new SqlConnection("Data Source=IT;Integrated Security=SSPI;Initial Catalog=AttendanceManagement");
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Parameters.AddWithValue("@DeptName", DeptName);
                    cmd.Parameters.AddWithValue("@DeptID", DeptID);

                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();

 

Posted 3 months ago

Hey akhter,

Please refer below sample.

HTML

<div>
    <asp:GridView runat="server" ID="gvSubDepartMent" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="SubDeptId" HeaderText="SubDeptId" />
            <asp:BoundField DataField="SubDeptName" HeaderText="SubDeptName" />
            <asp:BoundField DataField="DeptId" HeaderText="DeptId" />
        </Columns>
    </asp:GridView>
    <br />
    SubDepartName :<br />
    <asp:TextBox runat="server" ID="txtsubdept" />
    <asp:DropDownList runat="server" ID="ddlDepartMent">
    </asp:DropDownList>
    <br />
    <asp:Button Text="Insert" runat="server" OnClick="Insert" />
</div>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DeptId,DeptName FROM TestDepartment", con))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    using (DataTable dt = new DataTable())
                    {
                        da.Fill(dt);
                        ddlDepartMent.DataSource = dt;
                        ddlDepartMent.DataTextField = "DeptName";
                        ddlDepartMent.DataValueField = "DeptId";
                        ddlDepartMent.DataBind();
                        ddlDepartMent.Items.Insert(0, new ListItem("Select Department", "0"));
                    }
                }
            }
        }
    }
}

private void BindGrid()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM TestSubDepartMent", con))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    da.Fill(dt);
                    gvSubDepartMent.DataSource = dt;
                    gvSubDepartMent.DataBind();
                }
            }
        }
    }
}

protected void Insert(object sender, EventArgs e)
{
    string DeptName = txtsubdept.Text;
    string DeptID = ddlDepartMent.SelectedValue;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO TestSubDepartMent VALUES(@SubDeptName,@DeptId)", con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@SubDeptName", DeptName);
            cmd.Parameters.AddWithValue("@DeptId", DeptID);
            cmd.ExecuteNonQuery();
            con.Close();
            this.BindGrid();
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGrid()
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand("SELECT DeptId,DeptName FROM TestDepartment", con)
                Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable()
                        da.Fill(dt)
                        ddlDepartMent.DataSource = dt
                        ddlDepartMent.DataTextField = "DeptName"
                        ddlDepartMent.DataValueField = "DeptId"
                        ddlDepartMent.DataBind()
                        ddlDepartMent.Items.Insert(0, New ListItem("Select Department", "0"))
                    End Using
                End Using
            End Using
        End Using
    End If
End Sub

Private Sub BindGrid()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT * FROM TestSubDepartMent", con)
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    da.Fill(dt)
                    gvSubDepartMent.DataSource = dt
                    gvSubDepartMent.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub

Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
    Dim DeptName As String = txtsubdept.Text
    Dim DeptID As String = ddlDepartMent.SelectedValue
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("INSERT INTO TestSubDepartMent VALUES(@SubDeptName,@DeptId)", con)
            con.Open()
            cmd.Parameters.AddWithValue("@SubDeptName", DeptName)
            cmd.Parameters.AddWithValue("@DeptId", DeptID)
            cmd.ExecuteNonQuery()
            con.Close()
            Me.BindGrid()
        End Using
    End Using
End Sub

Screenshot