How To Set Auto Number Which Reset Daily Number

Below script is how to use an auto number using SQL database which support to reset auto number everyday. means record data will be reset to 001 when the day is changing.


Public Overridable Function SerialOrderId_AutoNum() As String
        MeDB.TutupKoneksi()
        DTS = New DataSet
        DTA = New  _
              SqlDataAdapter("SELECT SerialOrderId FROM SerialOrder_Table " & _
                             "WHERE YEAR(LEFT(SerialOrderId,8)) + MONTH(LEFT(SerialOrderId,8)) " & _
                             "+ DAY(LEFT(SerialOrderId,8)) = YEAR(GETDATE()) + MONTH(GETDATE()) + " & _
                             "DAY(GETDATE()) ORDER BY SerialOrderId ASC", MeDB.BukaKoneksi)
        DTA.Fill(DTS)
        If DTS.Tables(0).Rows.Count = 0 Then
            Return Format(Now.Date, "yyyyMMdd") & "0001"
        Else
            Dim KODE As String = Mid(DTS.Tables(0).Rows(DTS.Tables(0).Rows.Count - 1).Item(0), 4)
            Dim PLUS As String = Val(Microsoft.VisualBasic.Right(KODE, 4)) + 1
            If Len(PLUS) = 1 Then
                Return Format(Now.Date, "yyyyMMdd") & "000" & PLUS
            ElseIf Len(PLUS) = 2 Then
                Return Format(Now.Date, "yyyyMMdd") & "00" & PLUS
            ElseIf Len(PLUS) = 3 Then
                Return Format(Now.Date, "yyyyMMdd") & "0" & PLUS
            ElseIf Len(PLUS) = 4 Then
                Return Format(Now.Date, "yyyyMMdd") & "" & PLUS
            ElseIf Len(PLUS) = 5 Then
                Throw New Exception("OVERLOAD SEQUENCE ID")
            End If
        End If
        Return DTA.Fill(DTS)

    End Function