How to create sequential transaction numbers with the serial number of each transaction in the MS ACCESS database in Dapper with VB.NET

Question

How to create sequential transaction numbers with the serial number of each transaction in the MS ACCESS database in Dapper with VB.NET?. Is it possible to reset the serial number to 00001 if the month changes?.

Maybe the code I posted is still wrong. Please Guide me

Thanks

Public Class Form1
    Private HeaderInvno As Integer
    Dim sis As New Stocksinservice()
    Private Sub CreateInvno()
        Dim myDate As DateTime = DateTime.Now
        Dim strTime As String = myDate.ToString("MMyy-")
        Dim Stockin = sis.SelectTop()
        If Stockin Is Nothing Then
            HeaderInvno = 1
        Else
            HeaderInvno = Stockin.HeaderInvno + 1
        End If
        If ComboBox1.Text = "ITEM TRANSFER IN" Then
            BtxtInvoNo.Text = "DEPT-ITI-" & strTime & (HeaderInvno).ToString("00000")
        ElseIf ComboBox1.Text = "PURCHASE INVOICE" Then
            BtxtInvoNo.Text = "DEPT-PI-" & strTime & (HeaderInvno).ToString("00000")
        ElseIf ComboBox1.Text = "RECEIVE ITEM" Then
            BtxtInvoNo.Text = "DEPT-RI-" & strTime & (HeaderInvno).ToString("00000")
        End If
    End Sub
    Private Sub ComboBox1_SelectedValueChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedValueChanged
        CreateInvno()
    End Sub
End Class
Public Class Stocksinservice
    Private connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DEMO.accdb;Persist Security Info=False;"
    Public Function SelectTop() As Stocksin
        Dim sql = $"SELECT TOP 1 HeaderInvno FROM Stocksin ORDER BY HeaderInvno DESC"
        Using _conn = New OleDbConnection(connectionString)
            Return _conn.Query(Of Stocksin)(sql).FirstOrDefault()
        End Using
    End Function
End Class
Public Class Stocksin
    Public Property Invno() As String
    Public Property HeaderInvno() As Integer
End Class

Table Stocksin

This is a stored database record

Invno	HeaderInvno	InvnoDate
DEPT-ITI-1023-00001	1	18-10-2023

So if I select from the combobox the result is something like below :

DEPT-ITI-1023-00002
DEPT-PI-1023-00002
DEPT-RI-1023-00002

The desired result should be like below:

DEPT-ITI-1023-00002
DEPT-PI-1023-00001
DEPT-RI-1023-00001

Answer 1

You can add column here (adjust it with your code). We need additional column to contain smart code on ItemTransferIn and check MonthYear

ALTER TABLE YourTableName
ADD COLUMN ItemTransferIn VARCHAR(255),
ADD COLUMN MonthYear VARCHAR(6);

Update select method by having conditional parameter for ItemTransferIn when select top, and then update if month changed.

Public Class Form1
    Private HeaderInvno As Integer
    Private CurrentMonthYear As String
    Dim sis As New Stocksinservice()
    Private ItemTransferIn As String ' Define ItemTransferIn as a string

    Private Sub CreateInvno()
        Dim myDate As DateTime = DateTime.Now
        Dim strTime As String = myDate.ToString("yyMM-")
        Dim newMonthYear As String = myDate.ToString("yyMM")

        ' Set ItemTransferIn based on ComboBox1.Text
        If ComboBox1.Text = "ITEM TRANSFER IN" Then
            ItemTransferIn = "DEPT-ITI-"
        ElseIf ComboBox1.Text = "PURCHASE INVOICE" Then
            ItemTransferIn = "DEPT-PI-"
        ElseIf ComboBox1.Text = "RECEIVE ITEM" Then
            ItemTransferIn = "DEPT-RI-"
        End If

        ' Retrieve the HeaderInvno and MonthYear from the database
        Dim stockin = sis.SelectTop(ItemTransferIn )
        If stockin IsNot Nothing Then
            HeaderInvno = stockin.HeaderInvno
            CurrentMonthYear = stockin.MonthYear
        Else
            HeaderInvno = 0
            CurrentMonthYear = ""
        End If

        ' Check if the month and year have changed
        Dim newMonthYear As String = myDate.ToString("yyMM")
        If Not newMonthYear.Equals(CurrentMonthYear) Then
            HeaderInvno = 1
            CurrentMonthYear = newMonthYear
            ' Update the MonthYear in the database
            sis.UpdateMonthYear(CurrentMonthYear, ItemTransferIn )
        Else
            HeaderInvno += 1
        }

        ' Concatenate ItemTransferIn with other parts of the invoice number
        BtxtInvoNo.Text = ItemTransferIn & strTime & HeaderInvno.ToString("00000")
    End Sub

    Private Sub ComboBox1_SelectedValueChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedValueChanged
        CreateInvno()
    End Sub
End Class

And another Stocksinservice class

Public Class Stocksinservice
    Private connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DEMO.accdb;Persist Security Info=False"
    
    Public Function SelectTop(itemTransferIn As String) As Stocksin
        Dim sql = "SELECT TOP 1 HeaderInvno, MonthYear FROM Stocksin WHERE ItemTransferIn = @ItemTransferIn ORDER BY HeaderInvno DESC"
        Using _conn = New OleDbConnection(connectionString)
            Return _conn.Query(Of Stocksin)(sql, New With {Key .ItemTransferIn = itemTransferIn}).FirstOrDefault()
        End Using
    End Function

    Public Sub UpdateMonthYear(monthYear As String, itemTransferIn As String)
        Dim sql = "UPDATE YourTableName SET MonthYear = @MonthYear WHERE ItemTransferIn = @ItemTransferIn" ' Replace YourTableName with the actual table name
        Using _conn = New OleDbConnection(connectionString)
            _conn.Execute(sql, New With {Key .MonthYear = monthYear, .ItemTransferIn = itemTransferIn})
        End Using
    End Sub
End Class