How to auto increment a value

Question

0.00/5 (No votes)

See more:

Hi,

I have a requirement where if a user creates a new form, the ID value should increment autoamtically. I used SQL to do it. Below is my code:

string strcomm = string.format(@"IF EXISTS(select * from [DATABASE].[TABLE] where ID is NULL and [ArtifactID] = @ArtifactID)
Begin
update [DATABASE].[TABLE] SET [ID]= (Select CAST(MAX(ISNULL(ID,0)) as int)+1 from [DATABASE].[TABLE]) where [ArtifactID] = @ArtifactID
END");

The problem is, whenever user creates a task, it is incrementing the value but, it is incrementing only till 10. Whatever the task created after 10, it is taking the value as 10 only. Can anyone help me over this?

What I have tried:

I am new to SQL and tried changing but didn't get. Any help would be appreciated.

Posted 7-Jul-16 19:10pm

Member 8010354

Updated 8-Jul-16 18:09pm

Add a Solution

Comments

Member 8010354 8-Jul-16 4:49am

Can anyone help me on this how to resolve?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 11921311 · Answer 1 · 2016-07-08T01:37:00

Solution 3

This solution may be help you

At the time of create table

create table tablename
(
id int not null identity(1,1) primary key
)

Posted 8-Jul-16 1:37am

Member 11921311

Daniel Jones · Answer 2 · 2016-07-08T18:09:00

Solution 4

SQL

CREATE TABLE (
  ID_column INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  ...
);

IDENTITY property will auto-increment the column up from number 1.

Posted 8-Jul-16 18:09pm

Daniel Jones

deepankarbhatnagar · Answer 3 · 2016-07-08T00:55:00

Solution 2

check this:

SQL AUTO INCREMENT a Field[^]

Posted 8-Jul-16 0:55am

deepankarbhatnagar