c# - 无法将 NULL 值插入自动递增主键列

Question

我在 C# 数据库中创建了表 Items，因为主键的属性是 AutoIncrement=True，AutoIncrementStep=1，Unique=True，AutoIncrementSeeds=0；

CREATE TABLE [dbo].[Items] 
(
    [item_id]       INT          NOT NULL,
    [item_name]     VARCHAR (50) NULL,
    [item_model]    VARCHAR (50) NULL,
    [item_price]    VARCHAR (50) NULL,
    [item_quantity] VARCHAR (50) NULL,
    [entry_date]    VARCHAR (50) NULL,
    [user_id]       VARCHAR (50) NULL,

    PRIMARY KEY CLUSTERED ([item_id] ASC)
);

我正在使用以下代码将数据插入到 Items 表中:

string query = @"Insert into Items(item_name, item_model, item_price, item_quantity, entry_date, user_id)" +
                "VALUES(@name, @model, @price, @quantity, @date, @user)";

using (SqlConnection c = new SqlConnection(Properties.Settings.Default.Database1ConnectionString))
{
    try
    {
        c.Open();

        if (c.State == ConnectionState.Open)
        {
            SqlCommand command1 = new SqlCommand(query, c);

            command1.Parameters.AddWithValue("@name", name.Text.ToString());
            command1.Parameters.AddWithValue("@model", model.Text.ToString());
            command1.Parameters.AddWithValue("@price", price.Text.ToString());
            command1.Parameters.AddWithValue("@quantity", quantity.Text.ToString());
            command1.Parameters.AddWithValue("@date", dateTimePicker1.Text.ToString());
            command1.Parameters.AddWithValue("@user", added_by.Text.ToString());

            int k = command1.ExecuteNonQuery();

            if (k > 0)
            {
                MessageBox.Show("Data Added Successfully");
                c.Close();
                return;
            }
            else 
            {
                MessageBox.Show("Data was not added successfully - try again later");
                c.Close();
                return;
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Failed To Open Connection :." + ex);
        return;
    }
}

当我运行代码并尝试插入数据时出现错误

Cannot Insert NULL value into column 'item_id' table

Answer 1

NOT NULL 表示您不能将 NULL 插入到您需要显式插入的列中。

设置 IDENTITY(1,1) 将自动插入 item_id 为 1 的种子。

像这样改变你的定义

CREATE TABLE [dbo].[Items] (
[item_id]       INT          NOT NULL IDENTITY(1, 1),
[item_name]     VARCHAR (50) NULL,
[item_model]    VARCHAR (50) NULL,
[item_price]    VARCHAR (50) NULL,
[item_quantity] VARCHAR (50) NULL,
[entry_date]    VARCHAR (50) NULL,
[user_id]       VARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([item_id] ASC)
);

或

显式插入数据库

string query = @"Insert into Items(item_id,item_name,item_model,item_price,item_quantity,entry_date,user_id)" +
                "VALUES(@item_id,@name, @model, @price, @quantity, @date,@user)";

停止使用 .AddWithValues Source