ขอคำแนะนำหน่อยครับ พอดีผมต้องการ Auto ID สำหรับรันเลขเอกสารอ้างอิง แล้วมันต้องมีทั้งตัวหนังสือ-เดือน-ตัวเลข ปนกันอยู่ Code (VB.NET)
Private Sub AutoGenerateTranID()
Dim sqlTmp As String = ""
Dim comTmp As OleDbCommand = New OleDbCommand
Dim drTmp As OleDbDataReader
Dim tmpTransID As Integer = 0
sqlTmp = "SELECT TOP 1 TransID FROM Borrow ORDER BY TransID DESC"
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
Try
With comTmp
.CommandType = CommandType.Text
.CommandText = sqlTmp
.Connection = Conn
drTmp = .ExecuteReader()
drTmp.Read()
tmpTransID = CInt(CStr((drTmp.Item("TransID"))))
tmpTransID = tmpTransID + 1
LastTransID = tmpTransID.ToString("0000000")
End With
Catch
LastTransID = "0000001"
End Try
drTmp.Close()
End Sub
Exception:Thrown: "Input string was not in a correct format." (System.FormatException)
A System.FormatException was thrown: "Input string was not in a correct format."
Exception:Caught: "Conversion from string "IT00005" to type 'Integer' is not valid." (System.InvalidCastException)
A System.InvalidCastException was caught: "Conversion from string "IT00005" to type 'Integer' is not valid."
Function AutoQTNo() As String
OpenDatabase()
ConnectionDatabase()
Dim SvrDate As DateTime = DateTime.Now
Dim CmdAuto As New SqlCommand("SELECT SBAQTCode FROM SBAQT WHERE (SUBSTRING(SBAQTCode,1,5)='SBA01') AND (SUBSTRING(SBAQTCode,6,2)=RIGHT(YEAR(GETDATE()),2)) ORDER BY SBAQTCode DESC", Connt)
If CmdAuto.Connection.State = ConnectionState.Open Then CmdAuto.Connection.Close()
CmdAuto.Connection.Open()
Dim DrAuto As SqlDataReader
DrAuto = CmdAuto.ExecuteReader
DrAuto.Read()
If DrAuto.HasRows = True Then
Return "SBA01" & Format(SvrDate, "yy") & Format(CDbl(Microsoft.VisualBasic.Right(DrAuto("SBAQTCode"), 4)) + 1, "0000")
Else
Return "SBA01" & Format(SvrDate, "yy") & "0001"
End If
DrAuto = Nothing
CmdAuto.Connection.Close()
CmdAuto.Dispose()
End Function
Private Sub AutoGenerateTranID()
Dim SvrDate As Date = Date.Now
Dim CmdAuto As New OleDbCommand("SELECT TransID FROM Borrow WHERE (SUBSTRING(TransID,1,2)='IT') AND (SUBSTRING(TransID,6,2)=RIGHT(MONTH(GETDATE()),2)) ORDER BY TransID DESC", Conn)
If CmdAuto.Connection.State = ConnectionState.Open Then CmdAuto.Connection.Close()
CmdAuto.Connection.Open()
Dim DrAuto As OleDbDataReader
DrAuto = CmdAuto.ExecuteReader
DrAuto.Read()
If DrAuto.HasRows = True Then
Return = "IT" & Format(SvrDate, "MM") & Format(CDbl(Microsoft.VisualBasic.Right(CStr(DrAuto("TransID")), 4)) + 1, "0000")
Else
Return "IT" & Format(SvrDate, "MM") & "0001"
End If
DrAuto = Nothing
CmdAuto.Connection.Close()
CmdAuto.Dispose()
End Sub
Load balance : Server 02
