Click here to Skip to main content
16,021,172 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
To generate an autonumber to textBox in vb.net with dateformat like 20-08-30-001

but this is not working i made one public sub to select Max number from dATABASE AND ONE dunction with this code i dont know how to do it now

What I have tried:

VB
Public Sub CalculateNewRecordNumber(ByVal record As String)
    Dim data As String() = record.Split("-"c)
    Dim currentDate As DateTime = DateTime.Today
    Dim recordYear As Integer = Integer.Parse(data(0))
    recordYear = Integer.Parse("20" & recordYear)
    Dim recordMonth As Integer = Integer.Parse(data(1))

    If currentDate.Year = recordYear Then

        If currentDate.Month = recordMonth Then
            Dim number As Integer = Integer.Parse(data(2))
            number += 1
            record = recordYear & recordMonth & "-"
            Dim _recNumberOnly As String = number.ToString()

            For i As Integer = 0 To 4 - 1

                If _recNumberOnly.Length = 4 Then
                    Exit For
                Else
                    _recNumberOnly = "0" & _recNumberOnly
                End If
            Next

            record += _recNumberOnly
        Else
            recordMonth += 1
            record = recordYear & recordMonth & "-0001"
        End If
    Else
        recordYear += 1
        record = recordYear & "08-0001"
    End If


End Sub
Posted
Updated 31-Aug-20 2:32am
v2
Comments
Maciej Los 31-Aug-20 4:55am    
What database provider?
Member 14649324 31-Aug-20 7:06am    
Sql database

First of all, i agree with OriginalGriff. Using vb.net code to generate autoincrement string value based on date is bad idea, especially in multi user environment (you'll get duplicates!).

You've got few options. Take a look here: Microsoft SQL Server: Generate a sequence number, per day - Stack Overflow[^]
 
Share this answer
 
Comments
Member 14649324 31-Aug-20 10:55am    
Thank You so I will try this
It's a really bad idea. The problem is that you're combining a DB based number with a VB based number generator, and that always leads to intermittent problems that can really mess up your DB integrity.

What happens is that you get the MAX value from SQL, add one to it, and write it to the DB. While you are doing that, I have also read the MAX value from SQL, added one to it, and written it to the DB. Suddenly, both of use are - legitimately - working with the same row identifier and that can cause some really hard to fix problems because your data and mine get mixed up. Because this isn't usually noticed for days, months, or even years by the time it is spotted, nobody can remember exactly what should be what (let alone what date-and-number each should have!)

If you have to implement this exact format, then do it all within SQL (which will be a PITA) as an atomic operation within a transaction, with a UNIQUE constraint on the column and probably some form of retry built in. Nasty.
 
Share this answer
 
Comments
Member 14649324 30-Aug-20 15:39pm    
can you help me plz how i can implement this in the sql can you help me with the query or what ever the requirements

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900