How do you make the id + 1 ?

Tangara G

9y1.2k.NET

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.

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Asma Khalid9y ago
you need to update your form id back from server side instead of updating from client side. You can use any proposed mechnism here.
Nilesh Shah9y ago
You can store the Id in a table, get the last Id and increase it by one, update back after successful operation
Tangara G9y ago
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 Sontakke9y ago
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 G9y ago
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 Sontakke9y ago
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 G9y ago
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 ?
Rafnas T P9y ago
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 Sontakke9y ago
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 G9y ago
Hi Nitin,

When you mentioned identity column, do you mean the column is made auto-incremental ?
Cos I have read somewhere something like identity(1,1) 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.
Nitin Sontakke9y ago
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.
Bryian Tan9y ago
You need to cast the id to string. id.ToString() before concatenate it with another string
Bikesh Srivastava9y ago
use for loop and increement accordingly
Rafnas T P9y ago
try like this

id=id+1;
ABC + yymm + "-" + id;