System.Data.SqlClient.SqlException when I insert generates conflict with foreign key

Question

I have this table

When I try to insert a new Cargo appear an exception saying

INSERT statement conflicted with the FOREIGN KEY constraint "FK_Cargo_Rol". The conflict has appeared in the database "Banco" table "dbo.Rol", column 'idRol'. The statement has been terminated.

This is my code ASPX.CS

public partial class Public_Cargo_CrearCargo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
    if(!IsPostBack){
        string query1 = @"SELECT * FROM Formula";
        string query2 = @"SELECT * FROM Rol";

        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Banco"].ToString()))
        {
            SqlCommand comand = new SqlCommand(query1, con);
            SqlCommand comand2 = new SqlCommand(query2, con);

            DataSet ds = new DataSet();
            DataSet ds2 = new DataSet();

            con.Open();
            var adapter = new SqlDataAdapter(comand);
            var adapter2 = new SqlDataAdapter(comand2);

            adapter.Fill(ds, "Formula");
            adapter2.Fill(ds2, "Rol");

            Select1.DataSource = ds;
            Select1.DataTextField = "Formula";
            Select1.DataValueField = "idFormula";
            Select1.DataBind();

            Select2.DataSource = ds2;
            Select2.DataTextField = "Nombre_Rol";
            Select2.DataValueField = "idRol";
            Select2.DataBind();


        }
    }
}

protected void docreatecargo(object sender, EventArgs e)
{
    string query = @"INSERT INTO Cargo (Nombre, idFormula, idRol) VALUES (@nombre, @idFormula, @idRol)";

    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Banco"].ToString()))
    {
        SqlCommand comand = new SqlCommand(query, con);
        comand.Parameters.AddWithValue("@idFormula", Select1.SelectedIndex.ToString());
        comand.Parameters.AddWithValue("@idRol", Select2.SelectedIndex.ToString());
        comand.Parameters.AddWithValue("@nombre", cargo.Text);
        con.Open();
        Console.Write("idformula :" + Select1.SelectedIndex.ToString() + "," + "id Rol :" + Select2.SelectedIndex.ToString() + "," + "nombre :" + cargo.Text);
        comand.ExecuteScalar();

        Response.Redirect("~/Public/Cargo/Cargos.aspx");

    }
}

}

and this is my page ASP

<asp:Content runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
<div class="container">
    <div class="row">
        <div class="col-md-6">
            <div class="form-group">
                <label>Cargo</label>
                <asp:TextBox runat="server" ID="cargo" CssClass="form-control" required="true"></asp:TextBox>
            </div>
            <div class="form-group">
                <label>Formula asociada</label>
                 <select id="Select1" runat="server" name="D1"> </select>
            </div>
            <div class="form-group">
                <label>Rol asociada</label>
                 <select id="Select2" runat="server" name="D2"> </select>
            </div>
            <div class="form-group">
                <asp:Button ID="btn_login" OnClick="docreatecargo" CssClass="btn btn-primary btn-lg btn-block" Text="Guardar" runat="server"/>
            </div>

        </div>
    </div>
</div>

How can I fix it??

Answer 1

For the following lines of code:

comand.Parameters.AddWithValue("@idFormula", Select1.SelectedIndex.ToString());
comand.Parameters.AddWithValue("@idRol", Select2.SelectedIndex.ToString());

[controlId].SelectedIndex is returning the index of the selected item in the binding source and not the appropriate id from the database.

comand.Parameters.AddWithValue("@idFormula", Select1.Value.ToString());
comand.Parameters.AddWithValue("@idRol", Select2.Value.ToString());

Answer 2

As the error states, you cannot have values in FK table, if it does not exist in Primary Key Table.