How do I auto increment the ID value

Question

0.00/5 (No votes)

See more:

Hi,

I have this question but my problem is still exists.

C#

  public void AutoIncrementTaskID()
        {
            DatabaseConnection conn = this._dbcEH;
            string strComm =
                    string.Format(@"IF EXISTS(Select * from [DB].[Table] where ID Is NULL and [TempID] = @TempID)    
Begin
 UPDATE [DB].[Table] SET [ID]= (Select MAX(CAST(ISNULL(ID,0)as int))+1 from [DATABASENAME].[TABLE])  WHERE [TempID] =@TempID
End
");

I understood that the error is coming near (Select MAX(CAST(ISNULL(ID,0)as int))+1. Can anyone try to resolve this and let me know? thanks for you help in advance.

What I have tried:

I tried tweaking the code by changing cast, max and all bt it didn't worked.

Posted 10-Jul-16 6:07am

Member 8010354

Updated 10-Jul-16 6:29am

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 10-Jul-16 12:32pm

You should never update an ID field, ever.
What are you _actually_ trying to accomplish? Not "how"; "what"?

Member 8010354 11-Jul-16 1:15am

Okay, actually i am creating a presaveevent handler using APIs in web application (KCURA-Relativity). So i have created the customised application in that and whenever user creates a new task (EX: Like a student registration form), it hsould genarate the unique value. In that point i took SQL into the picture.

PIEBALDconsult 11-Jul-16 1:22am

I prefer GUIDs as IDs, but if you want an integer use a Sequence.

Member 8010354 11-Jul-16 1:56am

Can you elaborate please. I didn't undrestand.

1 solution

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)

OriginalGriff · Accepted Answer · 2016-07-10T06:20:00

Solution 1

Don't do that.
Instead make your ID field an IDENTITY field and SQL will handle it for you.
The problem with your method is that it will fail in production: as soon as two users try to insert new rows at the same time, they will fail because your TempID can't be duplicated! Or if it can, that makes it even worse because all the rows with that value will be changed...

SQL AUTO INCREMENT a Field[^]

Posted 10-Jul-16 6:20am

OriginalGriff

Comments

Member 8010354 11-Jul-16 1:15am

Okay, actually i am creating a presaveevent handler using APIs in web application (KCURA-Relativity). So i have created the customised application in that and whenever user creates a new task (EX: Like a student registration form), it hsould genarate the unique value. In that point i took SQL into the picture. In the link you refered, it is like while creating a table we are setting it as auto increment value. how to use this?

OriginalGriff 11-Jul-16 11:14am

That's right - you set it as an IDENTITY field when you create the DB and then when you do an INSERT operation, SQL will create a unique value for that field for you.
You can combine the INSERT with a SELECT @@IDENTITY or SELECT IDENTITY_SCOPE() to return the created value to your app if you need it.