Dear Sir,
I'm Trying to Create auto invono with ms access database via dapper in vb.net.
in July
for "ITEM TRANSFER OUT" It should appear in the textbox (BtxtInvoNo) to be "TEST-ITO-2407-002" but appears in the textbox "TEST-ITO-2407-001"
for "SALES INVOICE" Appropriate appears in the textbox (BtxtInvoNo) "TEST-SI-2407-002"
in June
for "SALES INVOICE" It should appear in the textbox (BtxtInvoNo) to be "TEST-SI-2406-002" but appears in the textbox (BtxtInvoNo) "TEST-SI-2406-001"
for "ITEM TRANSFER OUT" Appropriate appears in the textbox (BtxtInvoNo) "TEST-ITO-2407-002"
Is there something wrong with my code?
Please Guide me.
Lnk video result code
Link sample database MS ACCESS
Thanks
Imports System.Data.OleDb
Imports Dapper
Public Class Form1
Private ConstInvnoStocksout As String
Private HeaderInvno As Integer
Private CurrentMonthYear As String
Private SOservice As New StocksoutService()
Private Sub CreateInvno()
Cursor.Current = Cursors.WaitCursor
Dim myDate As DateTime = Bdpinvoicefirst.Value
Dim strTime As String = myDate.ToString("yyMM-")
Dim newMonthYear As String = myDate.ToString("yyMM")
If BDDTRANSACTION.Text = "ITEM TRANSFER OUT" Then
ConstInvnoStocksout = "TEST-ITO-"
ElseIf BDDTRANSACTION.Text = "SALES INVOICE" Then
ConstInvnoStocksout = "TEST-SI-"
End If
Dim stockout = SOservice.SelectTop(ConstInvnoStocksout)
If stockout IsNot Nothing Then
HeaderInvno = stockout.HeaderInvno
CurrentMonthYear = stockout.MonthYear
Else
HeaderInvno = 0
CurrentMonthYear = ""
End If
If stockout Is Nothing OrElse myDate.Year <> stockout.InvnoDate.Year OrElse myDate.Month <> stockout.InvnoDate.Month Then
HeaderInvno = 1
CurrentMonthYear = newMonthYear
Else
HeaderInvno = stockout.HeaderInvno + 1
End If
BtxtInvoNo.Text = ConstInvnoStocksout & strTime & HeaderInvno.ToString("000")
Cursor.Current = Cursors.Default
End Sub
Private Sub BDDTRANSACTION_SelectedValueChanged(sender As Object, e As EventArgs) Handles BDDTRANSACTION.SelectedValueChanged
CreateInvno()
End Sub
Private Sub Bdpinvoicefirst_CloseUp(sender As Object, e As EventArgs) Handles Bdpinvoicefirst.CloseUp
CreateInvno()
End Sub
End Class
Public Class Stocksout
Public Property Invno() As String
Public Property HeaderInvno() As Integer
Public Property InvnoDate() As Date
Public Property ConstInvnoStocksout() As String
Public Property MonthYear() As String
End Class
Public Class StocksoutService
Private ReadOnly _conn As OleDbConnection
Public Function GetOledbConnectionString() As String
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\WAREHOUSEGB.accdb;Persist Security Info=False;"
End Function
Private _connectionString As String = GetOledbConnectionString()
Public Sub New()
_conn = New OleDbConnection(_connectionString)
End Sub
Public Function SelectTop(ConstInvnoStocksout As String) As Stocksout
Dim sql = $"SELECT TOP 1 HeaderInvno,MonthYear,InvnoDate FROM Stocksout WHERE ConstInvnoStocksout = '{ConstInvnoStocksout}' ORDER BY HeaderInvno DESC"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of Stocksout)(sql).FirstOrDefault()
End Using
End Function
End Class