Error: Cannot insert the value NULL into column 'ID'

Question

I created a registration page and I have a table with a Primary key of ID,but I get this error: enter image description here

Cannot insert the value NULL into column 'Id', table 'D:\课程学习\动态网站设计\课程设计1\APP_DATA\REGISTRATION.MDF.dbo.Table'; column does not allow nulls. INSERT fails. The statement has been terminated.

I am just started to learn C# and really don't know how to fix this problem.

This is my code.

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;

public partial class Registration : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
            conn.Open();
            string chekuser = "select count(*) from [Table] where UserName='" + TextBoxUN.Text + "'";
            SqlCommand com = new SqlCommand(chekuser, conn);
            int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
            if (temp == 1)
            {
                Response.Write("The username is exsist");
            }

            conn.Close();

        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
            conn.Open();
            string insertQuery = "insert into [Table] (UserName,Email,Password) values (@Uname,@email,@password);";
            SqlCommand com = new SqlCommand(insertQuery, conn);
            com.Parameters.AddWithValue("@Uname",TextBoxUN.Text);
            com.Parameters.AddWithValue("@email", TextBoxEmail.Text);
            com.Parameters.AddWithValue("@password", TextBoxPass.Text);

            com.ExecuteNonQuery();
            Response.Redirect("Manager.aspx");
            Response.Write("Your registration is successful!");


            conn.Close();
        }
        catch (Exception ex)
        {
            Response.Write("Error:"+ex.ToString());
        }




    }
}

Hi Jing please do a search before asking questions, googling the error message came up with dozens of results - the duplicate was in the top 3 search results, see it's answer for the steps to solve your problem. Good luck! — Jeremy Thompson, Dec 27 '16 at 04:40

score 1 · Answer 1 · answered Dec 27 '16 at 04:33

Id property of your database table is not auto generated so its expecting a value, but you are not providing it during insertion. So either make your Id property identity auto generated or provide a value during insertion. For example giving Id 1

string insertQuery = "insert into [Table] (Id,UserName,Email,Password) values (@id,@Uname,@email,@password);";
SqlCommand com = new SqlCommand(insertQuery, conn);
com.Parameters.AddWithValue("@id",1);
com.Parameters.AddWithValue("@Uname",TextBoxUN.Text);
com.Parameters.AddWithValue("@email", TextBoxEmail.Text);
com.Parameters.AddWithValue("@password", TextBoxPass.Text);

score 1 · Answer 2 · answered Dec 27 '16 at 04:38

Error says it all,

you are trying to insert data into table and your table has a column as "id" which is a primary key. columns that are primary key can not hold null that is why you are getting this error

Insert unique id from your code or set auto generate for the column id, setting auto generate will auto insert the is for every new record and this will solve your issue.

sujith karivelil · Answer 3 · 2016-12-27T06:26:11.213

0

First of all I have to appreciate you for a well formatted first post, Then for fantastic use of parameterized queries.

When we look into the error, Which means there will be an [id] column in your [Table] which is set as not null but not auto increment. So you should provide a value for that colum in order to insert a row. you can solve this in two ways:

By changing that field as auto increment column; You can use alter command to modify the id field as autoIncrement by using the following query:
```
 ALTER TABLE [Table] MODIFY [ID] INT IDENTITY
```

Or By giving a value for that column in your insert query, in this case query will looks like this:

string insertQuery = "insert into [Table] (Id,UserName,Email,Password) values (id,@Uname,@email,@password);";

edited Dec 27 '16 at 06:26

answered Dec 27 '16 at 04:36

sujith karivelil

28,671
6
55
88

Hi ! I used the alter command,but there was something wrong like this:Column names in each table must be unique. Column name 'ID' in table 'Table' is specified more than once. – Jing Cheng Dec 27 '16 at 06:24
use `MODIFY` instead for `ADD` – sujith karivelil Dec 27 '16 at 06:26

Error: Cannot insert the value NULL into column 'ID'

3 Answers3