Click here to Skip to main content
14,971,661 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted

   
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.
   
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.
   
http://howtoideas.net/how-to-create-auto-increment-column-in-a-sql-server-table[^]


or

SQL
create trigger update_increment for update as
if not update(incrementID)
  UPDATE tb_users SET incrementID = incrementID + 1
    from inserted WHERE tb_users.id = inserted.id
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900