D Patel
asked on
Generate Unique ID in VB.NET
Hi Experts,
I need to generate Unique ID in specific format (say 03170001; where, first two characters '03' represents month, next set of two character '17' represents the year and next four characters '0001' represents the number gets generated in incremental way), when I click on Add button.
Also need to track it with backend 'MYSQL' for last generated number.
Can anyone help me that how to program it using VB.NET?
Regards,
D Patel
I need to generate Unique ID in specific format (say 03170001; where, first two characters '03' represents month, next set of two character '17' represents the year and next four characters '0001' represents the number gets generated in incremental way), when I click on Add button.
Also need to track it with backend 'MYSQL' for last generated number.
Can anyone help me that how to program it using VB.NET?
Regards,
D Patel
ASKER
The requirement related to warehouse receipt numbers.
There are not more than 9999 receipts per month basis.
For each month and year combinations the number will get reset to 0001.
There are not more than 9999 receipts per month basis.
For each month and year combinations the number will get reset to 0001.
Ah, you should have written that in your OP.. makes a lot more sense now.
But you're aware that 03170001 will sort before 02200001?
Otherwise: Create a table in MySQL having three int columns (year, month, monthly_increment). Make this combination unique. Ad check constraints on month (1-12 only) and on monthly_increment (1-9999).
Now you can select the maximum monthly_increment, add one and write it back to your table.
But you're aware that 03170001 will sort before 02200001?
Otherwise: Create a table in MySQL having three int columns (year, month, monthly_increment). Make this combination unique. Ad check constraints on month (1-12 only) and on monthly_increment (1-9999).
Now you can select the maximum monthly_increment, add one and write it back to your table.
ASKER
yeah,
there is no issue in sorting. Because the sorting depends on different column. This is particular for tracking purpose.
As the new button get clicked the unique number will generated and on save it will updated to table.
Thanks for your prompt reply.
there is no issue in sorting. Because the sorting depends on different column. This is particular for tracking purpose.
As the new button get clicked the unique number will generated and on save it will updated to table.
Thanks for your prompt reply.
Is this a numeric field or a text field?
ASKER
text field
If you have an upper limit on the number of items per day, then you can use the right four characters to represent the day of the month and a sequence number, represented in hexadecimal "0000" through "FFFF". If you allow the entire four characters to be a sequence number, then you have a 65535 (decimal) limit on the value.
If you want to use the day and sequence number, you can use the first five bits of the first byte/character for the day (01 through 1F) and the rest of the bits for the sequence number. That will allow your daily sequence number to get to 7FF (2047).
If you want to use the day and sequence number, you can use the first five bits of the first byte/character for the day (01 through 1F) and the rest of the bits for the sequence number. That will allow your daily sequence number to get to 7FF (2047).
Read my post again. It's not text. Store it as three separate INTEGER values.
@ste5an
Dpatel just confirmed the data type of the field:
Dpatel just confirmed the data type of the field:
text field
He asked a green field question. He wants to store it in a database. A database has no fields... further more the "unique id" is a non-atomic compound. It is absolutely wrong to store it in a single column (query performance is poor, constraints are hard to implement).
@Dpatel
Is this your database or do you have the ability to change it?
If you change the database structure, how many programs will need to change as a result?
====================
If you are limited to 256 items per day, then you can add the day using the next two characters and then use the last two characters to contain the hexadecimal value of the sequence number (00 through FF).
Is this your database or do you have the ability to change it?
If you change the database structure, how many programs will need to change as a result?
====================
If you are limited to 256 items per day, then you can add the day using the next two characters and then use the last two characters to contain the hexadecimal value of the sequence number (00 through FF).
I agree performance will suffer from having to work with a single field, and also that if a single field is used, the format should be 'yymmiiii'.
Assuming concurrency is not an issue to consider, which is a false assumption almost all the time ;-), there are two ways to get the next number:
a) store last used yy, mm, iiii in another table, and increment there;
b) query the new ID field, and add one, similar to select ifnull(ID, 0)+1 from tbl where id like yymm+'%' (where yymm is the string var containing the obvious).
In each case the update (for a) or insert (for b) has to take place ASAP to keep away from duplicate IDs ...
Assuming concurrency is not an issue to consider, which is a false assumption almost all the time ;-), there are two ways to get the next number:
a) store last used yy, mm, iiii in another table, and increment there;
b) query the new ID field, and add one, similar to select ifnull(ID, 0)+1 from tbl where id like yymm+'%' (where yymm is the string var containing the obvious).
In each case the update (for a) or insert (for b) has to take place ASAP to keep away from duplicate IDs ...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear Experts,
I have developed the logic to autoincrement number. But it does not work as expected. Means that at first time it generates a number and store into the table. At the next time also its generating same number not a next number.
Can anyone point out the error?
Regards,
D Patel
I have developed the logic to autoincrement number. But it does not work as expected. Means that at first time it generates a number and store into the table. At the next time also its generating same number not a next number.
Can anyone point out the error?
Public Function generateNo() As String
Dim startDate As New DateTime(DateTime.Today.Year, DateTime.Today.Month, 1)
Dim endDate As DateTime = startDate.AddMonths(1).AddDays(-1)
Dim yrmn As String = startDate.ToString("MM") & startDate.ToString("yy")
Dim connE = New OdbcConnection("dsn=atn;")
connE.Open()
sql = "SELECT CONVERT(Max(right(treceipt.srecNo,4)), UNSIGNED INTEGER) as RecNo FROM treceipt, mcust" & _
" WHERE treceipt.nCustID = mcust.nCustID and dtPacking between '" & _ startDate & "' and '" & endDate & "'"
dbComm = New OdbcCommand(sql, connE)
Dim dr = dbComm.ExecuteReader()
If dr.HasRows Then
If IsNumeric(Double.Parse(dr(0).ToString.Trim())) Then
str = Double.Parse(dr("RecNo").ToString.Trim())
Dim str1 As String
str1 = Double.Parse(str.ToString.Trim()) + 1
While (Len(str1) < 4)
str1 = "0" & str1
End While
str = yrmn & str1
Else
str = yrmn & "0001"
End If
Else
str = yrmn & "0001"
End If
connE.Close()
dr.Close()
Return str
End Function
Regards,
D Patel
You didn't insert the new value anywhere?!
ASKER
Yes,
I've written code for it on click event 'Add button'
I've written code for it on click event 'Add button'
Are you getting yymm0001 each time?
ASKER
Yes, it is always yymm0001
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your reply.
Caveat: You can only create 10000 unique numbers with that scheme..