SQL Server 2008中的“自动递增"字段
亲爱的所有人，
我的数据库中有一个名为tbl_ProductCatg的表(在SQl Server 2008中)
包含ID，Code，Description等字段.
id字段是自动递增的，我必须将此字段值插入Code字段.
也就是说，如果生成的ID为1，则应在代码"字段中插入值，例如0001(格式为长度为4)，如果id为77，则代码应为0077.
为此，我进行了如下查询:
插入tbl_ProductCatg(Code，Description)值(RIGHT(''000''+ ltrim(Str(SCOPE_IDENTITY()+ 1,4))，4)，''testing'')
此查询在sql服务器查询分析器中运行良好，但是如果我用C#编写此代码，则即使ID字段更新得很好，它也会在代码中插入Null.
寻找您的同类解决方案
在此先感谢
Supriya
Dear All,
A table is there named tbl_ProductCatg in my Database (in SQl Server 2008)
with fields Id,Code,Description and a few more.
Id field is autoincremented and I have to insert this field value in Code field.
i.e. if Id generated is 1 then in Code field the value should be inserted like 0001(formatted for having length of four),if id is 77 Code should be 0077.
For this,I made the query like:
insert into tbl_ProductCatg(Code,Description) values(RIGHT(''000''+ltrim(Str(SCOPE_IDENTITY()+1,4)),4),''testing'')
This query runs well in sql server query analyzer but if I write this in C# then it insets Null in Code even Id field is updated well.
Looking for kind solutions of Yours
Thanks in advance
Supriya
选中此
使用SQL Server自定义自动生成的序列 [
Check this
Custom Auto-Generated Sequences with SQL Server[^]
最可能的原因是新生成的自动增量值不在范围内.例如，您先执行insert语句，该语句首先在代码中(作为单独的语句)生成新的自动增量值，然后再执行上面的语句.
scope_identity调用必须与生成自动增量值的作用域相同.这意味着它在相同的存储过程，函数或批处理中.
但此外，我认为，您永远不要将自动增量值用作其他数据的一部分.自动增量仅用于创建唯一的，非描述性的值，因此它们不应具有任何其他含义.连接身份值，将其转换为字符串等不是使用身份值的正确方法.例如，您可以使用scope_identity在子表中添加外键列值，但在这种情况下，该值应保持不变.
The likeliest reason is that the newly generated autoincrement value is outside the scope. For example, you execute the insert statement that generates the new autoincrement value in your code first (as a separate statement), after that you execute the statement above.
The scope_identity call must be in the same scope where the autoincremented value was generated. This means that it''s in the same stored procedure, function or batch.
But furthermore, in my opinion, you should never use the autoincrement value as part of some other data. Autoincrements are used only for creating unique, nondescriptive values so they should never have any other meaning. Concatenating identity value, converting it to string etc. isn''t the correct way to use an identity value. You can use scope_identity for example to add a foreign key column value in a child table but also in that case the value should be unmodified.
您无法在C#中执行任何操作并让计算字段为您完成工作，
您可以将代码定义为计算字段，其公式如下所示:(substring(CONVERT([varchar](max),(10000)+[id],0),(2),(4)))
然后您可以随时使用它.您也可以在插入过程中忽略它.
请记住，如果您预测ID小于10000，则此公式适用于ID小于10000的情况，请将其更改为正确的公式，例如对于1000000，我们有一个公式:(substring(CONVERT([varchar](max),(1000000)+[id],0),(2),(6)))
希望对您有所帮助.
You can do nothing in C# and let a computed field do the job for you ,
you can define code filed as a computed field that its formula is like this :(substring(CONVERT([varchar](max),(10000)+[id],0),(2),(4)))
and then you can use it whenever you want. also you can ignore it during the insertions.
remember that this formula is for IDs less than 10000 if you predict it may go further than 10000 please change it to a proper one for example for 1000000 we have this one :(substring(CONVERT([varchar](max),(1000000)+[id],0),(2),(6)))
Hope it helps.