Click here to Skip to main content
12,998,892 members (31,269 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Hi. All

I need auto Increment in ID

Start ID : S100

Next time i need S101

Please tell me how i do for this
Posted 22-Mar-13 9:39am
Tadit Dash 22-Mar-13 15:42pm
What have you tried so far ?
Navas Khanj 22-Mar-13 15:45pm
hi..i want Auto INCREMENT ID Number

HEre i use Code

Dim query As String = "Select IsNULL(MAX(cast(SUBSTRING(F_ID,3,len(F_ID))+1,100 )) F_ID from Table2"
Dim dr As SqlClient.SqlDataReader
Dim cmd As New SqlCommand(query, con)
dr = cmd.ExecuteReader
'txt_FID.Text = dr(("F_ID").ToString)

Dim result = String.Format("R{0:D3}", 100)
txt_FID.Text = result
ThePhantomUpvoter 22-Mar-13 15:47pm
And so what exactly is the problem?
Dave Kreskowiak 22-Mar-13 16:38pm
Let the database do it and use a composite key. Two columns, the first being your text part and the second being an integer column that autoincrements.

With the code you've written, you can have two seperate users execute this at the same time and come up with the same ID number. That would be bad, wouldn't it??
Navas Khanj 22-Mar-13 15:49pm
once add ID next time not increment
ThePhantomUpvoter 22-Mar-13 15:57pm
Probably because you are not UPDATE'ing the value in the database.
Navas Khanj 22-Mar-13 15:59pm
For Example, "S100", everytime, when i click the command button, it will automatically + 1, S101
Member 10666857 26-Mar-14 7:58am
how can we increment it in c# language by using substring
my need is i have pqr-14001 and it become pqr-14002

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

hope help :
you can first get last value in the database then increase it and insert new record !
int Increment;// an int variable will specify the number.
// get last value and save it into Increment.
System.Text.StringBuilder sb = new System.Text.StringBuilder("s");// here u can attach ur custom character
Increment++;//increase variable
//insert new record with increased value
Dave Kreskowiak 22-Mar-13 16:37pm
This doesn't do anything but cause you problems in a production multi-user database application.

A better way to do this would be to let the database do the autoincrementing using a composite key.
source.compiler 22-Mar-13 16:42pm
you are right but the questin is a custom increment! so do you have any better way to generate custom increment?
i just gave a sugestion to give idea... for multiuser application can first get last ID value of the database and increase it then add new record into database ...
Dave Kreskowiak 22-Mar-13 17:24pm
for multiuser application can first get last ID value of the database and increase it then add new record into database

No, you can't. There are TWO queries going to the database to do that and they are NOT an atomic operation.

Say you have two clients, each doing the same operation. The first client calls the database to get the last used ID and it is returned dutifully, say as 145. The second client makes the same call, even a couple hundred milliseconds later, and gets, 145. Now they both increment the ID on the client-side and try to write a new record with the new "unique" ID. One works, the other fails.

Trying to write any client-side code that generates unique ID's for a table is huge problem, not trivial. ID's are best left up to the database to assign as it has atomic control over the assigning of record ID's.
source.compiler 22-Mar-13 17:32pm
but i suppose its not neithier of website or a network based application!
Dave Kreskowiak 22-Mar-13 18:08pm
Why else would you be generating unique ID's??
source.compiler 22-Mar-13 18:10pm
yea ... but its amateur way
Dave Kreskowiak 22-Mar-13 19:01pm
It doesn't mean you teach him the wrong way to do it.
source.compiler 22-Mar-13 19:26pm
its not wrong way... its simple way to prevent many complex sql queries
Dave Kreskowiak 22-Mar-13 23:05pm
It IS the wrong way as I've told you how your code fails miserably. It's written for applications that only ever have one user and that's just not a reasonable requirement in software.

Your motivation to "prevent many complex queries" doesn't make any sense because there are no queries when relying on the database to handle ID's. It's done automatically when you do an SQL INSERT into the table. There's nothing complex about that.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170622.1 | Last Updated 23 Mar 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100