当主键列已设置默认标识(1,1)时如何将数据插入sql server数据库

How to insert data into sql server database when the primary key column already set default identity(1, 1)
2020-10-17
  •  译文(汉语)
  •  原文(英语)

我试图在我的SQL Server数据库中插入两个数据列,但是在代码行出现错误-> cmd.ExecuteNonQuery();

无法将NULL值插入列OrderID,表RestaurantApp.dbo.Junc_Order; 列不允许为空.插入失败.

OrderID列实际上是我的数据表中的主键.我将其设置为identity(1,1),并希望插入其他数据,同时它可以自动插入1、2、3、4....

这是我的代码的一部分:

string insertString = "INSERT INTO Junc_Order(ID, Quantity)values (@ID, @Quantity)";
SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.Parameters.AddWithValue("@ID", r_ID);
cmd.Parameters.AddWithValue("@Quantity", r_Quantity);
cmd.ExecuteNonQuery();

在这些代码之前,我已经与数据库建立了连接,所以问题不应该是那个.

更新了Junc_Order表设计:

OrderID (PK,FK,int,not null)

ID(FK,int,not null)

Quantity(int,not null)
速聊1:
如果您还可以显示表设计代码,那就太好了.
速聊2:
真的确定您OrderID的身分专栏吗?您是否检查了此列的属性?
速聊3:
如果要自动插入身份,则不要插入.允许SQL Server分配它.只需使用INSERT INTO Junc_Order(Quantity)values (@Quantity)";
速聊4:
好吧,ID和OrderID是不同的列.这实际上是一个联结表.
速聊5:
我刚刚更新了问题,并添加了表格设计.
解决过程1

通过查看您的问题,您的插入查询似乎不正确:

  1. 首先,您不需要插入"OrderID",因为它是主键标识,因此sql server会自动将其插入.
  2. 其次,在某个地方您将"r_ID"设置为null,这就是您遇到错误的原因.请使用以下命令验证并修改代码:

    字符串insertString ="INSERT INTO Junc_Order(Quantity)values(@Quantity)"; SqlCommand cmd =新的SqlCommand(insertString,conn); cmd.Parameters.AddWithValue("@ Quantity",r_Quantity); cmd.ExecuteNonQuery();

速聊1:
再次抱歉造成误解.这是一个联结表,OrderID是主键,ID是另一个,这是外键.r_id实际上是我命名的输入数据变量.
速聊2:
看到上一次编辑后,您似乎没有创建"OrderID"作为身份.请验证并从下面的链接获取帮助,以向现有列添加身份: stackoverflow.com/questions/1049210/…

I'm trying to insert two data columns into my SQL Server database but I get an error at the code line -> cmd.ExecuteNonQuery();

Cannot insert the value NULL into column OrderID, table RestaurantApp.dbo.Junc_Order; column does not allow nulls. INSERT fails.

The OrderID column is actually the primary key in my data table. I set it identity(1, 1) and want to insert other data and meanwhile it can insert 1, 2, 3, 4....automatically.

Here is the part of my code:

string insertString = "INSERT INTO Junc_Order(ID, Quantity)values (@ID, @Quantity)";
SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.Parameters.AddWithValue("@ID", r_ID);
cmd.Parameters.AddWithValue("@Quantity", r_Quantity);
cmd.ExecuteNonQuery();

I already get connection with database ahead of these codes, so the problem should not be that.

Updated Junc_Order table design:

OrderID (PK,FK,int,not null)

ID(FK,int,not null)

Quantity(int,not null)
Talk1:
Would be good if you can show the table design code as well.
Talk2:
Are you really sure your OrderID is idendity column? Did you check this column properties?
Talk3:
If you want identity to be automatically insertted then just dont insert it. Allow SQL Server to allot it. Just use INSERT INTO Junc_Order(Quantity)values (@Quantity)";
Talk4:
well, ID and OrderID are different column. This is actually a junction table.
Talk5:
I just updated my question and added table design.
Solutions1

By viewing your question, it seems that your insert query is not correct:

  1. First of all, you don't need to insert "OrderID" as it is primary key identity so sql server automatically insert it.
  2. second, somewhere you are getting "r_ID" as null that's why you are facing error.Verify it and modify your code with the following:

    string insertString = "INSERT INTO Junc_Order(Quantity) values(@Quantity)"; SqlCommand cmd = new SqlCommand(insertString, conn); cmd.Parameters.AddWithValue("@Quantity", r_Quantity); cmd.ExecuteNonQuery();

Talk1:
Sorry again for the misunderstanding. This is a junction table, OrderID is the primary key and ID is another, which is a foreign key. r_id is actually the input data variable I name.
Talk2:
after seeing your last edit, it seems you did not created your "OrderID" as Identity. Please verify and get help from the below link to add an identity to existing column : stackoverflow.com/questions/1049210/…
转载于:https://stackoverflow.com/questions/30838922/how-to-insert-data-into-sql-server-database-when-the-primary-key-column-already

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入