You must Sign In to post a response.
  • Category: ASP.NET

    Insert Statement with a foreign key.

    I have managed to link aspnetusers to my model cvdetails.

    I have referenced the "id" from aspnetusers as a foreign key in "cvdetails".

    When I run the following insert statement:
    **********************************************************************************
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data.SqlClient;
    using System.Configuration;

    namespace LJLCV.Account
    {
    public partial class MyDetails : System.Web.UI.Page
    {
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {

    SqlCommand cmd = new SqlCommand("INSERT INTO CVDetails (FirstName,LastName,Address,LandLine,Mobile,Email,PersonalStatement,Skills,InterestsnHobbies) VALUES (@FirstName,@LastName,@Address,@LandLine,@Mobile,@Email,@PersonalStatement,@Skills,@InterestsnHobbies)", con);
    con.Open();
    // "A" is your really value
    //cmd.Parameters.AddWithValue("@CVID", txtfname.Text);//
    cmd.Parameters.AddWithValue("@FirstName", txtfname.Text);
    cmd.Parameters.AddWithValue("@LastName", txtlname.Text);
    cmd.Parameters.AddWithValue("@Address", txtaddress.Text);
    cmd.Parameters.AddWithValue("@LandLine", txtlandl.Text);
    cmd.Parameters.AddWithValue("@Mobile", txtmob.Text);
    cmd.Parameters.AddWithValue("@Email", txtemail.Text);
    cmd.Parameters.AddWithValue("@PersonalStatement", txtps.Text);
    cmd.Parameters.AddWithValue("@Skills", txtskills.Text);
    cmd.Parameters.AddWithValue("@InterestsnHobbies", txtinterests.Text);
    cmd.ExecuteNonQuery();
    con.Close();
    Label1.Visible = false;
    txtfname.Text = "";
    txtlname.Text = "";
    txtaddress.Text = "";
    txtlandl.Text = "";
    txtmob.Text = "";
    txtemail.Text = "";
    txtps.Text = "";
    txtskills.Text = "";
    txtinterests.Text = "";

    Response.Redirect("~/Account/Education.aspx");
    }
    }
    }
    **********************************************************************************
    I get this error:

    Additional information: Cannot insert the value NULL into column 'Id', table 'aspnet-WebApplication1-20151126090738.dbo.cvdetails'; column does not allow nulls. INSERT fails.

    Surely if 'Id' is referenced as a foreign key from aspnetusers, it should automatically place the value from 'Id' in aspnetusers into the 'Id' of CVDetails.

    I don't see why there is a problem, because I've referenced it as a foreign key. Also before people suggest I do not want an auto-increment ID as the ID is already given in aspnetusers I just want it to be referenced in CVDetails.
  • #763970
    There is a id field in CVDetails, which must be auto incremented or you have to send parameterized value for id to insert. As you mark this field not null so no value for id not accepted and it gives you the error.
    If you are unable to do something properly for the first time, call it Version 1.0!

    Thanks & Regards,
    MONOJ BHUINA

  • #763978
    Event though you set "foreign key", It will populate automatically.

    You have to get the id from the "aspnetusers" and insert into the "CVDetails"

    What is Foreign Key:-
    1. Insert user "A" in aspnetusers table
    2. Insert data "CVDetails" ( by getting the Id of "A" from the table "aspnetusers"
    3. Now you try to detete the user "A" from aspnetusers. You will get exception, because you setup foreign key.
    4. Now you have to delete the data of user "A" from the CVDetails then you can delete user "A" from aspnetusers

    By Nathan
    Direction is important than speed

  • #764003
    Hai Luke,
    If a field is exists in other table as the primary key, you need to have the valid value for it to be inserted from the table where it is referenced.
    So you need to make sure that the value which you are providing for the foreign key is valid and then you can insert your record in to this table.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #764122
    Foreign key doesn't add your value. You have to put it accordingly. When using Foreign key it will prevent you from deleting primary data before deleting foreign data.
    If you are unable to do something properly for the first time, call it Version 1.0!

    Thanks & Regards,
    MONOJ BHUINA


Sign In to post your comments