Click here to Skip to main content
15,948,339 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm creating a table with two columns that I want to auto-increment. One column is a primary key, so I'm using the IDENTITY keyword on it. The other column will be used to track the user-defined "sort order" of items in the table. Any time the user moves an item, its "sort order" will swap values with that of another element. However, when an item is inserted into the table, the inserted item should always be auto-assigned a sort-order value higher than any other value in the table. Here's a simplified version of the table creation script:

This is my create table script,

C#
CREATE TABLE [dbo].[tbl_category] 
  ( 
     [id]           [INT] IDENTITY(1, 1) NOT NULL, 
     [category]     [VARCHAR](50) NULL, 
     [last_updated] [DATETIME] NULL, 
     [priority]     [NVARCHAR](max) NOT NULL 
  ) 
ON [PRIMARY] 


Here, i have also mention my stored procedure script which i have done,
here you can see my last field priority, I want auto increment priority field,
above you can see my create table script in this i have already use identity keyword with id,
so, how can i increment with priority field ?

C#
 INSERT INTO dbo .tbl_categorynmpics 
            (category, 
             last_updated, 
             priority) 
VALUES      ( @category, 
              @imageurl, 
              Getdate(), 
              @priority ) 




Thanks,
Posted

1.Use the "sequences" if you're using SQL Server 2012.

2.Use GUID's and NEWID() function.

3.Create the trigger for that table to increment the values.


Thanks
 
Share this answer
 
Hai
You cant make auto increment coloum without primary key,in database design time,but in coding u can do.
first make it id as integer,read id coloumn if dont have value assign 1,i have value then increment with 1,these thing u do when insert value.

And above ur code in insert query u use 3 fields and try to add 4 vlaues
SQL
INSERT INTO dbo .tbl_categorynmpics 
            (category, 
             last_updated, 
             priority) 
VALUES      ( @category, 
              @imageurl, 
              Getdate(), 
              @priority ) 


where the coloumn for imageurl ?

And u ask like this ? if u insert value it automatic increment value,if u insert and delete or remove value always it appear next increment value even thoung last value is not availabe in table,so remove auto increment and try to give id via codingd

Best
Regards
 
Share this answer
 
Comments
Pratham4950 5-Dec-13 4:35am    
@Aravindba Yes, for imageurl it's my mistake. i had forget for delete that from values.

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