Generate Reference Number

[ooiooipig]

Hi everyone!! Can anybody advise me on how to generate the reference number?? The sequence of the reference number is as follow: YearMonthId eg. 2003030001, 2003030002, ...

At the end of each month, the month will go on to the next one.
eg. 2003030001 --> 2003040001

The same goes for the year.
eg. 2003120001 --> 2004010001

I'm having a hard time thinking of how to get this done. Can anyone help me??
Thanks!! :rolleyes:

[allida77]

strRefNumb=Year(Now()) & Month(Now()) & idNumb

Check out: http://www.devguru.com/Technologies/vbscript/quickref/functions.html

[ooiooipig]

Hi Allida77, thanks for your reply... how should i go about referring to the site?? I want to generate a reference number with the date n year...

[allida77]

this gives you month and year:

Response.Write(Year(Now()) & Right("00" & Month(Now()),2) & idNumb)

200303....

the idNumb is your 0001 portion. Are you generating this part?

[ooiooipig]

yah I suppose... it is only the last 4 digits that i'm generating as the front digits are the dates and year. How can i go about generating the last 4 digits and then store them in the database??

[allida77]

Well I can think of two ways one is returning the last row in the table and stripping the last four number then increasing by one then appending to the year month for your new id
OR
Response.Write(Year(Now()) & Right("00" & Month(Now()),2) & Right("00" & Day(Now()),2) & Hour(Time()) & Minute(Time()) & Second(Time()) )

This is always unique unless someone updates to your db at the same second at the same minute of the same hour of the same day ect. ect.

Does this field have to be a 10 length?

[ooiooipig]

The reference number that I need to create is the first method as stated above... is there any example u can show me as to how to do?? The number must have only 10 digits.

The first 6 digits (year and month) will stay the same, only the last 4 that is changing throughout the whole month. Then after 1 month, the month will increase by one, after 1 year, the year will increase by 1. The last 4 digits must be reset after every month.

I really appreciate if you can help me in this.

[ooiooipig]

anyone?? please help me... i'm stucked here!!

[Roy Sinclair]

Store the two parts of your number as separate fields in your database and then combine them as a single field in a view or in your select statement. Then you also need to use a stored procedure (or whatever the equivalent is for the database you use) to insert new records. That stored procedure can perform a select max(serial) from yourtable then select max(datefield) from yourtable.

With the maximum values from each of those separate fields you can then increment the max serial number by one. Next check the current month and year against the datefield value and if they aren't equal replace the datefield with the new one and reset the serial number. Then go ahead and insert the record into the database with the newly generated numbers.

[raf]

Yeah! That sounds quite straightforward.
But you'll also need to lock the table while this whole procedure is running, so you'll need a pretty fast db...

If you'd like to try on something more exotic (and if your db support's it) you can have "pre-allocated" values inside a db-cache so that new record immedeately can get there value for that field (since it's wayting in the cache). Only problem: if your server goes down, the cache is flushed and you'll have a break in your numbers.


Actually, if you'd ask me (DON'T!! Never did anyone any good :) ), drop the whole thing. Just save the date and have an autonumber field to generate the numbers. I'm sure you'll be able to do whatever you do with that field and it will be so much easier and flexible.

[whammy]

I'm not sure what the problem is, after reading the above posts...

MyDate = Year(Now()) & Right("00" & Month(Now()),2) & Right("00" & Day(Now()),2)

...is almost identical to what allida77 posted, and I use it all the time for a datestamp (but NOT in anything I would send to a user!).

If you want to add something unique to it, just add the primary key in your database, maybe?

My primary concern is that this kind of scheme could be hacked easily if someone looked at the reference number (and someone could perhaps create a script to get everyone in your database's email address, CC number, or whatnot!), so it might not be a good idea... I would think you'd need to include some other user entered, or perhaps random data in this "key".

What exactly are you trying to do? Explaining your ulterior motives will help us a great deal in solving your problem - that is if there are any security concerns involved. You wouldn't believe the number of sites on the 'net that basically allow anyone with a brain to read almost anything from a database - and not even hackers, just people who have common sense!

I would personally never do ANYTHING with the data I've seen regarding exploiting these methods (although I have seen plenty of opportunies!), or try to clue in a script kiddie on ways to do this stuff. But security is a VERY important thing when you are dealing with clients that depend upon you. ;)

Maybe Roy understands better than I what you're trying to do - his post seems like it. :D

[ooiooipig]

Hi guys, thanks for your replies!!

however, my codin gat this point of time is something like this:

<html>
<body>
<%
sql1 = "select ReqMonth from ReqNoRef"
set rs=MSCS.execute(sql1)

if ReqMonth <> (Month(Now)) then
LastReqNo=0000
end if

sql2 = "select LastReqNo from ReqNoRef"
set rs=MSCS.execute(sql2)

if LastReqNo = LastNo then
LastNo = LastNo + 1
end if

sql3="INSERT INTO ReqNoRef (ReqMonth, LastReqNo) values Month(Now), LastNo)"
set rs=MSCS.execute(sql3)

rs.close
set rs=nothing

ReqNo = Right(CStr(Year(Now)) & CStr(Month(Now)& CStr(LastReqNo)), 11)

'Dim MyMth
'MyMth = Month(Now)

'Dim MyYear
'MyYear = Year(Now)

'ReqNo = Right(CStr(Year(Now)) & CStr(Month(Now)& CStr(LastReqNo)), 11)

''ReqNo = Right(CStr(Year(Now)) & CStr(Month(Now)& CStr(Int(Rnd * 1000))), 11)

sql="INSERT INTO Requests (AppnId,DesiredDt,reqTitle,funcReq,FreqVol,Reqtr,ReqtrEmail,ContactNo,Dept,RC,Priority,ApprovOfficer ,ApprovEmail,Savings,Revenue,Benefit,Tester,TesterContact)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("vAppnId") & "',"
sql=sql & "'" & Request.Form("vDesiredDt") & "',"
sql=sql & "'" & Request.Form("vreqTitle") & "',"
sql=sql & "'" & Request.Form("vfuncReq") & "',"
sql=sql & "'" & Request.Form("vFreqVol") & "',"
sql=sql & "'" & Request.Form("vReqtr") & "',"
sql=sql & "'" & Request.Form("vReqtrEmail") & "',"
sql=sql & "'" & Request.Form("vContactNo") & "',"
sql=sql & "'" & Request.Form("vDept") & "',"
sql=sql & "'" & Request.Form("vRC") & "',"
sql=sql & "'" & Request.Form("vPriority") & "',"
sql=sql & "'" & Request.Form("vApprovOfficer") & "',"
sql=sql & "'" & Request.Form("vApprovEmail") & "',"
sql=sql & "'" & Request.Form("vSavings") & "',"
sql=sql & "'" & Request.Form("vRevenue") & "',"
sql=sql & "'" & Request.Form("vBenefit") & "',"
sql=sql & "'" & Request.Form("vTester") & "',"
sql=sql & "'" & Request.Form("vTesterContact") & "')"

'Response.Write(sql)
'Response.End

MSCS.execute(sql)
%>

redirecturl = "change_request.asp"
Response.Redirect redirecturl

</body>
</html>

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------

actually, I just need to create a reference number with the year in front, followed by the month and the unique number at the back. It goes something like this "yyyymmuuuu". I've a database to store the month in which the user submit their request and the last reference number created. I don't know if I have done it correctly above. Can anyone please advise me and help me to correct the codes above if there's any error??

[whammy]

You say "Can anyone please advise me and help me to correct the codes above if there's any error??"

That's not really what we're here for... it's up to YOU to test your own code. I'm not really sure what the problem is that you're having, from your (non)explanation.

This can't be too hard:

format: yyyymmuuuu

• Ok, you know what yyyy is. (2003)

• You know what mm is. (03, very soon to be 04)

So what is uuuu? And how are you making it unique within the month itself, since you aren't using days, minutes, hours etc.?

IMHO you should use Right("0000" & primarykeyvalue,4) or something like that (unless you have more than 10,000 people sign up every month).

If you're using a database, that's really the only way to guarantee a unique value...

[ooiooipig]

ok sorry!! thanks it's done!! :) so this case is closed. please advice me in the other posts thanks!!