如何使用 Dapper 的 MS ACCESS 数据库中每个事务的序列号创建连续事务编号 VB.NET

您可以在此处添加列（使用您的代码进行调整）。我们需要额外的列来包含智能代码并检查ItemTransferInMonthYear

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

通过在选择顶部时使用条件参数来更新 select 方法，然后在月份更改时进行更新。ItemTransferIn

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

还有另一个班级Stocksinservice

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