We help IT Professionals succeed at work.

Generate Unique ID in VB.NET

1,892 Views
Last Modified: 2017-03-25
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
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
First of all: Can you give us some context? Cause non-atomic values indicates that there is information involved, which should be handled differently.

Caveat: You can only create 10000 unique numbers with that scheme..
D PatelD Patel, Software Engineer

Author

Commented:
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.
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.
D PatelD Patel, Software Engineer

Author

Commented:
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.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Is this a numeric field or a text field?
D PatelD Patel, Software Engineer

Author

Commented:
text field
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
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).
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Read my post again. It's not text. Store it as three separate INTEGER values.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
@ste5an
Dpatel just confirmed the data type of the field:
text field
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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).
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
@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).
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
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 ...
ste5anSenior Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
D PatelD Patel, Software Engineer

Author

Commented:
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?

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

Open in new window


Regards,
 D Patel
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
You didn't insert the new value anywhere?!
D PatelD Patel, Software Engineer

Author

Commented:
Yes,

I've written code for it on click event 'Add button'
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
Are you getting yymm0001 each time?
D PatelD Patel, Software Engineer

Author

Commented:
Yes, it is always yymm0001
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
D PatelD Patel, Software Engineer

Author

Commented:
Thanks for your reply.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.