Cannot insert the value NULL into column 'ID', table

Question

I have a table with a primary and foreign key of ID, primary key id from LeaveCategory and foreign key = LeaveCategoryId. But I get this error:

Cannot insert the value NULL into column 'ID', table 'dbo.Action'; column does not allow nulls. INSERT fails

But I really don't know how to fix this problem.

This is my error:

click image

This is code:

public partial class AddLeaveType : System.Web.UI.Page
{
    int id = 0;

    SqlConnection conn = null;
    SqlCommand cmd = null;

    string connectionString = null;

    protected void Page_Load(object sender, EventArgs e)
    {
            if (!IsPostBack)
            {
                SqlDataReader dr = null;

                connectionString = ConfigurationManager.ConnectionStrings["LeaveManagementCS"].ConnectionString;

                conn = new SqlConnection(connectionString);

                string sql = "SELECT * FROM LeaveCategory";

                try
                {
                    cmd = new SqlCommand(sql, conn);

                    conn.Open();

                    dr = cmd.ExecuteReader();

                    while(dr.Read())
                    {
                        ListItem item = new ListItem(dr["Category"].ToString(), dr["Id"].ToString());
                        ddlCategory.Items.Add(item);
                    }

                    dr.Close();
                }
                catch (Exception ex)
                {
                    lblOutput.Text = "Error Message: " + ex.Message;
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
            }
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        connectionString = ConfigurationManager.ConnectionStrings["LeaveManagementCS"].ConnectionString;

        conn = new SqlConnection(connectionString);

        string sql = "INSERT INTO LeaveType (Type, Description, NumOfDays) ";
        sql += "VALUES (@type, @desc, @nod)";

        try
        {
                cmd = new SqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@type", tbType.Text);
                cmd.Parameters.AddWithValue("@desc", tbDesc.Text);
                cmd.Parameters.AddWithValue("@nod", tbNod.Text);

                conn.Open();

                int rows = cmd.ExecuteNonQuery();

                if (rows > 0)
                {
                    lblOutput.Text = "Added successfully";
                }
        }
        catch (Exception ex)
        {
            lblOutput.Text = "Error Message:" + ex.Message;
        }
        finally
        {
            if (conn != null)
                conn.Close();
        }
    }
}

Correct output:

click image

Answer 1

In Visual Studio click on view > server explorer > right click on the table > open Table definition > right click on the 'ID' and then properties > change the value of 'Identity specification' from False to True > done

Answer 2

A PRIMARY KEY cannot be null. It is the field that identifies your record uniquely. If you don't supply a value for the ID field then you clash with the underlying concept of a PRIMARY KEY.

If you don't know what value assign to the ID field then you could simply mark that column with the IDENTITY option and let Sql Server calculate the next value UNIQUE for you.

After that, if you want to know what value has been assigned to the ID column you could simple change your code to

protected void btnSubmit_Click(object sender, EventArgs e)
{
    connectionString = ConfigurationManager.ConnectionStrings["LeaveManagementCS"].ConnectionString;

    conn = new SqlConnection(connectionString);

    string sql = "INSERT INTO LeaveType (Type, Description, NumOfDays) ";
    sql += "VALUES (@type, @desc, @nod);"; // Note the semicolon in query here
    sql += "SELECT SCOPE_IDENTITY()";

    try
    {
        cmd = new SqlCommand(sql, conn);

        cmd.Parameters.AddWithValue("@type", tbType.Text);
        cmd.Parameters.AddWithValue("@desc", tbDesc.Text);
        cmd.Parameters.AddWithValue("@nod", tbNod.Text);

        conn.Open();

        int newID = Convert.ToInt32(cmd.ExecuteScalar());
        if (newID > 0)
        {
            lblOutput.Text = "Added successfully";
        }

    }
    catch (Exception ex)
    {
        lblOutput.Text = "Error Message:" + ex.Message;
    }
    finally
    {
        if (conn != null)
            conn.Close();
    }
}

By the way, if you have added a FOREIGN KEY to your table then you are required to give a value also to this field. It seems that the field LeaveCategoryId is a foreign key to the LeaveCategory table. If this is true then you should give a value to the field from some of your input data.

As a side note, it seems that you are using a global connection object. This is not recommended and you should create a local connection object, use it and then dispose as soon as possible. See Using Statement

Answer 3

Since your column 'ID' in table 'dbo.Action' is primary key(UNIQUE).It does not allow any NULL values.So while inserting into that table 'dbo.Action' use any sample integers or set IDENTITY for particular field(So that it will automatically get increments)