How do you make the id + 1 ?

Question

Hi expert I would like to know how do we go about generating a formNo such that when the next user submit a form it will be 2 instead of 1 ? Basically this formNo format requirement is like this : ABC + yymm + - + id which will be 2 when the user submit form the 2nd time. Tks.

Nilesh Shah · Answer

You can store the Id in a table get the last Id and increase it by one update back after successful operation

Tangara G · Answer

Sorry Nitin I do not intend to create the ABC column and the YYMM column. I just want get the output of the ID column which will be incremental and put it inside the getRefNo() method. Do I create a store procedure and specify the output and then how to use this output value and concatenate with the rest of the formNo - ABC ....

Nitin Sontakke · Answer

It seems you are getting my question...anyway... select 'ABC' + mmyy + replicate('0' 4 - len(ltrim(str(id)))) + ltrim(str(id)) from... something like this...put whatever logic you want at mmyy. This is how you format it at server side itself.

Tangara G · Answer

Hi Nitin for the month and year I can do it programmatically right ? Could you help me with the last question ? Cos I really don't know the stored procedure way. And now I have problem generating the output and then I can make use of this output and put it as a code instead.

Nitin Sontakke · Answer

You didn't answer the most important question. Do you want to reset this id to 1 when month and year changes? If answer is 'Yes' that changes the whole game here. If you the answer is 'No' I recommend that you do all the formatting server side only.

Tangara G · Answer

Hi Nitin Is it possible to do it like this ? I just want the last 4 digit which is based on the Identity Column ID to be added to ABC + yymm + - + this part which make use of the Identity Column ID and then I just format it to 0001. How to do it like that ? I mean how do I call out this id from the database before I format it and then concatenate it to the rest ?

Nitin Sontakke · Answer

Yes it is identity column. I may not be able to give entire solution just the hints. Any questions on the way please ask. column definition: [FormId] [Integer] not null identity(1 1) When you insert the record server will put the value. You don't need to read it write away. Another column [FormFormat] [varchar](10) not null In this insert abc + substring(ltrim(str(year(getdate())) 2 2) + ltrim(str(month(getdate()) remember to pad '0' to month if value less than 10. Then in select: select [FormFormat] + ltrim(str([FormId])) other columns from table name Also pad 0s to formId to look it good. Do you want to reset form id to 0 after month change? If yes then use sequence.

Tangara G · Answer

Hi Nitin When you mentioned identity column do you mean the column is made auto-incremental ? Cos I have read somewhere something like identity(11) but I do not know how to script the id column to something like this. Can advise? Also after I perform the above step how do I read the auto-incremental column and add that to the yy+mm ? Hope to hear from you soon. Tks.

Bryian Tan · Answer

You need to cast the id to string. id.ToString() before concatenate it with another string

Rafnas T P · Answer

try like this id=id+1; ABC + yymm + - + id;

Ben Chris · Answer

you need to update your form id back from server side instead of updating from client side. You can use any proposed mechnism here.

Rafnas T P · Answer

string dd = ABC1701-1; //get formNo field from table id = Convert.ToInt32(dd.Remove(0 8)); id = id + 1; if (dd != string.Empty) { dd = ABC + DateTime.Now.ToString(yyMM) + - + id; } else { dd = ABC + DateTime.Now.ToString(yyMM) + - + 1; } //save dd to formNo field in table cloumn

Nitin Sontakke · Answer

Where are you storing it? If you are storing it in database use either identity column or use sequence. Please DO NOT think of putting all combined values in single column. Put all things separate columns and combine them ONLY for display purpose may be in a view or in a stored procedure SELECT statement. Or you can have a calculated column. DO NOT enforce your business requirements on the code you write.

Bikesh Srivastava · Answer

use for loop and increement accordingly