Click here to Skip to main content
14,300,842 members
Rate this:
Please Sign up or sign in to vote.
See more:
I want to generate custom id number contains (current year-id) the id number at the next year auto reset (oledb)



For Example:
Year-ID
2019-1
2019-2
2019-3
....

2020-1
2020-2
....
2021-1
2021-2
2021-3
2021-4
...

What I have tried:

Private Sub referenceno1()
        cn.Open()
        cmd = New OleDb.OleDbCommand("select * from JOBCARDGENERATORTABLE", cn)
        cmd1 = New OleDb.OleDbCommand("select * from JOBCARDGENERATORTABLE", cn)
        cmd.Connection = cn
        Dim maxid As Object
        Dim strid As String
        Dim intid As Integer
        Dim NEWID As Integer = 1
        Dim MAXYEAR As Object
        Dim CURRENTYEAR As Integer = Date.Now.Year


        cmd.CommandText = "select max(JOBCARDNO) as maxid from JOBCARDGENERATORTABLE"
        cmd1.CommandText = "select max(YEAR) as MAXYEAR from JOBCARDGENERATORTABLE"

        maxid = cmd.ExecuteScalar
        MAXYEAR = cmd1.ExecuteScalar

        If maxid Is DBNull.Value Then
            intid = 1

        ElseIf CURRENTYEAR > MAXYEAR Then

            intid = 1

        ElseIf CURRENTYEAR = MAXYEAR And maxid > 1 Then
            strid = CType(maxid, String)
            intid = CType(strid, String)
            intid = intid + 1

        ElseIf CURRENTYEAR = MAXYEAR And maxid Is DBNull.Value Then
            strid = CType(maxid, String)
            intid = CType(strid, String)
            intid = intid + 1

        Else
            strid = CType(NEWID, String)
            intid = CType(strid, String)
            intid = NEWID + 1
        End If
        jobcardnotb.Text = intid
        REFERENCE = CURRENTYEAR & "-" & intid


        cn.Close()
        Return

    End Sub
Posted
Updated 11-Sep-19 23:20pm
v3
Comments
MadMyche 11-Sep-19 11:58am
   
How many ID's do you use per year?
Maciej Los 11-Sep-19 15:58pm
   
What database engine?
Rate this:
Please Sign up or sign in to vote.

Solution 2

For SQL server

The simplest way to achieve that is to create custom function. See: Custom Auto-Generated Sequences with SQL Server - SQLTeam.com[^]

Another way is to use SQL command like this:
SELECT CONCAT(YEAR(DateTimeField ), '-', COALESCE(MAX(ID), 1)) AS NewID
FROM YourTable
WHERE YEAR(DateTimeField) = YEAR(@DateToCompare)


Good luck!
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

cn.Open()
        cmd = New OleDb.OleDbCommand("select * from JOBCARDGENERATORTABLE", cn)
        cmd1 = New OleDb.OleDbCommand("select * from JOBCARDGENERATORTABLE", cn)
        cmd.Connection = cn
        Dim maxid As Object
        Dim strid As String
        Dim intid As Integer
        Dim NEWID As Integer = 1
        Dim MAXYEAR As Object
        Dim CURRENTYEAR As Integer = Date.Now.Year


        cmd.CommandText = "select max(JOBCARDNO) as maxid from JOBCARDGENERATORTABLE"
        cmd1.CommandText = "select max(YEAR) as MAXYEAR from JOBCARDGENERATORTABLE"

        maxid = cmd.ExecuteScalar
        MAXYEAR = cmd1.ExecuteScalar

        If maxid Is DBNull.Value Then
            intid = 1

        ElseIf CURRENTYEAR > MAXYEAR Then

            intid = 1

        ElseIf CURRENTYEAR = MAXYEAR And maxid > 1 Then
            strid = CType(maxid, String)
            intid = CType(strid, String)
            intid = intid + 1

        ElseIf CURRENTYEAR = MAXYEAR And maxid Is DBNull.Value Then
            strid = CType(maxid, String)
            intid = CType(strid, String)
            intid = intid + 1

        Else
            strid = CType(NEWID, String)
            intid = CType(strid, String)
            intid = NEWID + 1
        End If
        jobcardnotb.Text = intid
        REFERENCE = CURRENTYEAR & "-" & intid


        cn.Close()
        Return
   
Comments
CHill60 12-Sep-19 4:59am
   
Is this meant to be the solution or what?
Rate this:
Please Sign up or sign in to vote.

Solution 4

Solution:

provider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
            datafile = filepathtb.Text
            connString = provider & datafile
            myConnection.ConnectionString = connString


            myConnection.Open()
            cmd = New OleDb.OleDbCommand("select * from JOBCARDGENERATORTABLE", myConnection)

            cmd.Connection = myConnection
            Dim maxid As Object
            Dim strid As String
            Dim intid As Integer
            Dim CURRENTYEAR As Integer = Date.Now.Year

            cmd.CommandText = "select max(JOBCARDNO) as maxid from JOBCARDGENERATORTABLE WHERE CURRENTYEAR = YEAR"
            cmd.Parameters.Add("@CURRENTYEAR", OleDbType.Numeric).Value = CURRENTYEAR

            maxid = cmd.ExecuteScalar

            If maxid Is DBNull.Value Then
                intid = 1
            Else
                strid = CType(maxid, String)
                intid = CType(strid, String)
                intid = intid + 1

            End If


            jobcardnotb.Text = intid
            yeartb.Text = "J-" & CURRENTYEAR & "-" & intid

            myConnection.Close()
            Return
        End If


Result EXAMPLE:
J-2019-1
J-2019-2
.....
J-2020-1
J-2020-2
J-2020-3
....

I NEED THE RESULT LIKES:
J-2019-0001
J-2019-0002
.....

ANY BODY CAN HELP ME?
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100