Auto generated ID from DB in ASP.NET

Question

I have a primary key for my table employee. I named it "IDS". The IDS data is like : K-00001, K-00002, etc. what I want to ask is, how to made this IDS auto generate in ASP.NET, not using trigger or function in SQL?

I have done my code like this:

            DBRabbit _db = new DBRabbit();
            Employee k = new Employee();
            var row = (from r in _db.Employees
                       orderby r.IDS descending
                       select r).FirstOrDefault();
            var n = row.IDS.PadLeft(5, '0');    
            IDSText.Text = n.ToString();

And I'm stuck while think how to adding the 'n' to + 1.. because when I write like this:

var n = row.IDS.PadLeft(5, '0')+1;

(last record is K-00010)
the result is K-000101 how to make it K-00011 without changing DB or using SQL server script/queries?

score 5 · Accepted Answer · answered May 09 '12 at 15:00

5

You better make your IDS column auto incremented as James said. Also if you need to have another column for K-00010, K-00011,... I would suggest you to use a computed column with the following formula

'K-' + cast( right([IDS]+(100000),(5)) as varchar(5))

EDIT: Formula corrected.

answered May 09 '12 at 15:00

Kaf

33,101
7
58
78

1

+1: Nice suggestion. What I particularly like is the ease of implementation. OP could implement this without screwing up the entire design. – James Johnson May 09 '12 at 15:07
Absolutely, this is effortless. – Kaf May 09 '12 at 15:14
I have change it to auto increment, changing it to int then how to use that "cast" ? It's used on asp or sql server? – Cross Vander May 10 '12 at 09:48
1

ye, it is an t-sql formula. Go to the design mode of your table and add a new column to it. Then in the `Column properties > Table designer > Computed column specifications > Formula. Past it there and save. You are done. – Kaf May 10 '12 at 09:52
an error is coming... it's said: error validating the formula for column IDS – Cross Vander May 10 '12 at 10:29
You are trying to add this formula to the IDS column. `You have to add a new column to the table and add above formula as I said before.` – Kaf May 10 '12 at 11:26
now it's no errors, but nothing happened Mr. Kaf. It still 1, 2, 3, not K-00001, K-00002.. did I wrong or why? – Cross Vander May 10 '12 at 16:09
1

Oh jeez, you must be doing it wrong. This is deadly simple if you read what I said very carefully and follow it. Again, Make your IDS column auto incremented column and forget about it. `Add another new column to your table` and add above formula to the new column as `Column properties > Table designer > Computed column specifications > Formula. Past it there and save`. if your new column name is myColumn then if you select myColumn in your query it will bring what you expect. ex, `SELECT IDS,myColumn FROM Employee`. – Kaf May 10 '12 at 16:21
1

I'm sorry.. My fault.. I do that at the same column.. I didn't notice about new column.. Sorry.. my bad.. But really thanks about this, now it works!! thank you very much Kaf.. and thank you to teach me patiently :D – Cross Vander May 10 '12 at 16:29

score 2 · Answer 2 · answered May 09 '12 at 14:53

2

Make the column auto-incrementing. As a rule of thumb, you should never increment primary keys manually -- that's just begging for issues. The right sequence of events would compromise that logic.

answered May 09 '12 at 14:53

James Johnson

45,496
8
73
110

2

"Issues" being a nice way of saying "horrible-awful-painful-badthings." +1 =) – Josh Darnell May 09 '12 at 14:57
1

Indeed lost count of the number of times I've seen this sort of thing done. It's just a screw up waiting to happen. – Tony Hopkinson May 09 '12 at 15:02

score 1 · Answer 3 · answered May 09 '12 at 15:00

Just have an auto inc integer somewhere (DB in preference) and then build your no doubt very meaningful representation with

String.Concat("K-",KeyValue.ToString().PadLeft(5,'0');

Your way is lot of messing about with an almost guaranteed chance for Murphy to play with your head. Just don't do what you are doing.

score 0 · Answer 4 · answered May 09 '12 at 14:53

0

Try:

var n = (Convert.ToInt32(row.IDS) + 1).ToString().PadLeft(5, '0');

answered May 09 '12 at 14:53

MikeTWebb

9,149
25
93
132

Have to strip the K- off the front as well, he's doing the soon to go wrong "intelligent" number trick. – Tony Hopkinson May 09 '12 at 15:04

score 0 · Answer 5 · answered May 09 '12 at 15:00

0

are you using javascript for the above? if so try using

var n = (number(row.ids.substring(2))+1).toString().PadLeft(5,'0')

the substirng function is to remove the "k-"

answered May 09 '12 at 15:00

Amogh Rai

159
1
7

Auto generated ID from DB in ASP.NET

5 Answers5