Imports System.Data.SqlClient
Public Class DigidandyyMMandNumber1
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strConnString, strSQL As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Public Sub ConnectDB()
strConnString = ConfigurationManager.ConnectionStrings("con").ConnectionString
objConn = New SqlConnection(strConnString)
End Sub
Function CreateID() As String
ConnectDB()
Dim iMAX As Integer = 1
Dim intNum As String = Nothing
strSQL = "SELECT MAX(RIGHT(ID,5)) As ID FROM AutoID"
objCmd = New SqlCommand(strSQL, objConn)
objConn.Open()
If IsDBNull(objCmd.ExecuteScalar()) = True Then
'intNum = "OR" & CStr(Now.Year).Substring(2) & "0000" & iMAX
intNum = "ID" & CStr(Format(Now.Date, "yyMM")) & "0000" & iMAX
Else
iMAX = objCmd.ExecuteScalar() + 1
End If
If iMAX <= 9 Then
intNum = "ID" & CStr(Format(Now.Date, "yyMM")) & "0000" & iMAX
ElseIf iMAX <= 99 Then
intNum = "ID" & CStr(Format(Now.Date, "yyMM")) & "000" & iMAX
ElseIf iMAX <= 999 Then
intNum = "ID" & CStr(Format(Now.Date, "yyMM")) & "00" & iMAX
ElseIf iMAX <= 9999 Then
intNum = "ID" & CStr(Format(Now.Date, "yyMM")) & "0" & iMAX
ElseIf iMAX <= 99999 Then
intNum = "ID" & CStr(Format(Now.Date, "yyMM")) & iMAX
End If
objConn.Close()
Return intNum
End Function
Private Sub AddID()
Dim AutoID As String = Nothing
AutoID = CreateID()
objCmd.CommandText = "INSERT INTO AutoID (ID) "
objCmd.CommandText += "VALUES ('" & AutoID & "')"
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
MsgBox("บันทึกสำเร็จ")
End Sub
Protected Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
AddID()
End Sub
End Class
Tag : .NET, Ms SQL Server 2008, Web (ASP.NET), VB.NET, VS 2012 (.NET 4.x)
Imports System.Data.SqlClient
Public Class Test
Inherits System.Web.UI.Page
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim strConnString, strSQL As String
Dim intNum As String = Nothing
Dim Count As Integer
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Public Sub ConnectDB()
strConnString = ConfigurationManager.ConnectionStrings("con").ConnectionString
objConn = New SqlConnection(strConnString)
End Sub
Private Sub AddID()
ConnectDB()
strSQL = "SELECT ISNULL(MAX(RIGHT(ID,5)),0) As ID FROM AutoID"
objCmd = New SqlCommand(strSQL, objConn)
objConn.Open()
Count = objCmd.ExecuteScalar()
objConn.Close()
Dim iMAX As Integer = IIf(Count <= 0, 1, Count + 1)
If iMAX < 10000 Then
strSQL = "INSERT INTO AutoID (ID) VALUES ('" & "ID" & CStr(Format(Now.Date, "yyMM")) & iMAX.ToString("0000") & "')"
objCmd = New SqlCommand(strSQL, objConn)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
MsgBox("บันทึกสำเร็จ")
Else
MsgBox("ข้อมูลเต็ม")
End If
End Sub
Protected Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
AddID()
End Sub
End Class
Load balance : Server 03
