sqlserver中的显式错误 [英] Explicit error in sqlserver

问题描述

HI
我有一个存储过程来从c＃插入数据。当我创建一个存储过程时它会抛出一个错误。错误如下：



只有在使用列列表且IDENTITY_INSERT为ON时，才能指定表'tbl_seat'中标识列的显式值。

HI I Have a stored procedure to insert datas from c#.When i create a stored Procedure it throw an error.The error is given below

An explicit value for the identity column in table 'tbl_seat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

create procedure sp_insert_seat
@A1 varchar(5),@A2 varchar(5),@A3 varchar(5),@A4 varchar(5),@A5 varchar(5),@A6 varchar(5),@A7 varchar(5),@A8 varchar(5),@A9 varchar(5),@A10 varchar(5),@A11 varchar(5),@A12 varchar(5),@A13 varchar(5),@A14 varchar(5),@A15 varchar(5),@A16 varchar(5),@A17 varchar(5),@A18 varchar(5),@A19 varchar(5),@A20 varchar(5),
@S1 varchar(5),@S2 varchar(5),@S3 varchar(5),@S4 varchar(5),@S5 varchar(5),@S6 varchar(5),@S7 varchar(5),@S8 varchar(5),@S9 varchar(5),@S10 varchar(5),@S11 varchar(5),@S12 varchar(5),@S13 varchar(5),@S14 varchar(5),@S15 varchar(5),@S16 varchar(5),@S17 varchar(5),@S18 varchar(5),@S19 varchar(5),@S20 varchar(5),
@id int
as
begin
insert into tbl_seat values(@id,@A1,@A2,@A3,@A4,@A5 ,@A6 ,@A7 ,@A8 ,@A9 ,@A10 ,@A11 ,@A12 ,@A13,@A14 ,@A15 ,@A16 ,@A17 ,@A18 ,@A19 ,@A20,
@S1,@S2,@S3 ,@S4 ,@S5 ,@S6 ,@S7 ,@S8 ,@S9 ,@S10 ,@S11 ,@S12 ,@S13 ,@S14 ,@S15 ,@S16 ,@S17 ,@S18 ,@S19 ,@S20)

end

我尝试了什么：



我尝试了Set Identiy_insert tbl_name ON



我尝试这种方法但它也会抛出错误。

What I have tried:

I tried the "Set Identiy_insert tbl_name ON"

I try this method but also it throws the error .

推荐答案

IDENTITY列由SQL Server控制，你通常不应该写它们 - 它违背了IDENTITY列的目的，即确保所有值都是唯一的。虽然你可以做一些偶然和特殊的任务（比如在数据库被长时间插入和删除后删除间隙），即使我不推荐它 - 它也应该被认为非常非常谨慎，因为IDENTITY列是通常用作行ID列，并通过其他表中的外键引用。只是更改IDENTITY列可以非常容易地将错误的信息与行相关联。



如果@ID是标识列的值，那么你从某个地方获取它 - 并且您可能通过获取最大值并添加一个来预分配该值。这很危险 - 因为很容易让两个不同的用户预分配相同的值，然后你的数据库完全无序。

不要这样做。使用IDENTITY列，永远不要写入它，并在使用 SCOPE_IDENTITY（）函数对行进行INSERTED后获取ID号。



给自己两个好处：

1）总是列出你要插入的列：

IDENTITY columns are controlled by SQL Server, and you should not normally write to them at all - it defeats the purpose of an IDENTITY column, which is to ensure that all values are unique. While you can do it for some occasional and exceptional tasks (such as removing gaps after a DB has been INSERTED and DELETED for a long time) even I wouldn't recommend it - it has be be considered very, very carefully as IDENTITY columns are often used as row ID columns and referenced via foreign keys from other tables. Just changing IDENTITY columns can very easily associate the wrong information with the row.

If @ID is a value for an identity column, then you are getting it from somewhere - and the chances are that you have "pre-allocated" the value by getting the max existing and adding one. That's dangerous - because it is far, far too easy to get two different users pre-allocate the same value and then your database goes completely out of order.
Don't do it. Use an IDENTITY column, never write to it, and fetch the id number back once you have INSERTED the row using the SCOPE_IDENTITY() function.

And do yourself two favours:
1) Always list the columns you are INSERTING into:

INSERT INTO MyTable (MyColumn1, MyColumn2) VALUES (@MyValueForColumn1, @MyValueForCoumn2)

如果你不这样做，那么你的添加或删除列时代码中断，或者更糟糕的是数据库在错误的列中获取信息。

2）使用合理的名称！ A1到A15，S1到S20没有描述他们在做什么，这使得以后的维护更加困难。

If you don't then either your code breaks when columns are added or removed, or worse your database gets info in the wrong columns.
2) Use sensible names! A1 to A15, S1 to S20 aren't descriptive of what they doing, and that makes maintenance a lot harder later.

我知道你已经解决了手头的问题，但是当时看着程序和表格定义，我不禁想知道标准化水平是否足够......



我的意思是，如果情况是你有20个座位可以保留或免费（或类似），你已经建模了表格，以便每个座位都是一个单独的列，然后我认为你可以从进一步规范化结构中受益。



一个例子是将每个预订存储在一个单独的行中，例如：

I know that you've already tackled the problem at hand but when looking at the procedure and the table definition, I can't help of wondering if the normalization level is sufficient or not...

What I mean is that if the situation is that you have 20 seats that can be reserved or free (or similar) and you have modeled the table so that each seat is a separate column then I think you would benefit from going a bit further in normalizing the structure.

An example would be to store each reservation in a separate row, like:

seat_reservation
- id (pointing to another, parent table)
- seatno (value between 1 and 20)
- reservation (the current parameter value if needed)

现在，如果保留座位1,4,5和17，则表格仅包含那些四个r行。



这种设计可以很容易地调查哪些座位是保留/免费的，更改座位数，实现预留逻辑而无需程序编程，以及等等，再次认为我正确解释了这种情况......

Now, if seats 1, 4, 5, and 17 are reserved the table would contain only those four rows.

This kind of design would make it easy to investigate which seats are reserved/free, make changes to seat count, implement reservation logic without procedural programming, and so on, again taken that I interpreted the situation correctly...

这篇关于sqlserver中的显式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！