Link to home
Start Free TrialLog in
Avatar of D Patel
D PatelFlag for India

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
Avatar of ste5an
ste5an
Flag of Germany image

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..
Avatar of 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.
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.
Avatar of D Patel

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.
Is this a numeric field or a text field?
Avatar of D Patel

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).
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:
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).
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 ...
SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D Patel

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?

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
You didn't insert the new value anywhere?!
Avatar of D Patel

ASKER

Yes,

I've written code for it on click event 'Add button'
Are you getting yymm0001 each time?
Avatar of D Patel

ASKER

Yes, it is always yymm0001
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D Patel

ASKER

Thanks for your reply.