How To Create Auto Number Using DateTime With Auto Increment

Dear everyone. hey there. herewith i would share again about VB.Net knowledge during my experiences developing system and using many data.

at one day i need to make an auto increment number but it need to have year, day and month. i was thinking a moment at the time, how the way i do that ???

after juggling with several coding herewith which i can share with you today..





One:
make a simple table to make it sure this code and meaning will be working well.

 my table :
TesIdOtomatis
field :
IdOtomatis

Two :
make a simple stored procedure to save your entity data.
my stored procedure :
INPUT_IdOto

Three :
make a new entity class

my Entity Class :

Public Class IdOtomatisEntity

    Private _IdOtomatis As String

    Public Overridable Sub DataOtomatis(ByVal IdOtomatis As String)
        _IdOtomatis = IdOtomatis
    End Sub

    Public Property IdOtomatis() As String
        Get
            Return _IdOtomatis
        End Get
        Set(ByVal value As String)
            _IdOtomatis = value
        End Set
    End Property
End Class

Four :
make a new control class
Imports System.Data.SqlClient

Public Class IdOtomatisControl

    Dim MeKONEKSI As New CONNECTIONClass

    Public Overridable Function Simpan_IdOtomatis(ByVal _Oto As IdOtomatisEntity) As SqlCommand
        MeKONEKSI.TUTUPKONEKSI()
        COMMAND = New SqlCommand("INPUT_IdOto", MeKONEKSI.BUKAKONEKSI)
        COMMAND.CommandType = CommandType.StoredProcedure

        Dim _IdOto As New SqlParameter("@IdOtomatis", SqlDbType.Char, 12)
        _IdOto.Value = _Oto.IdOtomatis

        With COMMAND.Parameters
            .Add(_IdOto)
        End With

        COMMAND.ExecuteNonQuery()
        COMMAND = New SqlCommand("", MeKONEKSI.TUTUPKONEKSI)

        Return COMMAND
    End Function

    Public Overridable Function AsliWirat() As String

        MeKONEKSI.TUTUPKONEKSI()
        DTS = New DataSet
        DTA = New SqlDataAdapter("SELECT IdOtomatis FROM TesIdOtomatis " & _
                               "WHERE YEAR(LEFT(IdOtomatis,8)) + MONTH(LEFT(IdOtomatis,8)) " & _
                               "+ DAY(LEFT(IdOtomatis,8)) = YEAR(GETDATE()) + MONTH(GETDATE()) + " & _
                               "DAY(GETDATE()) ORDER BY IdOtomatis ASC", MeKONEKSI.BUKAKONEKSI)
        DTA.Fill(DTS)
        If DTS.Tables(0).Rows.Count = 0 Then
            Return Format(Now.Date, "yyyyMMdd") & "-001"
        Else
            Dim KODE As String = Mid(DTS.Tables(0).Rows(DTS.Tables(0).Rows.Count - 1).Item(0), 3)
            Dim PLUS As String = Val(Microsoft.VisualBasic.Right(KODE, 3)) + 1

            If Len(PLUS) = 1 Then
                Return Format(Now.Date, "yyyyMMdd") & "-00" & PLUS & ""
            ElseIf Len(PLUS) = 2 Then
                Return Format(Now.Date, "yyyyMMdd") & "-0" & PLUS & ""
            ElseIf Len(PLUS) = 3 Then
                Return Format(Now.Date, "yyyyMMdd") & "-" & PLUS & ""
            ElseIf Len(PLUS) = 4 Then
                Throw New Exception("OVERLOAD DATA")
            End If
        End If

        Return DTA.Fill(DTS)
    End Function
End Class

Five :
make a new boundary class

Public Class IdTanggalTahunBulan

    Private Sub BtnSIMPAN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSIMPAN.Click
        Dim OtoEntity As New IdOtomatisEntity
        Dim OtoControl As New IdOtomatisControl

        Try
            If BtnSIMPAN.Text = "INPUT" Then
                TxtIdOtomatis.Text = OtoControl.AsliWirat
                BtnSIMPAN.Text = "SIMPAN"
            Else
                OtoEntity.IdOtomatis = TxtIdOtomatis.Text
                OtoControl.Simpan_IdOtomatis(OtoEntity)
                BtnSIMPAN.Text = "INPUT"
                TxtIdOtomatis.Text = ""
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            TxtIdOtomatis.Focus()
            TxtIdOtomatis.Text = ""
        End Try
    End Sub
End Class